Google-Like Searches In FileMaker – Non-Contiguous

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

In my article, Google-Like Searches In FileMaker, I described a method for creating a search-engine-like interface for searching the contents of a FileMaker Pro database. This article expands on that method by eliminating one of it’s shortcomings: it would only find records containing all search terms in a single field. For instance, if you enter “ABC Plumbing Chicago” in the search field, it will only find records that contain all the words “ABC Plumbing Chicago” in a single field.

If, for instance, you want 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. This article describes a method to accomplish this.

Step One: Create a Value List

Looping through a value list is easier and more straight forward than looping through words in text. So, we’re going to set a variable to a value list containing one search term in each value. The simplest way to accomplish this is to substitute each space with a line break:

Set Variable [ $searchList ; Substitute ( g_SearchField ; ” ” ; ¶ ) ]

Note: Since we will be accessing the search criteria multiple times, placing the criteria in a variable will also improve the performance of the script. Accessing variables in FileMaker scripts is faster than accessing fields.

Step Two: Prepare the Loop

We will create a loop that uses an incrementor. An incrementor is an integer whose value is incremented by one with each pass through the loop. To prepare the loop, we will do the following:

  • create a variable to act as an incrementor,
  • add the Loop and End Loop script steps,
  • add a Set Variable step to increment the incrementor,
  • add an Exit Loop If step to exit the loop when the value of the incrementor is greater than the number of words in the search.

Here’s a sample of a basic loop script using an incrementor:

Set Variable [ $i ; 1 ]
Loop
Exit Loop If [ $i > /* count of whatever we’re looping through */ ]

Do loop operations.

Set Variable [ $i ; $i + 1 ]
End Loop

Step Three: Perform Finds

This part of the script is similar to that we created in Google-Like Searches In FileMaker. The difference is that it will be performed within a loop for each term in the search criteria. During the first pass, we will use the Perform Find step to get all records that contain the first term in the search. For each subsequent pass, we will use Constrain Found Set to narrow the search results to those records which contain all search terms.

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

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::Address Type 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 ]
If [ Get ( FoundCount ) = 0 ]
Show Custom Dialog [ Title: “No Records Found”; Message: “No records matched your search criteria.”; Buttons: “OK” ]
Exit Loop If [ 1 ]
End If
Set Variable [ $i ; $i + 1 ]
End Loop

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

