Using eSQL to filter a “QuickFind” portal

(Note – this is an update of an older post about filtering portals via relationships.)

The Requirement: A quick, user-friendly “spotlight” style search option, to filter a portal of people’s names.

Demo file:

The Interface

Here’s how the finished interface looks:


Using the FM’s nice “River” theme, we have a QuickFind box in which the user enters a string, and, as they type, the list expands or contracts to show matching records.  (Note that the “Search” button in the screenshot is not part of this technique – that opens a popover in which the user can specify a number of other criteria – show only people over a certain age, who have attended this month, etc.)

The input field is a global field within the table to be searched.  In this example, it’s called PPL::FILTERINPUT.

When this field is empty, it looks like this:


The magnifying glass icon and the “QuickFind” text are hidden, via the very useful “hide object when” feature, the condition here being “not IsEmpty (PPL::FILTERINPUT)”.

The Relationship

The portal showing the list of people’s names, plus other information as required, is based on a self-join.  I’ve called the table occurrence PPL_PPL~filter with the self-join relationship defined as:


In the PPL table, “id” is the primary key.  So the portal will show anybody whose id appears in the global field PPL::FILTERIDS.  (We can ignore the “isDead” thing, which just excludes dead people from the list.)

So, how does PPL::FILTERIDS get populated?

Populating FILTERIDS

The population of the match field involves two scripts, “Initiate filter” and “Filter portal”.  The second one, which does the searching, ONLY runs after a given pause between the user’s keystrokes.  This is important, because you don’t want the searching script to run every time the user presses a key – it would be too slow.  The duration of the delay that you allow depends mainly on the typing speed of the user – I’ve settled on 0.35 seconds.

So, the first script, “Initiate Filter”, runs EVERY time the user presses a key – it’s an OnObjectModify triggered script attached to PPL::FILTERINPUT.  This is what it does:

qf4The script is passed two parameters, one is the name of the field that we want to populate (i.e. PPL::FILTERIDS), and the other is the object name (not the field name) of the input field – I’ve called mine “PeopleNameFilter”.

These two parameters are stored as global variables, for use in the subsequent script. (Note that I use the GSP custom function to “get” these parameter values – it’s available here on Brian Dunning’s site).

We then use “Install OnTimer Script” to schedule the searching script every 0.35 seconds – this has the effect of allowing the user that amount of time to press another key – if s/he modifies the field again within 0.35 seconds,  the script runs again, and the new OnTimer Script replaces the previous one.

When the user has finished typing (i.e. 0.35 seconds has elapsed), the “Filter Portal” script runs.  It looks like this:


If the input field is empty, we just initialise the multi-key field.  This will have the effect of showing no records in the portal.  We then use “Install OnTimer Script” again, with a zero interval, to cancel the repetition of the current script.

If we have an input string, we use a fairly straightforward ExecuteSQL statement to find the matching ids.  Here’s the eSQL statement:

ExecuteSQL(“SELECT id FROM PPL WHERE searchableData LIKE ? ” ; “” ; “” ; “%” & PPL::FILTERINPUT & “%” )

PPL::searchableData, the field searched by the ExecuteSQL statement, is a concatenated text field, including whatever pieces of data you want to search on.  (I’ve experimented with making this a calculated field, but have found it much more efficient to have it as a plain text field, updated by a script whenever relevant data changes.)  In this case, the field is updated as:

lower(nameFirst & “ “ & nameLast & “ “ & address1)

So, at the end of all this, we have PPL::FILTERIDS populated with the primary keys of all records where matching text has been found (anywhere within the searchableData field – it doesn’t have to be at the beginning), and the portal showing those records.  If there are NO matching records, we say so, then cancel the OnTimer script (by installing another (blank) one), and go back to where we came from.

Note that the use of parameters is optional, but makes it possible to use it elsewhere, should we need other similar QuickFinds.

As ever, please comment if you can suggest a better way of doing this.  None of it is really of my own invention, and is documented here to remind myself of how it works, but if it’s of use to you, so much the better!


Family Relationships – Design Walkthrough

