What is this new script step in FileMaker 19 and how does it work?

FileMaker 19 introduced some game changing functionality to the platform, including the JavaScript functions and Add-Ons. Another last-minute feature that made it into the release was the Execute FileMaker Data API script step. Documentation is sparse at this time, so we set out to find out what it does and how it works. Let's jump in...

What Does It Do?

This new script step allows a developer to query the database in a couple of ways and, more importantly, have the results returned in JSON format. Much of the data transfer between online services nowadays is formatted as JSON and, historically, there was no easy way to get FileMaker data in JSON format. With this new script step, however, you can perform your query out of context, similar to the ExecuteSQL script step. Note, though, that it technically opens a hidden window to perform the query before closing it again.

How Did It Come To Be?

This new script step actually came about precisely because of the new JavaScript functionality in FileMaker 19. JavaScript libraries rely on JSON heavily. For that new JavaScript functionality to be widely accepted, there needed to be a simple way of populating JavaScript functions with FileMaker JSON formatted data. Turns out the Data API, which is generally considered to be a FileMaker Server function, already returns responses in JSON, so hooking into this was the logical way forward for Claris engineers.

If you're aware of the Data API, then you know that it comes with data limits of 2GB per licensed user per month. That's a decent amount of data, to be honest, but it's worth noting that any data retrieved using the Execute FileMaker Data API script step does not count against your annual allocation.

What Features Are Supported?

The implementation of this script step in FileMaker 19 only supports a couple of features of the web Data API. We've created a sample file that leads you through increasingly complex examples, adding parameters to each query. A screenshot is below and you can download the file here. We imagine there will be calls from the community to support additional areas of the web Data API and we'll see if those get answered in due course.

How Does It Work?

There's not too much to the actual script step itself. As below, you specify where you would like to store the script step's results (either a variable or a field) and then the request you wish to send, which opens up a blank calculation dialog. This is where many may give up, but don't! Keep reading and download the sample file to learn the how the request needs to be formatted.

Note that in our example file, we're populating a field with the request and then using this as our request. If you type your request directly into the calculation dialog, you'll need to escape all the double quotes with a backslash \.

Read

At its most basic, the script step is intended to retrieve data based on parameters the developer feeds it. This is the read functionality. With all its possible parameters, this request looks something like this:

{
"action":"read",
"version":"v1",
"layouts":"Contact Details",
"layout.response":"Contact List",
"limit":1000,
"portal":["email","phone"],
"query":[{"First Name":"==John"},{"Last Name":"==Williams"}],
"sort":[{ "fieldName": "Last Name", "sortOrder": "descend" },{ "fieldName": "First Name", "sortOrder": "ascend" }],
"offset":2
}

Don't be overwhelmed, download the sample file and step through the examples as we explain what each of the parameters does.

Metadata

We also get some bonus functionality, which will be super useful when creating Add-Ons. This is the metaData functionality, which retrieves more schema oriented information about your database rather than the actual data contained within the database. There's two sides to the metaData functionality.

Layouts
We can retrieve either a list of all layouts, or by specifying a layout name, we can retrieve metadata about that particular layout.

{
"action":"metaData",
"version":"v1",
"layouts":"Contact Details",
"recordId":2
}

See the sample file for details.

Tables
Similarly, we can get information about all tables or a particular table. This functionality is not currently available via the web Data API, so truely something new in FileMaker 19.

{
"action":"metaData",
"version":"v1",
"tables":"Contacts"
}

Conclusion

This new script step has little documentation to accompany it right now, so we'll be sure to add to this article if any more functionality comes to light. It will, however, make integrations with other services far simpler with the ability to get our FileMaker data in a format that these other services expect. It will also be incredibly useful in other ways, like retrieving and processing data from other tables, for example. Together with the other features of the FileMaker 19 platform, it truly is a game changing release. If you need some help with this, don't hesitate to get in touch.

And if you need general help with your FileMaker app, or want to save money on your licensing of Claris products, contact us and we'll be glad to assist.

Update 11/16/2021

Be sure to check out our update article about this script step with the release of FileMaker 19.4: Update For FileMaker Pro 19.4: Exploring The Execute FileMaker Data API Script Step.