I mentioned at the end of my last post Execute FM Data API Performance Test – Part 2  that I would demonstrate a use case where I feel that the Claris Execute FileMaker Data API excels is universal multi-selection picker scripts. 

Some housekeeping.

  1. You may feel like this is a guiltless plug, a broken record, or callback marketing but in the last two posts I mentioned I wrote a series on how to use the Create, Update, and Delete functions of the Execute FileMaker Data API script step. I’ve provided the links below.
  1. In this post, I assume you know how to create and parse JSON. While I am not going into detail, there is sample code in the scripts and it’s meant to be reusable.
  2. You’ll notice this is the same file as my last 2 posts but with the addition of tables, layouts and scripts. I have also added a sample record which is just from the SendGrid templates but should you click either button ( Normal or FM Data API ) which gets templates then the only record will disappear. The html is not needed to for the demo so if it gets deleted it’s ok.

Why picker scripts?

In many of the files we work with, we use pickers to select multiple items that create join records—whether it’s for tags, adding products to a quote, or selecting users and resources for a project. In these cases, we rely on scripts for opening, selecting, and filtering. While this approach works, it leads to duplicating the same script logic repeatedly, which often proves inefficient.

Instead of reinventing the wheel, we can use the same script and switch out the parameters. You can use this method without the Execute FileMaker Data API script step, but let’s explore different ways to create and update records.

Options for Record Creation/Update

  • Creating Records on the Server: If the file is hosted, we can create records directly on the server and store the key in a global variable. This global is used in conditional formatting to indicate whether the item is selected, which works well in card windows. One drawback is that the portal behind the card might not refresh immediately, making it seem like the item wasn’t clicked.
  • Navigating Away to Update Records: Another option is to navigate away to create or delete records and then return to allow for another selection.
  • Using Globals to Store Keys: For a previous version of tags, I stored all keys in a global. When the window closed, the existing records would delete and recreate with the new keys. This approach posed a performance issue, but it was a good learning experience.
  • Using Another Document Window: If you’re working with another document window, you can gather the data, select the target window to create records in a portal, and then return to the picker window when done for another selection.

These are just a few examples, but there are plenty of other ways.

Getting Started

A brief background: We use SendGrid to send emails, merging data fields in HTML and sending them as transactional emails. Most of these are automated emails, but we also have the ability to open a universal template picker, which displays only templates relevant to the current layout. While I’d love to demo how this all works in detail, that’ll be a topic for a future post. For now, let’s focus on the picker that selects layouts. The fields picker works in the same way, so this should give you a solid understanding.

While updating the code for Templates in Jarvis, I realized that I repeated the same process—only the fields, layouts, and sort order differed. To improve this, I decided to write more universal scripts. I’ve mentioned that the Execute FileMaker Data API  script step is fast for one-off records, so I rewrote the process to incorporate it.

Let’s take a look at the Layouts portal on the right side of the layout:

Click the Add button for Layouts, and a card window will appear. Clicking the gray checkmark icon selects the layout, while clicking the green icon removes it. Pay attention to the Layouts portal in the background, as changes occur instantly. You can click Selected in the header to filter the list to show only the selected layouts.

How Does It Work?

I’ve added two tables for Layouts and two more for Data Fields. The code is the same across both, but I’ll explain it using the Layout tables. The default layout list is stored in the DFL table, and the join table for SendGrid layouts is STL. Both tables have a primary key, FileMaker record ID, layout name, and audit fields.

The STL table has two foreign key fields: one that links to the template and the other that links to the default layout key from DFL. Another field calculates a simple JSON object, it uses the __f_DFL foreign key as the JSON key and _fm_recordId for the value. You can see this calculation in the Data Viewer.

Below is an image showing all the scripts used to make this work.

Script Breakdown

Universal Picker – Setup { json }

The sample JSON is defined on line 12. We use a Let statement to set up the JSON for the portal list. This is done by grabbing the list and then performing a substitute to create a larger object. The before and after transformations can be seen in the data viewer.

The rest of the script involves storing globals for the ID, data (from the portal), and the table where the data will be stored. It also fetches the layout for the picker. From there, the script calls the Universal Picker – Show All script.

Universal Picker – Show All

There’s not much to this script since the records sort based on the table name and it also clears a global used for button highlight. This script is also applied to the Show All button in the picker layouts.

Universal Picker – Multi Select { json }

This script handles selecting and deselecting items. A sample JSON shows the key being set, which checks if the value exists. The fieldData is set for the Execute FileMaker Data API script step, which in this case uses both foreign keys. Data is parsed out, and on line 17, it attempts to retrieve the fmRecordId using the key. If no key exists, it creates the record starting on line 37. If the fmRecordId does exist, it deletes the record starting on line 29.

The script updates the $$data global variable, either deleting the element (line 34) or adding it (line 43). This allows the process to repeat by validating whether the item is selected. The Position function is used for conditional formatting on the checkmark icon using the $$data global variable. This variable is also used in the Universal Picker - Filter Selected { field } script.

Universal Picker – Filter Selected { field }

This script runs from the Selected button in the picker list. Pass in a fully qualified field name, wrapped in quotes ( e.g., “DFL::___DFL” ). The script uses JSONListKeys to gather all keys (the primary DFL keys) and checks their count to ensure they exist. If records exist, it loops through the keys to perform a find, and then it highlights the Selected button.

Universal Picker – Close Window

Finally, this script simply closes the window and clears global variables.

Challenge Yourself

If you want to challenge yourself further, try adding a couple more tables: one for defaults and another for joins, specifically for Filters. Filters help narrow down the found set when searching for templates, with default examples like System and Marketing.

I hope this helped you learn something new.

Have a good week!

    If you need help utilizing this amazing and functional use case in FileMaker, let us help by clicking the button below!

    Contact Us