In “FOCUS”, the application that I spend much of my time looking after, we need to record family networks.  This post explains how I’ve accomplished this.  As ever, it’s included here partly for my own documentation needs, but also in the hope that a) it may be interesting to others, and that b) others may be able to tell me how to make it better.

(Note – Obviously, throughout this explanation, be careful not to confuse family relationships with Filemaker relationships.  Hopefully, it’s clear which is which.)

People in FOCUS can be linked to each other to reflect family relationships.  The Relationships (or REL) table is the join table which describes the relationship.  For most relationships, there are 2 REL records, i.e. one for each side of the relationship.

So, for example, Homer Simpson is the father of Bart Simpson, so there are 2 REL records:

  • Record 1 tells us that Homer is the father of Bart.
  • Record 2 tells us that Bart is the son of Homer.

These are the main fields involved:


  • REL__kf_PPL_ID_1 and REL__kf_PPL_ID_2 – these are the 2 foreign keys to the PPL table, i.e. the 2 people in the relationship.
  • REL_1’sGender and REL_2’sGender – the gender of each.  This is needed in order to control:
    • The value list of potential relationships for this person – e.g. a female cannot be a father.
    • The “antonym” for the relationship – e.g. once we know that Homer is Bart’s father, we need to know whether Bart is male or female to know whether Bart is Homer’s son or daughter.
    • REL_1is2’s___ – i.e. the nature of the relationship from the perspective of REL__kf_PPL_ID_1.  If ID_1 is Homer, this field holds “father”.
    • REL_2is1’s___ – i.e. the nature of the relationship from the perspective of REL__kf_PPL_ID_2.  If ID_2 is Homer, this field holds “son”.

In the complementary record, the IDs (and therefore the descriptive fields) are the other way around.  This post doesn’t cover the writing of these REL records, but it’s fairly straightforward.  (Maybe I’ll document that later.)

Showing related people

In the various portals etc., we use a combination of filtered portals and relationship-driven portals to show the various types of related people.

Showing all related people

To show all people related to a person, a portal displaying “PPL to REL” is the easiest way.

The PPL to REL relationship is thus:  PPL__kp_ID = REL__kf_PPL_ID_1.

The extension of the relationship is “back” to PPL, to see details of the related person.  The PPL to REL to PPL relationship is thus:

PPL__kp_ID = REL__kf_PPL_ID_1
REL__kf_PPL_ID_2  = PPL__kp_ID

The portal of all relationships when REL__kf_PPL_ID_1 is Homer will display (for example):

  • PPL to REL to PPL::PPL_Name  (i.e. “Bart”)
  • PPL to REL::REL_2is1’s_____  (i.e. “son”)

Showing specific types  of related people

Portal filtering is the best way to control what types of relationships are shown in a given portal.  (This would not be the case if we were dealing with more than a few relationships per person, after which point portal filtering becomes rather slow.)

So, for example, if we need a portal to show the parents of Bart, we would use the portal described above, but filter it, thus:


Portals to show “children of…” etc. are similarly straightforward, along with perhaps a “catch-all” portal to show “other related people”, using a “not equals” filter, thus:


Showing siblings

Siblings presents a special case.  Although it’s possible to specify that “1 is 2’s bother/sister”, we shouldn’t have to, when it can be deduced (i.e. if 1 and 2 have one or both parents in common).

So for this we can drive the portal with a relationship, thus:

PPL to REL to PPL to REL to PPL, where related people at the right end of the squid leg are the siblings of the person at the left end.

I’ve used match fields – so the first PPL to REL relationship is thus:


(where PPL_MatchTypeParent holds “mother[cr]father”).  So, using this relationship, PPL to REL to PPL gives the parents of the person on the left.

For the second “step” of the relationship, i.e. PPL to REL to PPL to REL, we use another match field, PPL_MatchTypeChild, which holds “son[cr]daughter”.  The relationship is set up thus:


So, where the first step of the relationship gives us “parents of Bart”, the second step gives us “children of the parents of Bart”, i.e. Lisa, Maggie, plus, of course, Bart himself.

All that remains is to show the “siblings” portal, based on PPL to REL to PPL to REL, with, crucially, a filter to exclude the person at the start of the chain (Bart), thus:


Filtering Portals, Method 2 – Using a Relationship for Filtering

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.)


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.