Filtering Portals, Method 2 – Using a Relationship for FilteringPosted: August 31, 2011
Using Filemaker’s native portal filtering is a great way of narrowing down a portal list, as long as there are fewer than a couple of hundred records involved. (See this post for more details.)
But where there are more records involved, it’s better to use a relationship-driven filter. I came upon the technique explained here on the website of Nightwing Enterprises, and I’ve included here by way of documentation for me, and in no sense to suggest that it was my idea!
In “Focus” (the system that I look after), this arises where we are potentially listing ALL people in the portal (rather than just those who meet certain criteria, enrolled on a certain activity, etc.).
So, for example, on the Main Menu we have a search box to enable users to find the person they are interested in from a potential several thousand, by entering part of their first name or surname. To achieve this, we have:
- A Global filter field, e.g. INT_g_Filter. This is a text field, which will hold the search string entered by the user.
- A corresponding “multi-key” field, e.g. INT_g_Mkey. This is also a text field, which will be populated by a triggered script when INT_g_Filter is modified.
- A search field in the People table, a text calc, defined as:
(Obviously, if you only want to search on the surname field, that’s all you would put in the calc. Similarly, if you want more fields to be used, they can be added on subsequent lines, with carriage returns separating them.)
- A relationship between the table where the multi-key field is held (say, “Interface”), and the table holding the actual data (say, “People”). The relationship is defined as shown below:
And now, the clever bit, i.e. the triggered script, run “OnObjectModify” of INT_g_Filter. (Note that the script uses the “GSP (num)” custom function to retrieve parameters – that’s from the Brian Dunning site, specifically here.)
This script is a much simplified version of the Nighthawk original (which allows for portal record selection, using arrow keys, and therefore has much more complex keystroke trapping). But if you just want a simple filter, this does the job. Note that this version receives 2 parameters, i.e. the name of the multikey field (P1), and the name of the search string input field. (NB – I had trouble passing the contents of the input field, as, at the moment of the script being triggered, the INT record hasn’t been committed, so I found it easier to pass the name, then do a commit, then a GetField.)