This is a little technique that I showed recently at the Melbourne and Sydney Technet meetings and I wanted to document it here. It’s a user interface idea we’ve used in a few solutions recently, and it works well for some particular use cases. Demonstrating this was a way to show how we moved from a hard coded, non optimal find to one that is completely abstract-able and extensible as well as being optimised for unstored find criteria.
The idea
The basic plan is having a row of filter fields at the top of a list. So from this screenshot :
As you can see in this example, every field in the list has a matching search field. You can type anything into those search fields, and through the magic of script triggers, it will do a search on those values and filter the list.
In this case we’re using the OnObjectSave trigger, but you could also quite easily use a Keystroke trigger as well if you knew that the find would be fast enough for it to display.
This type of list filter works really well in this particular solution where you’re constantly looking for a group of records. For example the users here are wanting to know every record that is Ready in the Yarra Valley region, and then work on those.
Idea, meet Reality
Of course the first time you code this, it’s simple, just a find using those values. Not a problem, easy to implement. Then we got into the first issue : This particular database has three fields in that list that are unstored calculations, which when you search on them are very slow.
No one likes an unstored find
I’ve attached a sample file ( SampleData.fp7 ) which has 100,000 records in it. Search on the first field ( stored and indexed ) and it’s almost instant. Search on the second ( an unstored calc on related data ) and it’s horrible slow. I’ve never let it run to completion, but it would take a while. But FileMaker already has a solution to this when you’re doing a find with multiple criteria. Search on the stored fields first and then do the second find as a separate find using Constrain Found Set.
To explain, in the sample data file, do a find on the first field for the value “c”. This will give you a found set of 100 records. Then enter find mode, put “100” in the second field and instead of clicking perform find, go to the Requests menu and select “Constrain Found Set”. This option is almost instant as it only requires FileMaker to examine those 100 records. If you’d done this find as one find with both criteria, FileMaker would examine all 100 records for both criteria, which will be very time consuming.
So our script now needs to take into consideration this process. If all we do is continue to hard code things, the basic framework of our script looks like :
If [ there's data in the first search field ]
Enter Find Mode
Set Field by Name [ the first find field ; the first search field ]
If [ we've done a find already ]
Constrain Found Set
Else
Perform Find
End If
End If
There are two issues with coding this directly. First you’re referencing the search field in two places, which means you have to remember to change both if there are layout changes. Plus you’re repeating yourself with every field, so this could be done with a loop.
Using our name value pair technique I’ve coded up a simple example that does exactly this. The first script is called by the trigger and the second script just runs the loop. Code wise it’s not very tricky but it simplifies this process quite a lot and abstracts out the main loop completely. You can then re-use that same code on many layouts and change the fields in one place whenever there is a code change.
The complete code and a working example is in the QuickSearch file attached to this post.
Improvement 1 – Complete Abstraction
One extra thing that you can do to improve or extend this is to use layout Object Names to reference the fields. That way this script can be moved from any layout to any other without modification. By setting specific object names, the script will loop through the objects, find all the search fields and the global fields and create the criteria to send to the second script.
This technique is similar to the one used in Matt Navarre’s excellent fmSearchResults tool which is a great way to do something similar : add a single google search like field to your solution. His code is much more extensive than this small example and so it’s well suited to using Object Names.
Personally I wouldn’t use object names for implementing my example though, as it has the disadvantage of being easy to break inadvertently when you modify layout objects, but it might suit some developers. I tend to change things on layouts by option dragging, so I’m likely to end up with some objects with broken or not quite accurate names.
Improvement 2 – Show and Hide the bar
A second thing that I’ve been wanting to do for some time is to have the search fields hide or display based on a button. So for some users they could have it not showing and others would have it available. The simplest way to do this would be two layouts, but technically you could also have it there in a Sub Summary part. By sorting on some non relevant field ( a fixed field, or something common to every record ), you could sort or unsort based on that and hide or display the search fields.
You can actually setup sorts on global fields, which are fast in large record sets. You need to add the field first as a normal stored field, and then after setting up your layout parts and a sort script ( or button ) you can change the field to a global and both the part and the sort will continue to work. I’ve added this as well to the example file to see it in action. You’d want to clean up the UI a little for production but the ideas are there.
Again you will have issues if the user has access to the sort dialog to do their own sorts as this would mean their sort criteria would hide the part, and there is no simple way to get it back, but retain their sort.
Improvement 3 – Keystroke triggers using Constrain
I mentioned above that you could use a Keystroke trigger to do these searches, so it’s almost like an instant filter. I see no reason why this wouldn’t work well, except… when your find criteria included unstored fields, in which case it could be slow to be repeating the entire search every time. But the solution to that would be to make two optimisations on the script :
First, only do keystroke triggers on stored fields. This would mean some fields would search differently than others, but I think that is something that could be easily explained and adjusted for in the user expectations.
Second, with each additional keystroke, do a new constrain find, based only on the current field that the user is in. This way you’re not repeating the find criteria that haven’t changed at all and really optimising the search. You would though need to be aware of all of the other characters like a backspace that would extend, not reduce the found set and alter the script accordingly.
I haven’t done this in the sample file, so it’s left as an exercise for the reader 🙂