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.

Copy/paste custom functions:

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

Manage Database - Create Fields/Relationship:

Source table:


Target table:


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::_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

Script Workspace

1. Copy/paste script:

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


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


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


Edit Layout

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.

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!

Reporting in FileMaker: How to Make Subsummary Parts Work Hard!

Are you creating a FileMaker Pro subsummary report? A subsummary layout part is used to sort a set of records into groups based on the values contained in a break field. Typically, you can only create one subsummary part per field on a layout. But what if you need both a leading and trailing subsummary part for the same field on a single report? Well, you can do it, given a little creativity!

Use case

Let me explain the situation first. I was working on designing a report for a client for their FileMaker solution, formatted as a proposal. It had a very simple structure. There was a Proposal table to hold data such as the proposal name, customer name, and date, and a Proposal Line Items table related by ProposalID to hold the individual items added to the proposal.

To produce the report, I had created a list view layout showing records from the context of the Proposal Line Items table, with merge fields placed in a Trailing Grand Summary part displayed at the bottom of the list to display the summary total and monthly amounts. So far, so good. Nothing too complicated here. In Preview mode, It looked something like this:

In Layout mode, it looked like this:

Adding some complexity

After I built this simple report, the client told me that they’d like to be able to add some “optional” items to the proposal as well. The catch was that they didn’t want the optional items to be included in the overall total. Instead, they wanted the regular items to show with a total below them, and then the optional items below that. They also wanted a checkbox next to each optional item, so that the proposal recipient could print out the proposal, manually mark which optional items they wanted, sign the proposal, and scan it back.

Here’s how they wanted it to look:

Ok…so now it‘s getting interesting. At this point, I didn’t know how I was going to do that yet. I knew that creating a total in the middle of the report was going to be challenging, because of the way FileMaker handles subsummary parts. But since I’m writing this blog post, you can imagine that I eventually found a way!

Simple subsummary part display

When faced with a difficult problem, I always remind myself, “Solve the simple case first!” So to start, I tackled the regular/optional issue, which was straightforward. I knew I’d be able to use a simple subsummary report to split the regular and optional items from each other.

I decided to add a simple checkbox to the entry portal for the line items, so the user could indicate that the item was optional. It was formatted as a number field, where Optional items would have a value of 1 (checkbox on), and regular items would be empty (checkbox off). Note: this is the FileMaker version of a boolean field, where 0 is off/false, and 1 is on/true. The only difference is that in FileMaker, we typically use the values “empty” and “1”. This avoids the extra step of having to fill in a value of 0 in a field when we could just leave it empty and still test for a 1 in cases that are on/true.

Going back to my layout, I modified it and added a subsummary part, then chose the “Optional” field as the break field.

Then I adjusted the Preview proposal script, to automatically sort the records by the “Optional” field, so that the subsummary part would show when viewed in Preview mode.

This successfully split the regular and optional items into two groups. FileMaker subsummary parts sort, group, and display records that have no value first. This is followed by records that do have a value, in numeric order (assuming the break field is formatted as a number field).

In this case, we have only two groups: regular items, which have no value in the Optional field; and Optional items, where the Optional field is set to 1. (If we did have records with 0 value, they would be separated, and come after the group of blank records. So a value of “0” in a field is not the same as an “empty” value for sorting purposes.)

This is how it looked in Preview after adding the subsummary part:

The two groups were now separated. However, it was obvious that simply adding a subsummary part wasn’t enough. I still needed a heading above the optional items so the user can tell the difference between the two groups. But I only wanted the heading to show above the optional items, and not the regular ones.

Conditional headings using a hide condition

So now I have somewhat veered off the original goal of creating a total in the middle of the report. That part is coming…but first I want to show you how I solved the issue of the heading, because it showcases some other interesting FileMaker features.

To sum up the goal here: I wanted to have a heading called “Optional Items” that shows above the optional items, but not the regular ones.

I chose to use the feature “Hide object when”, found in the Data/Behavior section of the Inspector palette, to control the visibility of objects. Since the context of the layout is Proposal Line Items, I could test whether the Optional field was “on” or “off” (empty or 1). I used the test not PROPOSAL_ITEM::Optional.

In other words, if the item is not marked as optional (i.e. is a regular item), hide the “Optional Items” heading object.

Here’s how that looked in Preview mode:

Great! Now we have a heading, which only appears above the optional items. But what about that empty space above the regular items? I wanted to get rid of it if possible, since it looks weird.

“Resize enclosing part” to the rescue

With the heading object selected, I chose “Sliding up based on:” and “All objects above” in the Sliding & Visibility area of the Inspector palette. I also checked the option “Also resize enclosing part”.

