fbpx
skip to Main Content
Type-Ahead Filter Value List With Execute SQL Social Preview

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. 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.

Graphic showing empty type ahead list

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.

Graphic showing matching type ahead 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:

SQLGetFieldName
SQLGetTableName
//allows Execute SQL statements to use FileMaker TABLE::field notation

—2—
Manage Database - Create Fields/Relationship:

Source table:

SOURCE

Target table:

TARGET

Target field:

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__TARGET__FilterTargetField_Results_srt

Match

SOURCE::_kgt_FilterTargetField_Results = TARGET::__kpt_ID

In Edit Relationship dialog, sort records on

source__TARGET__FilterTargetField_Results_srt::TargetField

to show portal row results in order.

4. Create relationship:

source__TARGET__All

Match

SOURCE::__kpt_ID |x| TARGET::__kpt_ID //use a Cartesian join on both tables' primary keys

—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.

Now Open for Enrollment!

Workflow Design Workshop Office Mockup

Finally, a plan for designing modern Claris FileMaker Pro solutions that are useful, effective, and look good too!

Sign up to the FREE class on December 29, 2021!

Alexis Allen

I'm a certified FileMaker developer based in Toronto with over 20 years of experience. I've designed solutions for desktop publishing, signmaking, packaging design, marketing and advertising. I have presented several times at the FileMaker Developer's Conference in the US on the topic of design. As a lifelong student, I enjoy all creative pursuits.

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top