19 Comments

  1. Jens
    Posted November 17, 2008 at 9:50 am | Permalink

    Doesn’t $searchList metamorphose into $searchTermList?

  2. Posted November 17, 2008 at 11:58 am | Permalink

    Thanks Jens. I fixed the typo.

  3. Posted March 23, 2009 at 4:15 pm | Permalink

    Thank you so much for this tip. It is exactly what I have been looking for!

  4. Posted May 28, 2009 at 7:00 pm | Permalink

    This is great. Thanks.

    Or it would be great but it actually isn’t working correctly. My first find returns no matches. My second find returns the records that match my FIRST find; my third search returns records that match my SECOND search, etc. I can’t find any mistakes in the script but obviously something is very wrong.

    Help???

  5. Posted May 29, 2009 at 12:31 am | Permalink

    @Abbe Lougee – Try adding a “Commit Records/Requests [No Dialog]” step to the beginning of the script. That should fix the problem.

    What’s happening is that FileMaker is not saving the text entered in the search field until after the script runs. The commit step forces it to save or commit all data entered.

    For some reason, I didn’t run into that problem when I first tested the script, but I’ve seen it happen under some circumstances. So, I’ve added it to my sample above. Thanks for pointing out the problem to me.

  6. Chris
    Posted July 15, 2009 at 2:07 pm | Permalink

    Thanks for the inspiration.
    In your script you use $i to extract the appropriate value from $SearchList. You then make a new request and go to each field to set it. Would it make sense to have a calculation field combining all the fields you might want to search. You could then plonk your entire search string into this field and search once. The script would also be easily portable?

  7. Posted July 20, 2009 at 5:24 pm | Permalink

    @Chris – Yes, that is an effective approach that would result in a simpler, more portable script, and I have done it that way as well. However, I prefer the approach that I outlined here for a couple of reasons.

    First is speed. If even one of the fields you are searching on is from a related table or not able to be indexed for any reason, the entire calculation will be un-indexed. So, FileMaker will have to construct the entire calculation on the fly for each record in the table being searched. This could be resolved by creating a text field that is updated to contain the calculated contents by a maintenance script that runs regularly to update the field. However, the overhead and potential unreliability of such a method might reduce its desirability.

    Second is a matter of personal preference and may seem trivial to some developers. For cleanliness, I prefer to avoid creating too many calculated fields that serve only interface purposes. It is generally impossible to avoid this practice entirely in FileMaker, but when it gets to a point where there are more fields of this type than fields that hold real data, I find that maintenance becomes more difficult.

    In short, I think that when speed is not an issue and portability is desirable, creating a single calculated field to search on would be a preferred method to the looping method in this article.

  8. Jason
    Posted July 21, 2009 at 8:33 pm | Permalink

    FANTASTIC bit of code. Being new to all this Filemaker stuff, I am finding this site very useful. Thanks Danny!!!!

  9. Posted April 13, 2010 at 2:32 pm | Permalink

    Congratulations!

    I found this a fantastic approach to make this kind of search to work.

    However, I have Filemaker 7, and with this syntax I can not use this solution! In Filemaker 7 it doesn’t have the “Set Variable” command.

    Do you have any suggestion to apply this solution on Filemaker 7?

    Any help would be very appreciated.

    Thank you very much!

  10. Posted April 13, 2010 at 3:20 pm | Permalink

    @Bruno, Yes. Simply use a global field instead of a variable. Create two new global text fields and wherever you see the Set Variable command, use the Set Field command instead.

  11. Abhaya
    Posted June 1, 2011 at 8:32 am | Permalink

    Nice one
    its great for newbie.

  12. Eric
    Posted January 22, 2013 at 10:23 am | Permalink

    Is it possible to perform a partial search within the word? (when I type in “ppl”. “apple” comes up?)

    Thank you Danny!

  13. Posted January 22, 2013 at 12:01 pm | Permalink

    @Eric – It is possible by starting the search with an asterisk (*ppl). You could use functions as described above to do this or you could add an asterisk to the beginning of the $thisTerm variable in the script above.

  14. Arthur Caron
    Posted August 5, 2013 at 7:19 am | Permalink

    @Eric (and others)
    I had a hard time figuring out how to do this (Danny’s explanation on this purpose is quite short) so here it is :
    Set Field [ Contacts::First Name; $thisTerm ]
    becomes
    Set Field [ Contacts::First Name; “*” & $thisTerm ]

  15. Posted August 5, 2013 at 11:28 am | Permalink

    @Arthur – Thanks for the clarification! Sometimes I forget that what is obvious to me isn’t necessarily so to everyone else.

  16. Vilaphong
    Posted March 6, 2014 at 3:10 am | Permalink

    Thank you !

  17. Jasu
    Posted September 15, 2014 at 6:40 am | Permalink

    Another excellent script. Very well done. Even better than “Google-like searches”. Makes for a nearly perfectly intuitive search method.

    The only thing that is missing is a way to list entries that partially match the search criteria, like with most search machines. Or have you ever had no results with Google?

    I envision, a user types “ABC plumbing Chicago”. There’s no perfect match. Instead of an error message, most users would expect an even more advanced search function to offer entries that match all but 1 word, for example, “plumbing” and “Chicago” but not “ABC”.

    I’m an almost complete FM newbie. Can anybody think of a solution for this?

  18. Posted January 6, 2016 at 5:58 pm | Permalink

    I have been trying to create something like this since our firm started using FileMaker back in 2006. Thank you so much. It’s a great piece of code.

  19. Stephen
    Posted December 27, 2016 at 8:48 am | Permalink

    I put the script into my database and it is working well but the only problem I have is the tables not resetting after I search something, until I delete the search out of the search bar. But if I click on what was searched and go to the next table, when I go back to the original table the searched item is still the only thing showing up. What could I add to my script so that this would be fixed.

    Thank You,

    Stephen

Post a Comment

Your email is never shared. Required fields are marked *

*
*