Live, As-You-Type Search in FileMaker

FileMaker 10’s Script Triggers feature opens the door to create richer, more dynamic and more responsive user interfaces.  For example, it is now possible to create as-you-type search functionality similar to that found in iTunes and Mac OS X Spotlight where the list of results updates dynamically as the user types. This is a slick feature. I’ve added this functionality to a few of my client’s databases, and it has never failed to elicit a glowing response.

Place a global search field with a script trigger in the header of your list layout for live search-as-you-type functionality

Place a global search field with a script trigger in the header of your list layout for live search-as-you-type functionality

Step One: Create a Global Search Field

This step is the same as Step One in my article, Google-Like Searches In FileMaker. If you have already done this for that project, you can skip this step and use the same field. Otherwise, create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below.

Place a global search field with a script trigger in the header of your list layout for live search-as-you-type functionality

Under the storage tab in the field options, select the “Use global storage (one value for all records)” check box.

Tip: To keep your data tables clean, I recommend having a separate table to hold global fields, such as this one, that are not directly associated with a particular table. Global fields can be accessed from any context whether or not a relationship exists between the tables.

Step Two: Create the Triggered Find Script

Create a script that will be triggered when the user enters text. The script will be similar to the one created in the Google-Like Searches In Filemaker – Non Contiguous article except that it will also capture the cursor position within the find field and return it there when complete, and it will not present an error dialog if the found set is empty. Here’s an overview:

  • Set variables to store the position of the cursor in the search field and the selection size.
  • Enter find mode (do not pause or specify find request).
  • Loop through each search term in the global field, create a find request and use the “Set Field” script step to set the find criteria in each field to search. (For more information on understanding this looping process, see Google-Like Searches In Filemaker – Non Contiguous.)
  • Perform Find in first loop iteration and Constrain Found Set in subsequent iterations.
  • Use the Set Selection script step to place the cursor back in the search field at the position stored at the beginning of the script.

The script will look something like this:

Set Variable [ $selectionStart ; Get ( ActiveSelectionStart ) ]
Set Variable [ $selectionSize ; Get ( ActiveSelectionSize ) ]
Commit Records/Requests [ No dialog ]
Set Variable [ $searchList ; Substitute ( g_SearchField ; " " ; ¶ ) ]
Set Variable [ $i ; 1 ]
Loop
Exit Loop If [ $i > ValueCount ( $searchList ) ]
Set Variable [ $thisTerm ; GetValue ( $searchList ; $i ) ]
Enter Find Mode [ ]
Set Field [ Contacts::First Name; $thisTerm ]
New Record/Request
Set Field [ Contacts::Last Name; $thisTerm ]
New Record/Request
Set Field [ Contacts::Street Address 1; $thisTerm ]
New Record/Request
Set Field [ Contacts::City 1; $thisTerm ]
New Record/Request
Set Field [ Contacts::State Province 1; $thisTerm ]
New Record/Request
Set Field [ Contacts::Postal Code 1; $thisTerm ]
Set Error Capture [ On ]
If [ $i = 1 ]
Perform Find [ ]
Else
Constrain Found Set [ ]
End If
Set Error Capture [ Off ]
Exit Loop If [ Get ( FoundCount ) = 0 ]
Set Variable [ $i ; $i + 1 ]
End Loop
Set Selection [ Globals::g_SearchField; Start Position: $selectionStart; End Position: $selectionSize ]
Tip: Maintaining performance and responsiveness is critical in this script since it will be run at each keystroke. To help with this, I recommend the following:

  • Add a Freeze Window script step to the beginning of this script and a Refresh Window script to the end. Doing so will help to improve performance and eliminate any possible screen flashes. This is a good general procedure to practice in any of your scripts that change modes, found sets or layouts.
  • Use only fields that can be indexed in the search. Avoid related fields or calculations that include them.

Step 3 – Set the Script Trigger

In layout mode, right click on the search field and click “Set Script Triggers …” A dialog will appear:

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

Select the OnObjectModify action which will run the specified script any time text is entered in or deleted from the field. Click Select and choose the script that you created in Step Two.

…And You’re Done!

Now the user can simply begin typing in the field and their search results will be refined as they type without requiring that the user enter find mode, know which fields to search or even click a button.

