CODEWITHSUNDEEP
group-byaggregate-functionsfilemaker
user1162803
user1162803

Reputation: 11

FileMaker - How to have distinct values listed on-screen?

I would like to have the distinct values of a repeating field listed in another field (in browse mode). The case is as follows:

I have a field that contains country names. The country names in this field may repeat themselves, thus when using the "List" function I get something like "France, France, France, Germany, Germany, Hungary". How can I create a field that lists all the values from my country field, but has it grouped as "France, Germany, Hungary"?

In the case I could directly use a SQL query to interfere with the FileMaker databse I would use the GROUP BY statement.

Upvotes: 1

Views: 3795

Answers (1)

pft221
pft221

Reputation: 1739

To make a summary of all the values across every record, do as follows:

  1. Make a new value list labeled 'Countries' (File Menu > Manage > Value Lists)
  2. Make the value list 'Use Values From Field' and specify your repeating field
  3. Create a new Calculation field, 'Listed Countries'

  4. Set the calculation to type 'Text' and with the following code:

    ValueListItems ( Get(FileName) ; "Countries" )

If you'd like to find the value for only the current record:

  1. Make a new Table Occurrence, 'NewTO' of the same base table and link the two records by a unique index.
  2. Change the 'Countries' value list so that it obtains values from 'NewTO' and your repeating field.
  3. Select 'Only include related values starting from' and select your original Table Occurrence

If you'd like the list to update as the repeating field value changes, make certain that you Do Not Store Calculation Results for the field.

Upvotes: 2

Related Questions