SID-01781: Search Form Data and output a table with only unique values

Status:	Answered	TWiki version:	5.1.4	Perl version:	5.8.8
Category:	CategoryForms	Server OS:	Linux	Last update:	12 years ago

How do I search Form Data and present a table of the unique values of a given field - summarized (without duplicates).

Here's the background. I've got a Staff Form with (for simplicity) two fields - Name & Department. Multiple people can be in any department, but only 1 department per Name. Example:

... and so on.

The end result needed, regardless of the number of people in any given department, is:

How do I accomplish this?

Thanks. Bob

-- Bob Mellinger - 2013-08-08

Discussion and Answer

Use some SpreadSheetPlugin magic.

Example:

Departments: Finance, Sales, Marketing

You could also use hash functions instead, see example blog post.

-- Peter Thoeny - 2013-08-09

Thanks for the quick response. My apologies, I believe that I did not properly, nor adequately, state my challenge. I have the following Form defined:

Staff Form

Each Name can be assigned to only one department, but each department may have multiple people with that Department. My source data is Form data, not a table as provided initially.

How can I search the Staff Forms and display a table with just the unique values for Department?

-- Bob Mellinger - 2013-08-09

Do a search for all topics with that form. In the SEARCH format apply a formula to keep track of the departments. Untested:

format="percnt$CALCULATE{$SETMHASH(department, $formfield(Department), +1)}$percnt"

As a result you have a hash where the key is the names of all department and the value is the number of staff per department. You might need to update the SpreadSheetPlugin.

-- Peter Thoeny - 2013-08-09

Thanks, Peter, I appreciate your help. As a result of trial and error and excessive caffeine, here is another solution that I stumbled upon:

%CALC{"$LISTSORT( $LISTUNIQUE(%SEARCH{ "(StaffForm.Department = '')" type="query" nosearch="on" nototal="on" separator=", " expandvariables="on" format="$formfield(Department)" }%) )" }%

-- Bob Mellinger - 2013-08-10

If you answer a question - or someone answered one of your questions - please remember to edit the page and set the status to answered. The status selector is below the edit box.