By Daniel Wood, 15 December 2010
Please note that this article is not in regards to portal-level filtering introduced in FileMaker 11. It concerns filtering of relationships by altering contents of fields that are used in the relationship.
This article is also aimed at an intermediate level user. It assumes you already know the basic ideas behind filtering relationships using a filter field.
With the introduction of script triggers in FileMaker 10, live filtering or portals was made possible. Numerous techniques were introduced to help achieve this. In conjunction with existing filtering techniques, the "Live" aspect was achieved usually by introducing an OnObjectModify script trigger to the filter entry field. As soon as the user enters a value into the field, the triggered script would force a save of the fields contents, thus updating the relationship in question, which in turn updated results in a portal on screen.
These filtering techniques work fine on local solutions, and solutions over a Local Area Network, but what happens when the same techniques are used on systems that are used over a wide Area Network - such as the internet?
A basic live filter example
A typical implementation of a live filter is using an OnObjectModify trigger in conjunction with a script. For example:
This script first remembers the cursor position & selection. It then sets the active field to itself, which is sufficient to save the field contents and so force a portal to update its results. The final part of the script restores the mouse cursor position.
When this script is attached to a field using the OnObjectModify trigger you have a basic live filter field ready to be used in a relationship.
Some drawbacks over WAN
The above method updates the field contents after EVERY character inputted. Locally or over LAN, this operation is usually unnoticeable, but over WAN it can be crippling.
In FileMaker, when a record is displayed in a sorted portal, EVERY record in a portal is downloaded to the client machine - regardless of whether it is visible in the portal or not. In addition to this, the ENTIRE record (excluding unstored calcs & container fields) is downloaded to the client. If you have a portal showing 10,000 sorted records, then all 10,000 records are downloaded to your client. If you are connecting over the Internet, then this can be a time consuming operation. They must all be downloaded in order to achieve the sort - an action that happens on the client machine
If you are filtering a portal as you type, then after every keystroke, FileMaker needs to obtain any new records now showing thru the relationship by downloading them. During which time, you will be unable to continue typing into the field until this is done.
When filtering portals there are two ways you can begin - with the portal showing NO records, or showing ALL records. If a portal begins by showing no records, then as soon as you begin typing into the filter field and records are found thru the relationship, they'll need to be downloaded, and this will cause a major slowdown on the initial keypress (or whenever the first set of records are found thru the relationship).
If the portal/relationship begins by showing all records, and you narrow down that set, the slowdown effects of filtering the portal are reduced, but at the cost of a large performance hit in initially loading the portal.
For relationships/portals that are unsorted, FileMaker will download visible records in the portal, so initial load times might be faster, but it still suffers the same fate as you begin to filter, because new records are potentially being displayed in the visible portal which must be downloaded.
The overall performance hit over WAN is noticeable. Users who use filter fields tend to type at a reasonable speed into the filter field - if the speed at which they can do this is reduced due to download times of records, the filter field becomes sluggish and feels unresponsive, which can lead to user frustration.
A nice alternative for WAN performance
Think about a users end goal when typing in a filter field. Most users will know the word/s they want to enter into the field, and will go about typing the entire word until it's entered. For example, if you wanted to locate all people in a database whose name is "Daniel", would you be more inclined to just type "Daniel" and look at the results, OR would you begin typing .... D - (look at results) - A - (look at results) - N (look at results) ... and so on until you see the record you want? I think the first approach is the more common.
So, what about a filter entry field that only shows results once the user finishes typing whatever they want to type? How can FileMaker detect when a user finishes typing into a field? Well, prior to script triggers, a user would have to manually exit the field in order for FileMaker to update the portal. But, by a cunning use of the OnTimer script step in conjunction with an OnObjectModify script trigger, it is easy to detect a pause in a users typing pattern. This pause can be used to update the portal.
This method will allow the user to type their search term at full speed, without constant portal refreshing impeding their data entry. It will then show results when it detects the user has finished typing, or paused for a period of time.
So, how does it work:
At it's most basic level, the method requires two scripts, and a filter entry field.
Script A - Typing Trigger
Script B - OnTimer Trigger
What's Going on in The Scripts?
Lets start with Script A. In this script, you can see it is being passed a script parameter which is set into a variable $ObjectName. The parameter passed is the object name assigned to the filter entry field - this is useful because it makes these two scripts completely generic and they can be reused for any number of filter entry fields in your solution.
The main part of the script is the installation of the OnTimer Script - Script B. We pass the object name to the second script also. I have set a delay of 0.25 seconds on the ontimer. Note that this delay will be the delay between when the user finishes typing, and when the portal results are updated.
The key aspect of this whole thing is that this particular script is the one that is attached to the filter entry field as an OnObjectModify script trigger.
To put it simply, every time the user enters a character, the time to which script B will run is reset to 0.25 seconds. Script B will only run once the user stops typing for that 0.25 seconds.
Script B is responsible for doing the normal filtering tasks. The last step of the second script is to disable the OnTimer script for itself. This ensures that once the user stops typing, this script will run once and once only. If the user then continues typing, then Script A runs and the process repeats itself all over again.
The end result
the end result is a portal which updates it's results after 1/4 second pause of data input by the user into the filter entry field. While the user is typing, nothing happens to the relationship or the portal, resulting in no slowdown of data entry due to records being downloaded. Once the user stops typing, only then is the field contents saved and the relationship/portal updated.
Taking it a step further
I implemented this method in a rather large solution with great results. The system had upwards of 20 filter entry fields on various screens. It soon became apparent however, that for some filter entry fields, numbers were the common type of data entry, whereas others were text/words. For most users, the entry of numbers via a keyboard is a slower task than typing a word. I quickly found that the 0.25 second delay was sufficient for text filter entry fields, but not for those that required number entry, or a combination of text and numbers.
The solution was to pass the Delay to the Typing Trigger script (Script A) as a second script parameter. Now, this script accepts two parameters - the object name, and a delay. For numeric entry fields, a delay of 1 second is more useful because it gives the user time to type in their number. For text fields, the delay can be decreased. To go even one step further, you could make the delay a user preference in the database, so touch typists could have a shorter delay, while a longer delay could be given to "two finger" typists.
While "instant" live filtering is looks great, and provides great visual feedback when filtering, it does have some performance drawbacks most notable over wide area network deployments of FileMaker solutions. Sorting of the relationship/portal also has a great drawback in these situations.
By implementing a short delay between when the user enters into the filter entry field, and when the relationship/portal is updated, an increase in filtering performance can be achieved.