FileMaker Portal Show Distinct Value Tutorial

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.

Method:

Reference the following graph derived from two tables, ParentTable and ChildTable, with the ChildTable_distinct_values as the second table occurrence of ChildTable table.

1_Show_Distinct_Values_Technique_Graph

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:

2_Show_Distinct_Values_Self_Relation

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:

3_Show_Distinct_Values_Portal_Filter

Result:

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’.

4_Show_Distinct_Values_Technique_Portal

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 :

5_Show_Distinct_Values_In_Portal_Example

There you have it. A FileMaker portal showing distinct values. Simple.

filemaker portal show distinct value tutorial

2016-11-01T17:01:18+00:00 By |