If you have any questions or comments feel free to post a comment below or contact me directly.

50 Comments

  1. Kevin Smith
    Posted November 11, 2009 at 9:57 am | Permalink

    Hi Danny

    It worked like a charm. Good, simple explanation too. Thanks very much.

    Regards
    Kevin

  2. Mike Carpentier
    Posted November 19, 2009 at 2:18 am | Permalink

    Hi Danny,

    Works beautifully! Would it be possible to mimic this behavior with a portal? I know about your other articles and have tried them too (and they work too of course).
    But this solution has one big advantage: you can type multiple partial words, in any order, to find the relevant records. And I can’t get that working in a portal-type solution.

    Thanks & cheers –Mike

  3. Denis St-Arnaud
    Posted November 20, 2009 at 3:42 pm | Permalink

    For me, it didn’t… I don’t know why, but the Perform Find[] script step just doesn’t seem to do what it should ( and I don’t know why.

    And this doesn’t work if the records to be filtered are in a portal. And the similar google like search solutions in a portal, given in the related posts, gives unexpected results when searching for multiple words.

    Now I’m frustrated, as I have been trying all day to make this work!

    any advice would be appreciated. either post here or email me.

  4. Denis St-Arnaud
    Posted November 20, 2009 at 3:55 pm | Permalink

    Aaah, I, again, simply used the wrong script step (Perform Find/Replace)

    But I am still having trouble since I use a portal: I have to change layout, show all records, loop through all the records to reset a filter field, then do what the article shows, loop through all records in the found set to set the filter field and then come back to layout showing the portal… doing this every time something is typed in the global field takes time.

    There must be a simpler way to set such a filter field for all found records at once, no?

  5. Posted November 22, 2009 at 8:44 pm | Permalink

    Thanks for the comments and positive feedback.

    Mike and Denis, here’s a couple of thoughts on using this technique in portals:

    Of course, you could combine this technique with the one described in the article, “Google-like Search Through Relationship Filtering,” and the script would only need the first three steps and the final step from the example script in this article. But, as you mentioned Mike, the relationship filtering isn’t as flexible as a find script can be.

    One possibility is to use calculated fields in the relationship that splits the words into values on each end (i.e. Substitute ( g_SearchField ; ” ” ; ¶ )).

    Another more flexible possibility would be to have a single, global multi-key field that holds a list of primary keys of all of the search results. This would be similar to what you are doing, Denis, except that the script would simply clear that one global field instead of having to show all records to reset a filter field (which will take longer and longer as the database grows). Do the search, then loop through the found records and populate the global multi-key field. To avoid switching layouts which can disrupt the user experience, you could perform the search in a new window that is sized and/or located to be hidden from the user and close that window once the results are processed.

    I could probably write another full article on this, but I hope this relatively brief explanation makes sense. Any questions, feel free to post another comments.

    BTW: Denis, you are not alone. Using the Perform Find/Replace where you really need Perform Find script step is an extraordinarily common error.

  6. chris
    Posted February 17, 2010 at 11:13 am | Permalink

    Thanks for the tip. Used

    if ($selectionStart=1)
    show all records
    end if

    after Set Variable ($selectionStart……)

    to show all records on full delete of search terms?
    Seems neater to me.

  7. Posted February 18, 2010 at 9:51 pm | Permalink

    @chris – thanks for the idea. The only problem is that the user could end up at position 1 without clearing the contents of the field. If they simply deleted the first character in the search field, all the records would be shown.

    Alternatively, instead of “if($selectionStart=1)”, you could do “if(isempty(g_SearchField)))” to get the desired result. Move that block to the beginning of the script and possibly add an “Exit Script” after Show All Records since it won’t be necessary to continue the script (or put the rest of the script in an “else” block).

  8. Steve
    Posted February 25, 2010 at 1:18 pm | Permalink

    This is great but it does not seem to work with fields that contain numbers. I have to put an * in the field and it is still quirky. Had anyone else ran accross this? Is there an easy solution?

    Thanks,
    Steve (A FileMaker Newbie!)

  9. Ronald
    Posted March 7, 2010 at 1:04 pm | Permalink

    Hello,

    I’m new to filemaker. I tried the script above and got it to work, almost. Every time I type a letter in the search field, the script pauses. I even tried putting freeze window at the beginning. Any idea what I could do to fix this?

  10. Ronald
    Posted March 7, 2010 at 1:25 pm | Permalink

    found my mistake!

    I had Enter Find Mode [pause] instead of Enter Find Mode [ ]. I have another problem now though. As I enter the letters, the cursor stays at the front of the edit box, so the word gets entered in backwards. Any thoughts?

  11. Posted March 8, 2010 at 10:54 pm | Permalink

    @Ronald – Check the first and last steps of your script. It sounds like it is not properly capturing and returning the cursor position. The first step in my example above is grabbing the cursor position within the field. The script then leaves the field and in the last step returns to the global field and restores the cursor to its previous position.

  12. PeterGriffin
    Posted June 11, 2010 at 3:42 pm | Permalink

    Hi Danny!

    You script works like a charm! Thanks for the post!
    I tried to follow your explanation regarding the portal adaptation but I’m not sure what to do. How should I populate the global multi-key field?

    Thanks!

  13. s.c.
    Posted August 3, 2010 at 10:08 am | Permalink

    hi,
    I really like this script.
    I have only a problem.
    When I cancel all the letters from the search field, it show me only a few records, and not all.
    Can u help me please?

  14. Chuck
    Posted September 4, 2010 at 2:22 pm | Permalink

    Beautiful script, great idea to bypass FileMaker’s standard search method.

    How could this be incremental search technique be adapted for ‘heads-down’ data processing, where if you need to find a customer whose LastName is Jackson, if you enter “Jack”, and you don’t want to get records that include everyone whose FirstName is Jack ? Obviously need multiple search fields matched to desired data fields to be searched.

    And could a timer be invoked at a typing pause to “guess” when to perform the search (at least for for fast typists), rather than automatically searching each keystroke ?

  15. Posted September 6, 2010 at 4:58 pm | Permalink

    @PeterGriffin – Use the Set Field script step. Before entering the loop, clear the contents of the field:
    Set Field [Table1::MultiKeyField ; "" ]
    Then within the loop set the field to the the primary key of the current record concatenated with a carriage return and the contents of the multi-key field:
    Set Field [Table1::MultiKeyField ; Table2::_pk_theID & ¶ & Table1::MultiKeyField]

  16. Greg Hains
    Posted September 16, 2010 at 3:10 am | Permalink

    Hi there Danny,

    Thank you very much for this live search script – I have been looking for something like this for quite a while. I’m just hoping I can contribute back to the FmPro world soon enough…

    I do have one question though.
    I need to have criteria set for another field in there. I do want it to search all the defined fields but one field to be set.
    I have tried inserting text into the necessary field in the line before the Perform Find but it doesnt help.
    I also tried using the same method at the top with:
    Set Field [ Jobs::Status; "Open"]
    New Record/Request
    but that didnt work either.

    Can you suggest please what I can do?

    Cheers,
    Greg

  17. Posted October 20, 2010 at 8:18 pm | Permalink

    @Greg Hains – To set a criteria for another field, you will need to include the Set Field for that field in every search request. So in the above script, you would put your Set Field [ Jobs::Status ; "Open" ] step after each of the existing Set Field steps. Logically, each New Record/Request step is adding another OR search to the query, so every time you add another New Record/Request step, you will need another Set Field.
    I hope that helps and expect to see your contribution to the FM Pro community soon ;)

  18. Posted October 20, 2010 at 8:25 pm | Permalink

    @Chuck – You could have a couple of global search fields, then in the search script, you would add a Set Field step to each search request. In the above script, you would put Set Field [ Contact::LastName ; g_SearchLastName ] after each of the existing Set Field steps (similar to what I suggested to Greg in the comment above).
    As for the timer, it can be done using the Install OnTimer Script Step. See my recent post, Dynamic Portal Filtering While You Type, for a description on how to do this. The method explained in that article will work just as well with this method.

  19. Posted October 20, 2010 at 8:30 pm | Permalink

    @s.c. – Make sure you include the Commit Record/Request step at the beginning. Also check the Set Variable [ $i ; 1 ] step. Make sure you are setting it to 1. I have seen similar behavior to what you describe when the 1 was missing from that step. Otherwise, if you can send me a copy of the script text, that would be helpful in troubleshooting. You can open the script and print, then save as PDF if you are on a Mac.

  20. Mike Wells
    Posted March 15, 2011 at 2:28 pm | Permalink

    OK, so I’m a little lost here. I’m a newb at this trying to set up a database, and I can follow most of the steps here, and I know where to substitute my fields for the ones you posted, such as in the ‘Set Field [ Contacts::City 1; $thisTerm ]‘ line, I just don’t know what I am doing wrong. At the beginning, where you have the line Set Variable [ $searchList ; Substitute ( g_SearchField ; " " ; ¶ ) ] ‘g_searchfield’ produced an error, which I thought meant that had had to name it the same as the field I had created for this, but that doesn’t work either. Not sure what I’m doing wrong here.

  21. Mike Wells
    Posted March 22, 2011 at 4:49 pm | Permalink

    I’ve fixed my previous issue. Everything is set, except I seem to have the same issue that Ronald originally had, it just goes into a loop as soon as I enter a single character. I have to kill the FM task and reopen to continue.

    I was able to follow these instructions except for one thing: In the ‘Set Variable’ strings, I have it the same, except it alwauys says ‘Value’, as in ‘Set Variable[ $selectionStart ; Value:Get (ActiveSelectionStart )] . This seems to be the only thing different(Except pointing to my own fields, of course), so I’m not sure where I have messed up.

    Any help would be greatly appreciated.

    PS, Unlike Ronald, I don’t have the Enter Find Mode set to ‘pause’, so that’s not it.

  22. Posted April 6, 2011 at 7:17 pm | Permalink

    @Mike Wells – It is difficult to troubleshoot your specific problem without seeing your database. However, I will venture to guess that the problem is with one of the “Exit Loop If” steps or the Set Variable [$i ; $i + 1] step. Feel free to email me a screenshot of your script if you continue to have trouble.

  23. bob
    Posted June 11, 2011 at 6:26 am | Permalink

    NICE … works like a charm. Thanks for sharing

  24. Soren
    Posted November 30, 2011 at 6:12 am | Permalink

    Hi,
    It works great except for one thing. Right after pressing a key it returns the dialogue box “Before typing, press Tab or click in a field, or choose the New Record menu command”.

    Btw: Inserting a Show All Records in the very beginning of the script makes sure that it doesn’t only iterate new searches, but searched through all records every time.

    /S

  25. Soren
    Posted November 30, 2011 at 6:57 am | Permalink

    OK I found the mistake of course. Very stupid. The script referred to a Container field…

    Now it’s working great!

  26. Posted December 1, 2011 at 2:41 pm | Permalink

    I just created this script in our database–and it’s EXTREMELY cool! Thanks you for posting this!

  27. steveald
    Posted March 12, 2012 at 8:21 am | Permalink

    Great solution! I stumbled with some of the concepts found in “Dynamic Portal Filtering While You Type,” took a step back, found this process does basically the same thing, and am one happy camper. Thanks!

  28. Charlie
    Posted November 9, 2012 at 11:55 am | Permalink

    That’s a great solution.
    Thanks a lot for describing it !

  29. gerald
    Posted November 27, 2012 at 9:55 am | Permalink

    This works great, thank you!

    Still new to FM, and I’m having trouble figuring out how to solve this problem.

    When someone makes an error in the inputing the global field, and pressing the back button to erase, the script seems to fail to display records that it may match. It seems one has to backspace until the global field is empty and start again.

    Is there a way to display matching records when backspacing?

  30. Posted November 27, 2012 at 12:46 pm | Permalink

    @gerald – As written, the script above should work just as well for any field modification, whether typing or backspacing. I’m not sure what would cause that. If you send me a clone of your database, I might be able to figure out what is going on.

  31. Damien
    Posted November 28, 2012 at 9:23 am | Permalink

    Hi Danny,
    Thank you for sharing this great tip.
    I have only a little problem, while omiting a record after I typed in a key word in the find bar, then click on show only omit records, it seems that if I repeat this operation it shows more records than I omitted.
    In fact, I want to create some “custom list” of records in view mode, after selecting the one’s I need.
    It would be nice If you have a solution to that kind of task I need to do.

  32. Posted November 28, 2012 at 11:30 am | Permalink

    @Damien – The built-in “Show Omitted Only”, will show all records that are not in the current found set. I think the functionality you are looking for is in my article Checkbox Record Selection In A Multi-User Environment. This will allow you to select a set of records hand-picked from one or more searches and bring up the list of selected records.

  33. Damien
    Posted December 7, 2012 at 11:33 am | Permalink

    Hello Danny,
    Thank you for your response, I’ll have a try soon and keep you inform of my progression.

  34. Jake
    Posted March 14, 2013 at 4:07 am | Permalink

    Hi Danny,

    Great tip! It’s working beautifully for me. Any way to throw in a functionality where once the desired record shows itself and you press “Return” or “Enter” for the typed contents of the global field to be erased? Only reason I ask if because I also have some buttons that allow users to flip back and forth between records and if the value in the search field stays the same it looks a little confusing. Thanks again!

  35. Posted May 9, 2013 at 12:48 pm | Permalink

    Hello! Thanks for this!

    Question…this is now built into v12…however I have a user that’s asked if it could not only filter using the first word in a list but if the search term appears anywhere in the field.

    For instance if the user starts typing “Mart”
    It would show not only…
    Martian
    Martin
    Marty

    But also…
    Doc Martin
    My favorite Martian

    Does that make sense? It’s an odd request I know but just curious if this is possible…thanks!

  36. Posted May 9, 2013 at 1:48 pm | Permalink

    @Joshua – I’m not sure what you mean when you say it’s built into v12. There’s the quick search feature which almost, kind of, sort of does this, but not quite. That’s not new to v12. But anyway, yes, this method does search anywhere in the field using the native FileMaker search. It also splits up multiple word queries so that if you type “Doc Mart”, it will find “Martian Doctor”, “Doc Martin” and a record with “Mart” in one field and “Doc” in another field.

  37. Posted May 9, 2013 at 2:00 pm | Permalink

    @Jake – It is possible to respond to a “Return” or “Enter” keystroke by using the “On Keystroke” script trigger and have it perform some operation if the “Code ( Get ( TriggerKeystroke ) )” is equal to 10 (Enter) or 13 (Return).

  38. olly
    Posted May 15, 2013 at 3:35 am | Permalink

    @Danny
    Many thanks for this – it is brilliant!
    I do get a severe lagging when using the search on a FileMaker Go database on a iPad…

    Do you think it could be possible to perform the find when a user pauses from typing for lets say 1 second? I guess that way the iPad wouldn’t need to deal with performing find after find on each keystroke?

    Any thoughts would be greatly appreciated.
    TIA

  39. Posted May 15, 2013 at 9:23 am | Permalink

    @olly – Yes, it is possible to perform the find only when a user pauses from typing with the Install OnTimer Script step. Take a look at the article on Dynamic Portal Filtering While You Type. Near the end of the article under the header Enhancements and Delayed Search for Fast Typing, I explain this method.

  40. flybynight
    Posted June 6, 2013 at 9:51 am | Permalink

    @Danny – thanks, this is awesome!
    Glad to see that you are still getting impact from it over 3 1/2 years later. That’s an eternity in web-time! HA!
    Just a quick question about performance. My layout that I’m using this on is relatively simple. It’s based on the Contacts starter solution list view, and just shows Company, Name (concatenated First, Middle, Last), phone and email. The only fields I’m actually wanting to search are Company and Name.
    The Company field is a auto-enter calc from the Companies parent table CompanyName field. I have another field Company_Contact that use for a value-list picker in another table/layout that is a concatenation of Company, First, Last.
    I know that you said to avoid calc or related fields, but I’m just wondering about the performance trade-off from searching ONE calc field vs. the script adding a request and setting THREE (or more) separate fields.
    Right now my Contacts list is only a few thousand and I aimed the search field at the one Company_Contact calc field. Performance is very responsive now, but I expect the list to maybe double over the next couple of years and so far we aren’t using FM Go, but that may change.
    I’d appreciate your thoughts or findings if you have tested the difference on larger solutions.

    Thanks!
    -Shawn

  41. Posted June 6, 2013 at 8:24 pm | Permalink

    @Shawn – There is no inherent problem with using calculated fields, if the calculation is in the current table and can be indexed (not unstored). If it references fields in related records or fields that are unstored, there is a potential for performance problems as the database grows. If it is performing well now, it should be fine if the database doubles. If you expect to have 10s of thousands of records in the future, the potential for performance problems with unstored calculations or fields in related tables will become significant. As for the performance tradeoff, for scalability, you will be better off searching 3 or even 10 fields that can be indexed rather than 1 field that is unstored or in a related table.

  42. David Wollesen
    Posted June 9, 2013 at 6:26 am | Permalink

    Hi, thanks for the awesome script!

    I’m new to FM.
    I seem to have the same problem as Steve. It doesn’t seem to work as well with numbers for me.
    Lets say I want to find invoice #5700.
    When i start typing 5 all records disappear but if i type the whole number (5700) it shows me the one record.

    very strange to me but don’t know if i messed up somewhere.

    I would also like to have like a “clear field” button og feature, like in the “built in” search field in the toolbar. Is that possible some way?

    Thanks for all the help! I really appreciate this.

    David Wollesen

  43. Posted June 10, 2013 at 8:26 am | Permalink

    @David – The search works the same as any standard FileMaker find, so the same rules apply. In this case, for number fields, you’ll want to add an asterisk to the search criteria for the number field. So, the Set Field will look something like this:
    Set Field [ Table::NumberField; $thisTerm & "*" ]

  44. David Wollesen
    Posted June 10, 2013 at 8:59 am | Permalink

    Thanks! Awesome.

    Is it possible to make a “clear search” button that also deselects the search field?

    Because it’s kind of annoying in FMGO when entered the search field, the field itself expands downwards so some of the list is hidden behind the active search field. (only in FMGO) (it works perfectly in FMP)

    And do you know any way to limit the size of the active field when used in FMGO?

    Thanks again,
    David

  45. robert
    Posted August 21, 2013 at 8:53 pm | Permalink

    Great tip. I was going to implement this but I came across the new “quick find” feature in FM11 which now lets you do simila.

    Just use an OnObjectModify script-trigger on the “GlobalSearch” global field. The scripttrigger launches a script containing the command: Perform Quickfind (GlobalSearch). That´s it.
    In addition I recommend activating a OnLayoutKeyStroke scriptrigger on the layout (not on the object) so that it catches an passes any key to the global field in case that field is not on focus. The contents of such script is a simple: allow user abort off, set error capture on, goto field Global field. That way the user does not need to click into the search box. just type and it finds.

  46. Lucas
    Posted March 19, 2014 at 8:52 pm | Permalink

    Excellent tip. This thing never gets old!

    Had a little trouble with it at first, only finding the right step translations as I was working in french. But everything works great.

  47. Scott
    Posted May 8, 2014 at 11:00 am | Permalink

    I love this tip! I used it on one of my databases with no problems at all. But I’m trying to use it on another database right now, and I’m having issues with screen flashing while typing in the search field.

    I’ve tried the freeze and refresh tip, but it has no effect.

    Any Ideas :)

  48. Gerald
    Posted July 29, 2014 at 10:47 am | Permalink

    Hello,

    I am not sure if these posts are being actively reviewed but…

    I’ve been using this feature and it works well.
    However on the new WebDirect feature, the cursor I believe is jumping somewhere and therefore it is not searching properly.

    Does anyone have any ideas how to counter this?
    Somewhere on the internet it suggests Exit Script [False] but not sure where to place this.

    Thank you

  49. Gerald
    Posted July 29, 2014 at 11:25 am | Permalink

    I think I found the answer. It has to do with the “bug” in WebDirect where SetSelection fails to work properly.

    So far there doesn’t seem to be a workaround.

    I assume we will all wait for the fix.

  50. David Wollesen
    Posted September 2, 2014 at 6:16 pm | Permalink

    Hey again. I have really enjoyed using this feature for a long time. Does anybody know how to change the record sort order while typing in the search field as well as when clearing the search field?

    I have an invoice list sorted with the newest ID on top. When I start searching it flips it around so the oldest number comes on top. The same thing happens when clearing the field. I have tried to insert a “Sort Records” step during the script, but that seams to deselect the search field every time it uses the trigger. I might have inserted it the wrong place.

    Does anybody know how to solve this? I would really appreciate it.

    Thanks.

Post a Comment

Your email is never shared. Required fields are marked *

*
*