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: QuickFindDemo.zip

The Interface

Here’s how the finished interface looks:

qf1

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:

qf2

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:

qf3

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:

qf6

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!

Advertisements

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_field_defs

  • 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:

rel_filter1

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:

rel_filter2

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:

rel_rel1

(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:

rel_rel2

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:

rel_filter3


Data Separation – A Couple of Pitfalls

Well, the data separation exercise has been going pretty well, and I can really see how it will make life easier, now that several organisations will be using the system.  Swapping in a new interface file will be a doddle, with no need to import data etc.

BUT, there’s no gain without pain, and a couple of things recently have tripped me up.

1. GLOBAL VARIABLES

Global variables do not work across files.  So, where a variable is set in the interface file, and it is used to set a field value, or in a calc field, in the data file, it doesn’t work.  This has been an issue with the “portal sorting” technique that I use, whereby a variable is set to indicate the sort order to be used (“surname ascending”, “date descending”, etc.), and that variable is used to calculate the value in a field in the table (i.e the table on which the portal is based).  The solution is simple, i.e. to use a global FIELD (in the Interface table for example) rather than a global VARIABLE, but it was a bit of a shock to see a time-honoured technique just not work.

2. RUNNING SCRIPTS WITH [FULL ACCESS]

The way I’ve been handling deletions so far is to have delete not allowed for system users, but then, after all the pre-checking and confirming etc., running a delete script with [Full access] privilege.  Worked fine, until the data was separated out into a separate file – the [Full Access] thing doesn’t work across files.

So the possible solutions are:

  • Allow deletion to all users – obviously not really an option.
  • OR…do all the scripting of the deletion routine in the data file – rather messy, and spoils the principle of having no scripts “over there”.
  • OR…set the delete protections to “limited”, and allow deletion only in certain circumstances.

I decided to go for the last option, as it was the easiest to implement, and seems pretty secure.  I have a global field in the interface table (which can obviously be seen throughout the system) called INT_g_DeleteOK.  Once all the deletion checking has been done, the script sets this field to “Yes”, does the delete, and IMMEDIATELY sets the field to “No”.  Obviously, until the field is set, it is empty, so testing for INT_g_DeleteOK = “Yes” seems pretty bullet-proof.


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.