For years, FileMaker developers have been devising various *filter* techniques. The idea is that you type all or part of a name, part number, description, etc… and a list of results filters down to show relevant matches. As handy as these techniques are, they always stop just short of perfect because, before FileMaker 10, you had to *exit the field* before the filter would take effect. Using FileMaker 10’s powerful Script Triggers, you can make the impact of your filtering more immediate.
>Note: We’ve never done this before, but the idea, scripts, and sample file for this technique come directly from FileMaker luminary and Six Fried Rice reader David Graham of [Bit Tailor][bit]. We’re publishing it here (with David’s permission) because it is an excellent example of Script Triggers solving an age-old problem in an elegant way.
[bit]: http://bittailor.com “Bit Tailor: Software with a Perfect Fit”
There are as many ways to filter lists in FileMaker as there are developers implementing it. The beauty of this technique is that it doesn’t matter if you prefer exploded keys and filtered portals, aggregate text fields and a find, or (my personal favorite) scripted multi-request finds. Whatever you want, when it is fast, clean filtering you’re after, it is *always* better if the results appear as you type. This sort of thing was essentially impossible with FileMaker 9. Once a user started entering data in a field, you were hands-off until they finished.
Some people dreamed up horrifying solutions with looping scripts and all manner of wackiness. These techniques, in my opinion, *never* worked well. Consequently, we were always resigned to the “press Enter when you’re done” model. But when the results appear as you type, the user gets immediate feedback, only needs to go as far as necessary to get the result she wants, and doesn’t have to click back into the field to correct or make a change. So it is clearly a better model. See for yourself:
David’s technique relies on the handy `OnObjectModify` trigger on the filter field to kick off a script every time the field changes. This includes:
* When you add a single letter to the field by typing a key.
* When you remove something by pressing Delete or Backspace.
* When you cut text form the field or paste it in.
You may be tempted to use the `OnObjectKeystroke` trigger for something like this, but `OnObjectModify` gets you more bang for the buck: It handles cut and paste, and doesn’t require [all the complexity][key] of keystroke triggers.
[key]: http://sixfriedrice.com/wp/script-triggers-using-the-keystroke-trigger/ “An article about keystroke triggers”
## The Code
I’ll let you explore David’s sample file for the full scoop, but the core of his technique can be found in two places. First, he uses an exploded key filtering technique, which I won’t cover her, except to say that the idea is to process some text data and produce a multi-line key that includes partial match values. In other words, if the value is “Test” then the key would match “T”, “Te”, “Tes” and “Test.” In this way, you can type all-or-part of the value and see the results. Look at David’s `ExplodeToMultikey` custom function to see how he accomplishes this.
If you prefer, you can use scripted finds and a list view layout to do your filtering instead.
But the interesting part is this very simple trigger script:
Set Variable [$currentObject; Get ( ActiveLayoutObjectName )]
Commit Records/Requests []
Go to Object [ $currentObject ]
Set Selection [ Start: Length (Get(ActiveFieldContents)) + 1]
That’s it. This script really just does two things:
1. It commits the record, causing anything you’ve typed in the filter field to take effect.
2. It returns you to the field, so if you type again, your keystrokes will go right on the end of the field.
In this way, as you type, your changes reflect immediately. Very cool.
>Note: If you were using finds instead of exploded keys, you would add a a few more steps to your script to switch to Find mode, build a find request or two, and then perform the find.
You can [download the working file right here][dl].
[dl]: http://sixfriedrice.com/wp/wp-content/uploads/2009/01/spotlight-filter.zip “David Graham’s filter example file.”
Thanks to David for the great technique!
Sight unseen I’ll be sending my thanks for providing this. I’m sure I could have figured it out eventually, but time is money and you just saved me a bundle.
I was in the process of trying to solve this very issuse. Your solution with the neat use of functions and variables makes this a wonderful simple solution to the problem. My favorite kind
Thanks
Thanks, Geoff, for explaining and sharing David’s “spotlight-filter” technique file here.
@Geoff: I’m glad to give back for once after taking so many good ideas from your site. Thank you!
A special thanks goes out to Tom from TechTalk — he doesn’t sign his posts so I only have a first name — for recommending using the OnObjectModify event. My first demo was an incredibly complicated approach using keystroke events, and his suggestion made it so much simpler. As Geoff says, “…if you want me to learn something, send me to school. If you want me to learn it well, bury me in it and make me dig my way out.” It took me awhile but I did manage to dig my way out. 😉
– Dave
Replace the third line with this:
ExplodeToMultikey ( Right ( string ; Length ( string ) – Position ( string; ” “; 0; 1 ) ) ) & “¶” &
and you will be able to search on any word in the field, not just the first word.
oh.. forgot to mention… you would have to refresh the indexing of the Name Spotlight Index field (turn it off, save, and then turn it back on).
Indra,
Can you specify where to make your suggested change?
I added the find-based filter (instead of a portal) and tested with 400,000 records. Lightning fast!
I have used other database development systems that offer this kind of functionality built-in. For example, Visual FoxPro had a function called IncrementalSearch().
A client wants me to re-design a twenty-five year old database app. They do heads-down data entry and this “spotlight filter” functionality is one of the most important things to her. Without it, I wouldn’t get the job. I’m just learning to use FMP and was discouraged to learn that I would have to re-invent the wheel.
Thanks to David Graham for saving everybody a great deal of time and money. It’s an elegant solution.
Per Indra’s last point on turning off/on indexing, you need to also make sure “Automatically create indexes as needed” is enabled; and, of course, do NOT select “Do not store calculation results – recalculate as needed”.
Be sure to give the spotlight field an object name for this to work properly.
Geoff and David, thank you! I used this technique to redesign a client’s system. I actually wanted to get away from filtered portals so my script does a Find in a List view.
I had just finished implementing it when I stumbled upon the recent “Ergonomic Stimulus Contest” run by Albert Harum-Alvarez of Small Company. So I submitted an entry and won 2nd place! Your technique is a winner! I gave you guys credit for the technique in my entry and presentation. Thanks again.
Hi great mod
However – just tried Indra’s mod to make it list for any word in the field. But it works just the same!
I have followed the instructions, carefully, copied out the line of text to modify the function and refreshed the indexing as specified. – no change.
The index field lists as the example below – is this correct? (surely it should just list the words individually)?
F
Fo
For
Ford
F
Fo
For
Ford
Ford
Ford M
Ford Mo
Ford Mot
etc …
@Anatole: You might be better served by the following variation. NB, this technique is a bit expensive and likely wont be appropriate for large numbers of records and/or WAN deployments. In that event I’d use the find mode variation of the same technique.
Let( [
secondWord = MiddleWords ( Name ; 2 ; 1 ) ;
thirdWord = MiddleWords ( Name ; 3 ; 1 ) ] ;
ExplodeToMultikey ( Name )
& Case( not IsEmpty( secondWord ) ; ExplodeToMultikey ( secondWord ) )
& Case( not IsEmpty( thirdWord ) ; ExplodeToMultikey ( thirdWord ) )
)
@Indra: you shouldn’t have to refresh the index. Modifying an expression in a stored calculation field will automatically force the host to reevaluate the field and it’s index.
– Dave
@Tom. That’s great news … Congratulations! Thanks for the attribution.
– Dave
Obviously a fine solution where previously only the Fusion Reactor plug in would do this. Saw it first in the FMP10 ‘Trailer’ seminar from FileMaker.
I adjust it mildly, cos i use mine as search fields and am not always editing the last letter, but also previous words:
Set Variable [$currentObject; Get ( ActiveLayoutObjectName )]
Set Variable [$currentSelectionStart; Get ( ActiveSelectionStart )]
Commit Records/Requests []
Go to Object [ $currentObject ]
Set Selection [ Start: $currentSelectionStart ]
This means that wherever you edit in the field you are sent back to relevant position. To be sure a minor adjustment! 😉
Blessings, T
I had an hour or so free this morning so decided to see if I could create my own take on this (and fix the first word filtering problem whilst I was at it). It requires a couple of new functions-
I’ve called the first one: TextToList (String ; Numeric )
Let ( [
Counter = Length ( GetValue ( String ; 1 ) ) ;
NewCounter = Numeric ;
NewList = String & “¶” & Left ( GetValue ( String ; 1 ) ; NewCounter ) ;
NewCounter = NewCounter+1 ] ;
If ( NewCounter 1 ; TextToList ( MiddleWords ( String ; NewCounter ; 1 ) ; 1 ) ; “” ) ;
NewCounter = NewCounter+1 ] ;
If ( NewCounter ? Counter ; TextWordToList ( String ; NewCounter ; NewList ); NewList ) )
Once again, you’ll need to call this with a Numeric value of 1 and also a StringList value of “”.
Finally you’ll need to create a calc field in the portal table that has the following:
TextWordToList ( SearchField ; 1 ; “” ) & “¶” & TextToList ( SearchField ; 1 )
SearchField should be the field that you’re try to search on (eg a full name field).
This calls the two functions in a useful way and will convert the string Tom Thumb into :
Thumb
T
Th
Thu
Thum
Tom Thumb
T
To
Tom
Tom
Tom T
Tom Th
Tom Thu
Tom Thum
By calling the functions in the way described, the first word doesn’t get repeated which saves on some disk space / filtering speed with larger databases. From this list you can search on the words Tom, Thumb, Tom Thumb, Thu, etc and get a positive result. Combine this with the OnObjectModify script trigger and you’ve got a rapid portal filter that will search on any word not just the first one.
Note : There are a couple of spurious carriage returns at the start of the calc field but I only had a short while to run through it and have only just noticed their presence – they’re easy enough to remove though : )
Now with the advent of FMP 11 and the “Quick Find” script step, all *elected fields are included in this search.
Set Variable [$currentObject; Value:Get( ActiveLayoutObjectName )]
Set Variable [$query; Value:Get( TABLE::g_search_field )]
Commit Records/Requests [ ]
Perform Quick Find [$query]
Go to Object [Object name: $currentObject]
*fileds can included in the Quick Find by selecting the checkbox in the new Inspector / Data tab / Behavior section.
robear
Is the explodetomultikey am add-in or non-Filemaker function?
I’m using FM Pro 11 and FM Pro 11 Advanced and I’ve searched for explodetomultikey with no luck in finding.