This feature allows you to dynamically adjust the size of a layout part to accommodate different lengths of text in a field when printing from Preview mode. On the layout, set the field to the maximum height you think will accommodate the field’s longest contents. Then use the “Also resize enclosing part” option to shrink the part down when previewing. This avoids large amounts of wasted space on printed reports, in cases where the user didn’t actually enter a lot of text.

Here we only have a small amount of text, but we’re going to use the “resizing part” feature to get rid of the extra space caused by the subsummary part. The hide condition will cause the heading to not appear at all when the layout is viewed in Preview mode. The subsummary part (which contains the heading object) will then be resized to the remaining available space, which in our case is only 1 point.

This works well—as long as the heading object’s size almost completely fills the available space inside the subsummary part. As mentioned, remaining empty space above or below the object will still be displayed.

Troubleshooting layout parts

To demonstrate what I mean, I’ll show you a small trick I use when I’m trying to troubleshoot layout parts. It can be hard to tell one layout part from another in Preview mode. So sometimes I select the part and fill it with a colour to make it obvious which part is being displayed.

This makes it really easy to see your subsummary part in Preview mode.

As you can see above, there is a bit of yellow showing just above the regular items. That’s because I added some extra space below the heading object, just to demonstrate what happens.
Below, I’ve fixed it, and made the subsummary part just one point larger than the “Optional Items” heading object. It makes the part almost completely disappear above the regular items.

I played with making the parts exactly the same size as the objects, and got unpredictable results. I think there can be display issues if the boundaries of an object have the exact same coordinates as its enclosing part (i.e. the object exactly overlaps the top and/or bottom edges of the part). For my purposes, I can live with a 1 pt space above the regular items. This is how it looks without the highlighting:

More conditional hiding

The last issue I wanted to resolve before diving into the totals, is how to show a checkbox next to the optional items. Once again, I used the “Hide object when” condition. The checkbox doesn’t need to be functional, since the end user will be printing this out on paper.

I created a copy of the existing Description merge field, and added a button beside it, with an icon of a checkbox attached. After grouping these two objects, I set the hide condition to: not PROPOSAL_ITEM::Optional, the same as the “Optional Items” header. That way, the checkbox will hide on the records displaying regular items (and show on the optional ones).

I put the opposite hide condition, Hide object when: PROPOSAL_ITEM::Optional on the original Description merge field (the one we want to show on regular items). This way, either one or the other Description label will show on the corresponding record. Here’s how it looks with both fields visible in layout mode:

And here’s how the report looks in Preview, after all these changes:

So, we’ve got a report with a subsummary part sorting on the Optional field. We’ve got our optional items showing below our regular items. We’ve got an “Optional Items” heading to differentiate them. We got rid of the blank space above the regular items. And we are showing a checkbox next to the optional items. Yay!

By this point, you’re probably wondering when we’re going to get back to the totals. All righty then! Let’s do that now!

Showing the totals

The last remaining problem is to figure out how to show the totals below the regular items, then followed by the optional items. (The total field is formatted as a Summary field summarizing the Subtotal calculation from each record. The Monthly Amount field is a calculation with the formula GetSummary ( zcn_Total ; Optional ) / 12.)

At first, I thought of simply using the subsummary part I had already created, and just expanding it. So I added the trailing total line above the Optional header. I also changed the totals to reference the subsummary fields from the local table, like this:

Here’s what it looked like in Preview mode:

While the labels are in the right place, the totals are not correct. Since the subsummary part is technically a leading subsummary for the Optional items below it, it shows the totals for the Optional group of records, which is not what we want.

I thought about getting the totals from the parent Proposal instead, but that figure includes the Optional items, which we actually want to exclude. I suppose theoretically you might be able to reference a calculation on the Proposal that excludes the optional items through a relationship or something like that, but I didn’t end up trying it.

Two subsummaries for the same field

As I was considering this problem, it occurred to me that what I really wanted was to have two different subsummary parts for the same break field: a leading subsummary part for the heading, and a trailing subsummary part for the totals for the same group of records. Except that FileMaker only lets you create one subsummary part per field on a given layout…hmmm…

This got me thinking about how I could possibly get around this limitation. I suddenly thought, I wonder if you could create a calculated version of the Optional field, and create a second subsummary part, but with the calculated field as the break field? This would effectively give you two subsummary layout parts for the same field.

It turns out that you can! (Probably I’m not the first person to think of this…but it was new to me when I made the connection.)

So I did just that. I created an unstored calculation field that simply referenced the Optional field:

This way, the value of the Optional text field and the Optional calculation field will always be the same.

Then, I created a subsummary part below the body part, with the new calculation field as the break field:

…and placed the subsummary fields into it. Now, it looked like this in layout mode:

FileMaker Pro subsummary report layout mode

The last step was to add the new field to the sort order, so that the second subsummary part would show up when the layout was previewed:

