Filtering portals – Method 1, using FM’s native portal filteringPosted: July 26, 2011
This method of portal filtering uses the actual Filemaker “filtered portals” feature, which appeared in Filemaker 10, I think. This is a great way to reduce the number of relationships in a solution (portal filtering doesn’t rely on relationships). Matt Petrowsky showed how to use the feature elegantly and dynamically – i.e. the contents of the portal changes according to a search string entered by the user – although native FM portal filtering is not great for very large numbers of records. For that (i.e. more than a couple of hundred records), it’s better to use a relationship-based filter – see Method 2.
A combination of techniques is often needed: I use both on one layout – Method 1 to filter the portal showing names of people enrolled on a Programme (never more than a few hundred, and usually much less), and Method 2 to filter the portal showing all people on the system (several thousand).
So, to deal with the first method first, the “Expected” portal above uses the following:
A Text field in the parent table (in this case Sessions), called SSN_Filter. This is the field that the user enters the search string.
A triggered script, “Filter Portal”, run when this field is modified (OnObjectModify). The script does this:
i.e. basically it commits the record (including the contents of the SSN_Filter field), then takes the user back to the filter field (the name of which has been stored as $object), to continue entering the search string.
The portal filter itself is defined as:
i.e. show records when either there’s nothing in the filter field (the “IsEmpty” bit at the end of the expression, which will have the effect of showing ALL records) or there’s data in the filter field, and that data matches something in the first name or surname fields.
Simple, but clever, EXCEPT for one problem. Because the SSN_Filter field is not a global, this method is not really suitable in a multi-user setup where it’s likely that more than one user will be accessing the same SSN rec. That said, the SSN_Filter field is not really “data”, so it’s no big deal if users trip each other up, but it’s not perfect. Also, on the filter input field, make sure “auto-complete” is switched OFF, otherwise it will display the last stored value if the user enters the same starting value.
See later post for explanation of how Method 2 uses global fields and relationships for a more robust method more suitable for larger data sets.