Google-Like Searches In FileMaker

This article is a part of a series on Advanced Search Interfaces.

Using a single, global field and a fairly simple script, you can create a search-engine-like interface for searching the contents of a FileMaker Pro database.

Using a single, global field and a fairly simple script, you can create a search-engine-like interface for searching the contents of a FileMaker Pro database.

Place a global search field and button in the header of your list layout for search-engine-like search functionality.

Step One: Create a Global Search Field

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.

text

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 Find Script

Create a script that enters find mode, creates a new search request for each field you wish to search, copies the value of the global search field to each of those fields and performs a find.

  • Enter find mode (do not pause or specify find request).
  • For each field you want to search, use the “Set Field” script step to copy the value from the new global field.
  • Place a “New Record/Request” steps in between each “Set Field” step.
  • Perform Find.

Here’s a sample of how the script might look:

Enter Find Mode [  ]
Set Field [ Contacts::First Name; Globals::GlobalSearch ]
New Record/Request
Set Field [ Contacts::Last Name; Globals::GlobalSearch ]
New Record/Request
Set Field [ Contacts::Address Type 1; Globals::GlobalSearch ]
New Record/Request
Set Field [ Contacts::City 1; Globals::GlobalSearch ]
New Record/Request
Set Field [ Contacts::State Province 1; Globals::GlobalSearch ]
New Record/Request
Set Field [ Contacts::Postal Code 1; Globals::GlobalSearch ]
Set Error Capture [ On ]
Perform Find [  ]
Set Error Capture [ Off ]
If [ Get ( FoundCount ) = 0 ]
Show Custom Dialog [ Title: "No Records Found"; Message: "No records matched your search criteria."; Buttons: “OK” ]
End If

Tip: Note the use of the Set Error Capture script step sandwiching the Perform Find step. This is to prevent the default FileMaker error which asks the user if they wish to modify the find request or cancel. Neither of these options will result in what the user would expect.

Step Three: Add Layout Objects

You can then place the global field and a search button to call your new script in your list view header, a pop-up window or wherever else you might find it useful.

That’s it! Now the user can simply enter search terms in the search field and click the search button and view their results without ever entering find mode and without knowing which fields to search.

Enhancement – Split Field Searching

One of the shortcomings of this script is that it will only find records that contain all the search terms in a single field. In other words, if you enter “ABC Plumbing Chicago”, it will only find records that contain the words “ABC Plumbing Chicago” all in the same field. If you want to be able to find a company record for “ABC Plumbing” located in “Chicago”, the script would need to split the phrase up and loop through each word. My article, Google-Like Searches In FileMaker – Non-Contiguous, explains a method to accomplish this.

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