And finally…voilà! We have our finished report, with everything in the right place!


In the end, I was able to provide what the client requested: a nicely formatted report of regular items, followed by a total for just those items, followed by the “Optional Items” heading, and finally the optional items themselves with a checkbox beside them.

Sometimes it takes a bit of experimentation to get the results we want. A lot of the time, the solution is not at all clear at the beginning, and we don’t even know if we’ll find a way to do it. I always have to remind myself to work through the problems one at a time. And not to worry too much about whether it will work or not yet, but just keep trying different things along the way. Our creativity kicks in when we’re able to connect two or more pieces of seemingly unrelated knowledge together. But it can involve a lot of trial and error, so if you’re working on something, don’t give up!

This solution is also an example of how combining different FileMaker features together helps us get our desired final result. In this case, we used subsummary fields and parts, together with “Hide object when” and the “Also resize layout part” feature to achieve our goals.
Hopefully this will help someone in future who’s struggling with their subsummary reports in FileMaker!

Creating SVG Icons For FileMaker

Creating SVG icons for FileMaker

Have you ever needed to create a FileMaker SVG icon from scratch because you can’t find the specific one you need? This post will tell you what you need to know to get you out of your pickle. <<Insert pickle icon here>> 🙂

It can be hard to find an icon set that has every symbol you need. I use the ones that come pre-loaded with FileMaker, but sometimes I need one that isn’t included in the default set. Since I want all my icons to blend in with each other, I’m left scouring the web to find something that fits in well with my chosen icon style. (There’s nothing that says “sloppy design” quicker than mis-matched icons…but maybe that’s just me.)

Finding icons

There are a few resources you can use to find icons. First, there’s always a Google search for the name of the symbol, plus the word “icon.” Or you can try searching an icon website such as Icon Finder, Icon Scout, or The Noun Project for your specific symbol. (These search results will also let you know if you’re onto the correct symbol for what you’re trying to represent.)

Be sure to check the licensing terms before you use any icon from the Web in your own work. Some icons are free or open-source (such as those from Material Design Icons), but others are for sale and/or have attribution requirements. 

It might be easiest for you to simply pay to get the icons you want. Given the skill, time, and effort that goes into creating an icon—and especially a comprehensive set of icons in a style you like—it can certainly save you oodles of time (read: money). You can try Streamline icons which are well worth the modest price. When you buy a set, you can be totally confident that they’ll blend seamlessly in your app.

Even so, you still might not find an icon in the exact style you want. Or you might not want to pay for a whole set just to get the one icon you need. Something I’ve been doing lately is drawing my own in SVG format. And, for some simple but perhaps hard-to-find icons, you can too. 

SVG what?

SVG stands for Scalable Vector Graphics format. FileMaker introduced support for SVG-formatted icons in FileMaker 14. It’s different from other graphics formats because instead of an image file, an SVG file consists of XML text that tells FileMaker how to draw the icon at any size or screen resolution. It means you can resize an icon in this format without losing any crispness on screen. And because it’s XML, SVG files are also considerably smaller than other graphics formats.

SVG format also gives you the added ability to set the colour of an icon based on the results of a calculation, using Conditional Formatting. This is incredibly useful if you’d like to show a different icon colour for an “active” vs. “disabled” button, for instance, based on certain data or conditions. To make this work, you need to modify the text of the SVG file slightly, and add the class “fm_fill” wherever you see the fill colour specified. (Instructions below.)

Creating your own icon

So, back to creating your own icon. I’m far from an SVG expert, but I have learned enough about it to successfully create icons for use in FileMaker, and I’m sure you can, too. I’m only going to focus on the practical steps for this post, and skip all the theory.

I’m using Sketch 49.3 (Mac only) for this demo. (Windows users, you may be able to adapt these instructions to Inkscape.) The end goal is to create an SVG file you can import as an icon in FileMaker. 

As an example, I’m going to show you how to draw a simple flag icon in Sketch, and then convert it for use in FileMaker. It consists of two shapes, the “flagpole” and the “flag.”

A note about Sketch: You can begin drawing directly on the canvas without creating an Artboard, but it’s easier to judge the scale of what you’re drawing by creating an Artboard first. Especially if you end up drawing a series of icons, it’s easier to compare them if they are on their own individual Artboards.

1. Setting up the Artboard:

  1. Create a new document in Sketch and select Insert > Artboard.


2. Click “Create Custom Size,” found at the bottom of the Inspector panel on the right.


3. Name the Custom Preset and enter 16px x 16px for the size. Click Save.


4. In the Inspector panel, make sure “Custom” from the dropdown selector of Artboard sizes is selected.


5. Select your custom preset as the size for the Artboard.


Sketch creates the Artboard where you can now draw your icon.


