Getting the IDs for a Filtered Portal

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:

List of Summary Field Type

 

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:

 

Filtered Portals Demo

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:

Get Filtered Customer IDs

 

You can download this demo file to explore this technique – please post any comments below.

12 replies
  1. Daniel Wood
    Daniel Wood says:

    Great article Andrew, thanks for sharing!

    I’ve also done a little experimenting with this function. As a quick way to get the filtered IDs we put the summary field inside the portal and give it an object name, eg “IDs”. We can then grab the IDs using the GetLayoutObjectAttribute function, eg:

    GetLayoutObjectAttribute ( "IDs" ; "Content" )

    This can be used in a calculation field for the purposes of GTRR if need be.

    As far as the field in the portal goes, an interesting ‘feature’ is that you can set its hide condition to 1 so it does not appear, but the GetLayoutObjectAttribute function will still evaluate that object even if its not visible.

  2. Andrew Duncan
    Andrew Duncan says:

    Hi Daniel – I like the idea of the hidden summary field on the portal and using GetLayoutObjectAttribute to get the list of IDs! A nice improvement to this technique – thanks for commenting.

  3. Nick Lightbody
    Nick Lightbody says:

    Hi Andrew,

    Thanks for the share – really helpful – I hadn’t realised that the List of summary honoured the portal filter – and thanks also to Daniel for his suggestion.

    Cheers, Nick

  4. Andrew Duncan
    Andrew Duncan says:

    Hi Nick,

    Yes you could use this with my scripted example:

    ValueCount ( AllCustomers::ListOfCustomerIDs )

    or this with Daniel’s non scripted version:

    ValueCount ( GetLayoutObjectAttribute ( “IDs” ; “Content” ) )

Trackbacks & Pingbacks

  1. […] via Getting the IDs for a Filtered Portal | Databuzz. […]

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.