FileMaker Inspirations

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:

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:

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