Type-Ahead Filter Value List for FileMaker using ExecuteSQL

A feature that’s appreciated by users is the ability to see matching values in a list as they type in a field. It helps reduce the number of items to choose from, and the more they type, the fewer results they see. You can easily implement this kind of type-ahead value list filtering capability in FileMaker!

A major task we have to accomplish as FileMaker developers and designers is to help reduce complexity for the user. A lot of that is about picking the right design pattern for the job at hand. Features that help them quickly and accurately find what they’re looking for are very useful and help them complete their tasks faster.

If you have a relatively long list (more than 20 or so items), it certainly improves the user experience to be able to narrow the list of choices, by typing the search terms in a field above the list. I call this a type-ahead filter, but it’s also known as predictive search or autocomplete.

It's a pretty common and useful feature.

There is a native FileMaker feature to allow auto-complete based on previously entered values, but it may not cover every situation. It only works on text fields, for example.

But if you want to create your own type-ahead functionality, you can:

• use any kind of data as the input
• show more than one field in the resulting list
• display category headers inline
• display sections in a custom order.

You also have more control over the style of the list. The style for the native dropdown lists cannot be changed, but you have pretty fine control if you style a portal to function as a dropdown list.

From the user’s perspective, this technique works exactly like a regular dropdown list. They click on the field to activate the list and are put into the field, where they can type if they choose. If the user doesn’t type anything, then they can still browse the list like they normally would. There’s nothing fancy here from an interaction perspective, and it meets all the user’s expectations for how a dropdown value list should behave.

NOTE: This technique works best when querying indexed fields. It’s possible to make it work on unstored calculations, but there will be a performance penalty when performing the search. If you find that performance is an issue, you could try improving speed by transferring the Execute SQL step to its own script, and have it run via Perform Script on Server instead.

Here are the steps to creating your very own type-ahead function using FileMaker and ExecuteSQL. Obviously, you'll have to replace the "Source" and "Target" references with your own table and field names after copying and pasting into your own file. You can download the demo file here.

—1—
Copy/paste custom functions:

—2—
Manage Database - Create fields/relationship:

Source table:

Target table:

Target field:

1. Create user input/filter field:

2. Create match key field:

3. Create relationship:

Match

In Edit Relationship dialog, sort records on

to show portal row results in order.

4. Create relationship:

Match

—3—
Script Workspace

1. Copy/paste script:

Source-Trigger List
//simple script to fill the key field SOURCE::_kgt_FilterTargetField_Results

with

List (source__TARGET__All::__kpt_ID)

so that the portal is populated with all values. (If you need to improve performance, you can skip this step. In that case, the list will be empty until the user types something.)

2. Copy/paste script:

Source - Perform Filter

Attached to the filter field, this script waits until the user stops typing and then executes the script

Source - Update List

3. Copy/paste script:

Source - Update List

Updates the key field

SOURCE::_kgt_FilterTargetField_Results

with the ExecuteSQL results, which in turn refreshes the portal

source__TARGET__FilterTargetField_Results_srt

when the user types in the Input/Filter field

SOURCE::zgt_FilterTargetField_Input

Performs an ExecuteSQL statement (returns the IDs of records from the TARGET table)

Let ( [
_targetTable = SQLGetTableName ( TARGET::__kptID) ;
_targetTablePK = SQLGetFieldName ( TARGET::__kptID ) ;
_targetField = SQLGetFieldName ( TARGET::TargetField ) ;
_inputText = SOURCE::zgt_FilterTargetField_Input;

_query = "SELECT " & _targetTablePK & ¶ &
"FROM " & _targetTable & ¶ &
"WHERE ( LOWER(" & _targetField & ") LIKE ? )" ];

ExecuteSQL ( _query ; "" ; "" ; "%" & Lower(_inputText) & "%" )
//_query )

that match what the user entered in

SOURCE::zgt_FilterTargetField_Input

—4—
Edit Layout

1. Copy/paste user input/filter field:

SOURCE::zgt_FilterTargetField_Input

Has script trigger:

OnObjectEnter - "Source - Trigger List"

And script trigger:

OnObjectModify - "Source - Perform Filter"

The Install OnTimer Script step has a short delay that prevents the search from being performed after every keystroke, but when the user stops typing instead.

2. Copy/paste portal that uses relationship:

source__TARGET__FilterTargetField_Results_srt

Show the field

source__TARGET__FilterFamilyResults::TargetField

in the portal.

That’s it! I use this technique all the time. I like that it offers a lot of control over the look and behaviour of the list, while still abiding by the rules of normal value lists. It’s a bit more work to implement, but the user experience is so much better for long lists. Please download the demo file and let me know what you think!

Thanks for the input of my colleagues at Direct Impact Solutions in refining this technique! Check out their YouTube channel.