Search as You Type Using ExecuteSQL

FileMaker introduced a very different kind of capability with the ExecuteSQL function. First introduced in FileMaker 12, it allows you to perform “select” queries against your FileMaker data, as opposed to the more familiar “Find” functionality available in FileMaker.

SQL (Structured Query Language) is a standard used with other, more traditional, database servers. Using SQL, SELECTS on indexed data is blazing fast and efficient. It allows you to construct relationships in SQL that do not necessarily exist in your application otherwise.

There have been many useful techniques that have been demonstrated since its introduction. This post will detail and explain yet another technique of the ExecuteSQL function – Search as You Type.

The Search as You Type Technique

In this example, we will attach a script trigger to a global field that will fire on every keystroke. The script will then perform an ExecuteSQL to build a list of IDs that relate to the table we want to show results from. Once we have a list of IDs, we can enter those in a global field, where it will act as a multi-key relationship to show related records in a portal. Sound easy?

The real trick is dynamically building the SQL we want to use. However, as this is all handled by our script, you do not need to know SQL to use this solution. By getting a dynamic list of fields to search on, our search term entered can search against as many fields as we want.

Finally, if we allow for entering multiple search terms, separated by commas, we can build a query that can look across ALL fields for multiple queries and narrow results as you type. An example screenshot is shown below.

Step One: Building the Field List

To start, we need a list of fields to search on. Fortunately, you can interrogate the internal FileMaker tables used to reference schema. For example, the following SQL will return results for all the table occurrences that appear in your relationship graph.

SELECT * FROM FileMaker_Fields

The kind of find we want to perform works best on Text type fields, as we will use the LIKE operator to find results. The equivalent field type in SQL is “varchar”. To return only a list of Text fields in your database, you include the clause “WHERE FieldType = ?” and give it a parameter of “varchar”. We also restrict our search by looking for only fields where the “FieldClass” is equal to “Normal”. That leaves us with a list of text fields that exclude any global fields or summary fields.

This technique as shown in the sample file does not handle fields defined with repetitions. You could add support for that, but generally, you should avoid repeating fields in data.

In our sample code, we abstract out the table name to make it easily portable. To modify in your own solution, update the table to target by updating the variable named $get.tablename.

Step Two: Building the Search Request

Now that we have a list of fields we want to search in, we can build the list of corresponding parameters we need for the ExecuteSQL function. By building the expression used for the ExecuteSQL function, it is a little easier to build the SQL statement using variables and then use the Evaluate function to perform it.

Then, we define a corresponding search parameter for every field being searched on. As a result, we populate two variables with values: $this.fields and $this.params.

Additionally, since queries run in SQL are case sensitive, we will make all search requests lowercase both in SQL, using the LOWER function. We also use the Lower function in FileMaker. By using LOWER in SQL, you also prevent FileMaker from automatically indexing all fields being searched on.

The only field we need returned is the ID field, which is the primary key in our table. Once we have a list of primary keys, we can temporarily store those in a global text field and relate it to our target table to create a many-to-many relations and show results in a standard portal.

Finally, with the ExecuteSQL expression constructed, we can run it with the Evaluate function.

Step Three: Multiple Search Parameters

Since we build the SQL expression dynamically, we can also add support for entering multiple search parameters. In technical terms, we want to perform an “AND” query for every search parameter entered.

A comma is defined as our search delimiter. For example, if we want to find all records that contain “Chicago” and also contain “IL” in any field, we can type “Chicago, IL.” The query returns all fields that are LIKE %chicago% AND all fields that are LIKE %il%.

Get the Search as You Type Using ExecuteSQL Sample File

You can use the following sample file to examine the code and modify for use in your own solution.

The changes required to point the SQL to a new table in your solution are minimal: simply change the variable that is set for “$get.tablename” to your own table occurrence name and update the relationship and portal.

• https://github.com/SoliantMike/FM-ExecuteSQLAsYouType

Special thanks to Mislav Kos for reviewing and suggesting several improvements.

References

• ExecuteSQL documentation

If you have any questions or need help with your FileMaker solution, please contact our team.