Users appreciate the ability to see matching values in a drop-down value 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. Features that help them quickly and accurately find what they’re looking for are very useful and help them complete their tasks more quickly.
However, 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.
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.
If you 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.
—1—Copy/paste custom functions:
SQLGetFieldNameSQLGetTableName//allows Execute SQL statements to use FileMaker TABLE::field notation
—2—Manage Database – Create Fields/Relationship:
TARGET::TargetField //the field you wish to filter on & display in the list
1. Create user input/filter field:
SOURCE::zgt_FilterTargetField_Input //global text field in source table
2. Create match key field:
SOURCE::_kgt_FilterTargetField_Results //global text field in source table
3. Create relationship:
SOURCE::_kgt_FilterTargetField_Results = TARGET::__kpt_ID
In Edit Relationship dialog, sort records on
to show portal row results in order.
4. Create relationship:
SOURCE::__kpt_ID |x| TARGET::__kpt_ID //use a Cartesian join on both tables’ primary keys
1. Copy/paste script:
Source-Trigger List//simple script to fill the key field SOURCE::_kgt_FilterTargetField_Results
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
with the ExecuteSQL results, which in turn refreshes the portal
when the user types in the Input/Filter field
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
1. Copy/paste user input/filter field:
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:
Show the field
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.