Filtering portals – Method 1, using FM’s native portal filtering

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.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s