22 Comments

  1. Aviv Damari
    Posted December 8, 2008 at 2:20 pm | Permalink

    I have tried the first script and it worked fine. However I copy your script for non- Contiguous and I could not get to work. When I set variable I get the word Value inserted and in your script the word Value does not appear. Am I doing something wrong? Could that be the problem? I like this script very much and I hope you can help us make it work.
    Aviv Damari
    Damari Construction Consulting Inc.
    cell 480510500

  2. Posted December 8, 2008 at 2:33 pm | Permalink

    @Aviv

    I suspect the problem may be in the line : Set Variable [ $thisTerm ; GetValue ( $searchList ; $i ) ].

    Make sure the Value for the variable is exactly “GetValue ( $searchList ; $i )” (without the quotes). If that doesn’t fix the problem, print the script to a PDF or take a screenshot and email it to me.

  3. Bob Smith
    Posted July 15, 2009 at 10:37 am | Permalink

    Thanks so much for the help with the search engine. Works great. What i was wondering was if you know a way that i can format a layout to hide the database and and to show only the search engine and the search results in a separate space. This link shows what I’m looking for: http://www.senasa.go.cr/Medivet/inicio.aspx

    Thanks in advance,

    Bob Smith

  4. Posted July 20, 2009 at 9:06 pm | Permalink

    @Bob Smith – If I understand what you’re trying to accomplish correctly, here’s a general overview of how you could approach this:

    Create a restricted user group that only has access to layouts you create for the search. The layout would have only the objects required for the search, and your startup script would lock the status bar so they could only navigate with buttons you provide in the layout. You could take it a step further and create a custom menu set for those layouts which include only the items you want them to have.

    I hope that answers your question. It’s a very general explanation. Getting much deeper into details could fill a series of articles. Feel free to comment again or click the contact us link below if you want to discuss more specific details.

  5. felisberto
    Posted September 23, 2009 at 5:36 am | Permalink

    i’m a filemaker newbie my question is the same
    i’d like a separate layout to display text results.how to accomplish this?please help me.
    so simple as possible

  6. Rhonda
    Posted February 4, 2010 at 2:33 pm | Permalink

    Thanks so much for sharing your knowledge! I’ve tried the script and am blown away by how much this will open up my FileMaker design world.

  7. Jenny
    Posted January 28, 2011 at 12:47 pm | Permalink

    This looks brilliant, just what I was after – thanks so much for sharing.

    I’m afraid I’m brand new to filemaker but have been set the task of creating an easily searchable contacts database at work. I’m just a little confused when trying to recreate your script, as I can’t find an obvious way of putting in the second value into the ‘set field’, i.e. the “Globals::GlobalSearch” part of this: [ Contacts::First Name; Globals::GlobalSearch ]

    If anyone can enlighten me I’d be awfully grateful!

  8. Posted April 6, 2011 at 7:24 pm | Permalink

    @Jenny – When you select the “Set Field” step, there are two buttons in the Script Step Options in the script window, below the steps of the script. Click the “Specify…” button to the right of “Calculated result:” This will bring up the calculation window where you can enter the “Globals::GlobalSearch” text (or double click the field in the field list.

  9. Erick Bueno
    Posted May 19, 2011 at 5:19 pm | Permalink

    Hi Guys !

    Could anybody send me the file with this example? I did it as this article said and I have errors. Would it be possible because I am importing tables from other databases?

    Thanks in advanced.

    orfeo_92@hotmail.com

  10. Dirk from Cologne
    Posted July 18, 2011 at 12:40 pm | Permalink

    I had to solve a problem like this.
    I used an extra field in my table and combined all fields I wanted to search through:

    e.g. Field1&Field2&Field3…..

    I only used that field in the search layout.
    Is there any advantage to use the shown method?
    Thank you!
    Dirk

  11. Posted July 19, 2011 at 12:43 pm | Permalink

    @Dirk – One advantage to the method I outline above is that is requires no database definition modification. If you want to change the query in the future, you will have to change the database definition. This is not always a good practice.
    The other advantage is with indexing. If any one of your fields is unindexed or from a related table, the entire calculation will be unindexed and FileMaker will have to evaluate the entire calculation for every record in your database each time you perform a search.

    Also, make sure you have a space or ¶ between each field. Otherwise you will be matching on joined words. For instance, if one field has the value “Jeff” and another has the value “Red”, they will concatenate as “Jeffred”, which may not be what you want to search for. In this case searching “Red” will not match, but searching “*Fred” will.

  12. tskwok
    Posted November 11, 2011 at 11:05 am | Permalink

    I am a newbie to Filemaker.I did try to use it in my database but seem it is not working properly.

    It always return “No Records Found”. I try to turn Set Error Capture [ On ] to off and it will have a warning popup with message “This operation could not be completed because the target is not part of a related table” What seems to be the problem?

    I did create a new table with a new field global search and the script is as follow

    Enter Find Mode [ ]
    Set Field [ Product List::Unique Product Number; Gobal Search::Global Search ]
    New Record/Request Set
    Error Capture [ On ]
    Perform Find [ ]
    Set Error Capture [ Off ]
    If [ Get ( FoundCount ) = 0 ]
    Show Custom Dialog [ Title: "No Records Found"; Message: "No records matched your search criteria."; Buttons: “OK” ]
    End If

    Please help, thanks in advance

  13. Posted November 11, 2011 at 2:55 pm | Permalink

    @tskwok – It sounds like the layout you are using is showing records from a table other than Product List and one that is not related to Product List in your table occurrence graph. Another possibility is that the Global Search field has not been defined to be stored as a global field. The latter is less likely, as you would probably not be able to enter anything into that field if it were not the case.
    So, first check the layout setup to make sure it is showing records from the correct table. If that doesn’t solve the problem, check the Global Search definition.

  14. tskwok
    Posted November 11, 2011 at 7:44 pm | Permalink

    Problem solved!…I use the layout to show the records from “other table” rather than the product list….. gosh! Thanks you for help.!

    Appreciate it very much!

  15. Posted December 1, 2011 at 12:01 pm | Permalink

    This script works awesome–thank you! The only problem I have encountered is that whenever I open the database and search using this function—it always seems to take about a minute to go through all of the records only on the first search. After that, it works as quick as a normal search. Is there a reason for this??

  16. Posted December 1, 2011 at 1:46 pm | Permalink

    @sws – There are a lot of reasons you could experience a long delay during the first search. Most likely, you are accessing FileMaker over a less than optimal network (perhaps the internet?) and one or more of the fields you are searching is either an un-indexed calculation or being accessed through a complex relationship. The first time the search runs, FileMaker has to download all of the values in that field into cache and do a linear search. Once the data is loaded into FileMaker’s cache, subsequent searches are performed much more quickly.
    One possible solution is to find a way to index the data being searched into the current table. You can do this with lookup fields or auto enter calculations, but you will need to have some mechanism in place to make sure the data stays current. The other possible solution is to have your startup script run a search. This will cause the file to take longer to open, but will eliminate that wait during the first search.

  17. Joshua
    Posted January 21, 2012 at 5:31 pm | Permalink

    This is just what I have been looking for! Thanks a lot! It works great for me except for one problem. After every character that is typed, I get a popup box from Filemaker saying, “Before typing, press Tab or click in a field, or choose the New Record menu command.” So, I have to click back in the search box after I type each character. Any suggestions? Thanks in advance!

  18. Eric
    Posted May 16, 2012 at 10:46 am | Permalink

    I’m pretty new to filemaker, I have the script set up and it should work but I don’t know how to complete set 3. Thanks in advance!

  19. Shane
    Posted December 31, 2012 at 1:44 am | Permalink

    Hi, new to this, I get the operator expected here when I make the calculation with two fields, separated by ; as per the script, what am I missing….?

  20. Posted January 2, 2013 at 2:06 pm | Permalink

    @Shane – I’m not sure if I understand your question, but I’ll take a guess at the problem. The semicolon is not a part of the calculation. It is separating the parameters of the Set Field step. To the left of the semicolon is the field being set; to the right of the semicolon is the calculated value to which the field is being set. Does that clear things up?

  21. Shane
    Posted January 2, 2013 at 8:10 pm | Permalink

    Thanks Danny, you did indeed understand my question, and the answer solved part of the problem. The issue I have is that I am only just now learning about calculations etc, and am not sure what I need to ‘add’ to the calculation to get it looking like the one in the script. How do I set multiple fields in the Set Field line….? I guess that is the real question. Thanks.

  22. Posted January 4, 2013 at 10:36 am | Permalink

    @Shane – Each Set Field step is setting the value of a single field to the value of the global search field. The only thing the calculation would contain is Globals::GlobalSearch (or the name of your global search field). To set multiple fields, you would have multiple Set Field script steps as shown in the example.

Post a Comment

Your email is never shared. Required fields are marked *

*
*