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: – downloadable from the “Box” over on the right.

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!

Layout Tip – Tidy Pop-Ups On iOS

Pop-up menus in Filemaker Go are great, but have an annoying behaviour.  If, after selection from the menu, you tab automatically to a text field, the on-screen keyboard is displayed.  This is ugly.  You can, of course, remove the pop-up field from the tab order, but this has the effect of leaving the menu displayed after item selection – also ugly.  The solution is to have an OnObjectModify on the pop-up field, which just does a Commit Record.  This has the effect of removing the pop-up menu, which, combined with removing the field from the tab order, gives the calm, elegant behaviour that we seek!  (Thanks to Mike Mitchell on Filemaker Technet for pointing this out.)

“Tidiest Drop-Downs” – Part 2, Finishing Touches

In the previous post, I documented how I plan to use a combination of drop-downs and pop-ups to get the best of both worlds, in terms of user-friendliness.  I’ll call these “tidiest drop-downs”.

But there’s one problem with the technique, which is this: because the key field is the one that the user actually enters data into, it’s possible for the user also to enter invalid data.  The intention is to limit the selection to items that are in the value list, but if the user double-clicks in the field and starts typing, invalid data may be left in the field.  (Of course the user may, by chance, enter a valid key value – just as a chimpanzee, left to its own devices, may eventually type the Complete Works of Shakespeare.  But it’s unlikely, to say the least.  And I’m not for a minute implying that my users are in any way like chimps…)  So anyway, we need to use a triggered script to make sure that this doesn’t happen.

On the key field, I’ll add an “OnObjectModify” script.  This will run either when the user selects a key value from the drop-down list, or when s/he types in the field.  The pseudocode for the script is:

Check whether the field holds a value that exists in the value list.
If so, do nothing.
If not, display an error message and offer the drop-down list again.

A couple of pre-requisites for this script;

  1. It has to be as portable as possible.  (I don’t want to have to write a new iteration of the script for every drop-down in the system.)
  2. Once the script has detected invalid data in the field, I want to throw the user back into the field, complete with drop-down list displayed (without the user having to click into the field again).

So this is how it turned out in FileMaker:

valid_dd_scriptNote that the script uses two custom functions, both of which can be found on Brian Dunning’s site.

  1. “GSP” – which gets the specified parameter from the parameters passed to the script.
  2. “FilterList” – which checks for the presence of a value in a given list.  (It does a lot more than this if you want it to, but that’s what it does here.)

Note also that you need a “dummy” field to go to, either during or after validation.  I have the dummy field (a global) parked just off the displayed area of each layout in the system – it often comes in handy.

So, the first parameter is a comma-separated list of all the (key) values in the value list that is attached to the field. (They have to be comma-separated, rather than cr-separated, so that the list will be treated as one parameter.)

The second parameter is the OBJECT NAME (not field name) of the field being validated.  We need this in order to get back to the field if validation fails.  So, for the script to work, you must give the field an object name.

So the script checks whether the contents of the active field exists in the list of values.  If it does, fine – just go to the dummy field, and end validation.

If the value doesn’t exist, then something’s obviously wrong, so show the custom dialog, initialise the field, then go back to the field in question.  Note the need to go to the dummy field first – if you don’t (i.e. if you go straight to the field being validated), the drop-down list will not be displayed – you haven’t actually left that field yet, hence the need for the little round-trip.

Note also the need to use “Go to object” rather than “Go to field”.  Although we can pass the field name as a parameter to the script, we can’t use that information once we get here, because FileMaker haven’t yet come up with a “Go To Field By Name” script step.

And that’s it.  A nicely portable validation script to help keep the solution free of invalid data.