One of my favourite features of FileMaker Pro v13 is the new “List of” summary type option, which is used for creating a return-delimited list of non-blank values in a field. For example if you wanted a list of all Customer IDs for your found set of records you can now simply reference a Summary field that uses the List of type option:
I’ve found the performance to be excellent and it can now replace the use of Custom Functions, looping scripts etc that were previously used to generate a list of IDs for a found set of records. We’ve also another great use for it – in conjunction with filtered portals it can be used to get the IDs for the currently filtered portal records. This makes it simply to filter a portal, get the list of filtered IDs which can then be used in a multi-key relationship to group those filtered records for further processing.
Portal filtering was introduced in FileMaker Pro 11 and lets developers easily filter a portal by simply editing the portal setup – you don’t need to add unnecessary clutter to your relationship graph to support portal filtering. However one of the limitations with portal filtering is that the results of calculations are based on the full set of related records, not just the records in the portal that are currently filtered. This means that you can’t simply use functions like the List function to return a list of all the Customer IDs that are currently filtered as it will return a list of all Customer IDs for the underlying relationship used by the portal.
However you can now use the new “List of” summary type field with a filtered portal to quickly get the list of filtered record IDs. You simply need to create a Summary field in your “child” table that you are filtering that is a List of the IDs, then create a script that retrieves this value.
Here’s a screenshot showing a simple filtered portal of Customers by State. When you select a State from the pop-up menu the list of customers changes to match the selected State:
You’ll notice below the portal is another field that displays the list of Customer IDs for the currently filtered portal records. We’ve added a script trigger to the State pop-up menu filter that checks for any filtered records and retrieves the CustomerIDs for the filtered records by retrieving the value of the List of Customer IDs Summary field. The script has to go to the first row of the portal to establish the correct context first – here’s what the script looks like with the step that gets the filtered IDs highlighted:
You can download this demo file to explore this technique – please post any comments below.