In FileMaker, showing distinct values in a portal can be accomplished in various ways. In this tutorial, I show how to do this using a self-relationship combined with a portal filter using the GetNthRecord ( field name ; recordnumber ) calculation function.
An example of this technique being useful is when you want to list products a customer has purchased over a period of time. If you do not want to see products repeated in the list, this how-to provides a simple technique. This technique gives you the list of items with only one occurrence of each distinct product.
In this post, I show you the technique, followed by an example cited above.
Reference the following graph derived from two tables, ParentTable and ChildTable, with the ChildTable_distinct_values as the second table occurrence of ChildTable table.
First, create a relationship between the ParentTable and the ChildTable table occurrences where ParentTable::ParentPrimaryKey = ChildTable::ParentForeignKey. Then create a self-relationship between the ChildTable and ChildTable_distinct_values table occurrences, based on the value on which duplicates appear in the data via the relationship, where ChildTable::ValueToDisplay = ChildTable_distinct_values::ValueToDisplay.
The relationship connecting the ChildTable and ChildTable_distinct_values table occurrences is defined as shown:
Next, setup a portal in the ParentTable table occurrence layout showing records from the ChildTable table occurrence .
In the Portal Setup -> Filter Portal Records -> Specify Calculation dialog, input the equation of: ChildTable::ChildPrimaryKey = the GetNthRecord ( ChildTable_distinct_values::ChildPrimaryKey ; 1 ) as the boolean switch for display of the record in the portal, shown here:
Upon the first instance of a value in the related record set, it displays, whereas, any additional instance of the same (duplicate) value does not display since the subsequent instance will have a different primary key. Here is shown sample data of ‘A1, A1, A2, A2, A2’.
Here is an example of a purchased product list for a customer using this technique (showing filtered and unfiltered portals for comparison). Note the repetitions in the unfiltered list :
There you have it. A FileMaker portal showing distinct values. Simple.
filemaker portal show distinct value tutorial