2. Drawing the icon:

  1. Zoom in on the Artboard so you can see it better.


2. Insert a rounded rectangle Shape to create the “flagpole”.


3. Make the size 1px x 14px.


4. Remove the border attribute and make sure there is a fill attribute. Move the shape closer to the middle of the Artboard.


5. Insert a Vector.


6. Create the first point of your triangle “flag” by clicking on the empty Artboard to the right of the first shape. While holding down the shift key, click again at about the horizontal midpoint of the first shape. Click once more, up and to the right, to form the apex of the triangle, and then click your starting point again to close the shape.


7. With the triangle selected, remove any border attributes and make sure there is a fill attribute.


8. Using the Inspector, move the flagpole .5px closer to the flag. Adjust the size of the triangle to make it look more like a flag, if necessary.


You now have an icon ready to be saved as an SVG file. You can save the Sketch file at this point if you like, and continue to add Artboards and icons to it as many times as you like.

Making a FileMaker-compatible SVG icon

The process to make an SVG icon fully compatible with FileMaker features is the same whether you draw the icon in Sketch yourself, or purchase an icon set that you open in Sketch. (I was able to open an EPS file containing a set of icons in Sketch, and then create SVG icons for use in FileMaker using this method. Note that Sketch can’t open AI files except as a single flattened layer.)

3. Creating the SVG file:

  1. Select both shapes and right-click or Control-click to select “Copy SVG Code”.


2. Create a new Text Edit document and paste the copied text into it.


3. Locate the “fill” tag containing the hex code for the fill colour.


4. Next to this, insert a space, and then paste the following text: class=”fm_fill” (If you don’t type a space, your document won’t save properly.)


Save your file and make sure to use the “.svg” extension at the end of the filename. TextEdit may warn you about this, but go ahead and save it anyway. (Note that if you want to open this file again later to edit it, you’ll have to either go to TextEdit and use File > Open, or right-click the file in the Finder, select Open With…Other and then locate the TextEdit app. If you simply double-click on the file in the Finder, it will open in Sketch.)

A note about adding the class=“fm_fill”. If you don’t add this, you’ll still be able to save the icon as SVG format, and import it into FileMaker. However, you won’t be able to change the icon fill colour—it will retain whatever fill colour it originally had in your Sketch file. FileMaker likewise won’t be able to change the icon fill colour using Conditional Formatting.

4. Importing the icon into FileMaker:

  1. In FileMaker, draw a button and double-click to bring up the Button Setup panel. Select the icon setup you want and click to add a new icon.


2. Select your SVG icon file from your computer, and the icon will automatically update. Change the icon size if you wish.


3. You can change the icon colour using the Inspector panel.


4. If you wish, you can enter a calculation in the conditional formatting dialog to change the colour of the icon under certain circumstances.


That’s it. Phew! It might seem like a lot, but once you get the hang of it, it goes pretty quickly. This is everything I wish I’d known when starting out with creating SVG icons. Perhaps now you’ll be inspired to create your own!

Bonus: You can take a Material SVG icon, open it in Sketch (double-click on Mac), and make the same alterations as above to make the fill responsive in FileMaker. Just make sure you copy only the icon shapes, and there are no other invisible artifacts present when you copy the icon, such as a masking rectangle. You can see these in the layers palette. Select the mask and delete it if present.


SVG Wikipedia page: https://en.wikipedia.org/wiki/Scalable_Vector_Graphics

W3 Schools SVG Tutorial: https://www.w3schools.com/graphics/svg_intro.asp

FileMaker SVG Grammar for Buttons: https://fmhelp.filemaker.com/docs/edition/en/svg/

About Sketch Artboards: https://www.sketchapp.com/docs/grouping/artboards/

SVG 2 Language Reference: https://svgwg.org/svg2-draft/Overview.html

Grouping the “g” element: https://svgwg.org/svg2-draft/struct.html#TermContainerElement

5 Things

5 Hidden FileMaker Design Tools I’m Thankful For

Over time, one collects bits and pieces of knowledge about different corners of development. Features that are a little more difficult to discover, or shortcuts that can help out in a pinch. Some of these nuggets can make developing faster, easier, or more accurate. Here are five helpful features you may or may not already know about, but which I’m thankful we have available.

Read More
Converting From Classic Part 1

Converting from the Classic Theme Part 1: The Guts

Many of us who have been working in FileMaker for some time have solutions built on the Classic Theme. “Classic” sounds good, doesn’t it? Who wouldn’t want to build something “Classic?” Well, fellow FileMaker developers, in this case we do want to clear out that old classic, Classic Theme. In this article, find out about a 2-step process to update the meat of your layouts to something leaner, faster, and better-looking than the good old Classic solution you have today.

Read More
Back To Top