FileMaker 19 introduces an incredible leap forward in gathering data in the near-universal data exchange format known as JSON. No longer do we need to write schema just to collect a found set of records into a JSON array of record objects. Now we can use the Execute FileMaker Data API Script step to query any table occurrence in the open and current file, to control that query, and to sidestep annoying limitations and ‘gotchas’ found in other methods. We can now use a method first introduced in the FileMaker Data API (FileMaker Server) and brought to FileMaker Pro and FileMaker Go and FileMaker WebDirect. We are thrilled. We see lots of ways to use this one little new script step. Let’s explore this step and all we can do with it.

Where Did this Step Come From?

As FileMaker becomes more of an Open Platform, we want to send data to services, to JavaScript Widget Add-ons, and to pass data to other parts of the file. We need a better way to construct the data. It makes sense nowadays to construct the data into JSON–in fact most JavaScript libraries and all API services recommend, if not require, the JSON format. Claris listened to its partners and heard the need for something to be added that easily created the JSON without the burden of building extra schema to construct a found set as a JSON array. Partners suggested altering ExecuteSQL or creating a “asJSON” function to meet this need.

An engineer at Claris had the inspiration to make the FileMaker Data API engine available to the calculation engine. It was already in the product; it just needed to be exposed. And we are the better for it.

So now we have a great script step that packages up records as JSON. Let me reiterate: “A step”. As in “One step”.

The Step

The Execute FileMaker Data API script step (a long name, but on point), requires two parameters: a target and a request. The target is the variable or field into which we will place the response, similar to the other amazing step Insert from URL. The request is the JSON object describing the query. With those two simple parameters, we get back a full array of records as JSON objects.

The Request

The request parameter is a simple JSON object describing the query. It is simple because it only needs a few keys. Here’s a working example:

{
   "layouts":"People",
   "limit":500,
   "offset":1,
   "layout.response":"",
   "query":[
      {
         "City":"Denver"
      }
   ],
   "sort":[
      {
         "fieldName":"LastName",
         "sortOrder":"ascend"
      }
   ]
}

This request, hard-coded into an Insert Text script step defines only a few things: the layout upon which the query will be performed, the limit of the number of records to be returned, the query, the offset, and the sort That’s it. To find some records that are displayed on the People layout, we write a query like the one above. Let’s look at each key here:

layouts

The layouts key tells the step upon which layout to perform the query, basically, to do the find. This key is required because this script step really does go to this layout; it opens a new (hidden) window at this layout and sets itself to do the find. Without this key, the step doesn’t know where to perform the query and returns an error in the response.

NOTE: The key is plural, but you only use one layout for the value. FYI!

The layout itself can be any layout: a user-facing layout or, better yet, a layout specifically designed for this query, such as a dev layout (don’t we all have layouts with the prefix “dev_” in the name?). The layout must have all the fields that contain data you want to return in the response.

If you have three fields: City, State, Name, on the layout, those will be included in the response. Though I’ve never worked with it, this is true when using the actual FileMaker Data API. They actually don’t have to be visible on the layout–they can be off-screen–but they must be there.

Curiously the field in which you perform the query–”City” in my case– doesn’t need to be there.

Note: This is whole query, including the “layouts” key is a source of indirection. Use FMPerception to highlight all the places you used this step before changing layout names!

layout.response

This key, rather unfortunately named because of the dot, is a bit of a complexity. Its purpose is to tell the query from what context (that is, what other layout) to return the records. It’s a puzzling one and requires some experimentation.

limit

The limit isn’t required, per se, but if you do not include it, then the step will return the first 100 records. It’s best to set this limit to something higher than what you’ll actually need (more on this later).

offset

The offset simply tells the Data API script step to start at that record. If the the offset is 1, then it’ll return all the records in the set starting at 1.

query

This key defines the records you want to get from the step. And this key can be constructed using any normal FileMaker find string: “=Pre”, “>1/1/2020”, “*”, and “=”, for example.

The query value is an array, and the array can have one or many elements. Each element defines a new find request, and each find request can have multiple criteria. Here’s a few examples:

{
   "layouts":"People",
   "limit":500,
   "offset":1,
   "query":[
      {
         "City":"Denver",
         "Gender":"M"
      }
   ]
}

In this query, I’m searching for all records that include City of Denver and M as the Gender. This is an “and” query–both criteria must be met for the record to be found–it is just like me typing these two values into their respective fields in one find request.

{
   "layouts":"People",
   "limit":500,
   "offset":1,
   "query":[
      {
         "City":"Denver"
      },
      {
         "Position":"Boss"
      }
   ]
}

In this query, I’ve got an ‘or’ find. If I were doing this request in Find Mode on a layout, I’d enter “Denver” in the City field, create a new request and enter “Boss” into the Position field. This returns all records containing either “Denver” or “Boss”.

sort

The sort key is another array of objects. Each object defines by which fields the data should be sorted. You can have multiple objects inside this array to sort by multiple columns.

NOTE: Everything I’m showing here is hard-coded. But you’re a FileMaker developer; you work with JSON every day. You can figure out a way to make this dynamic: global fields (fields with global storage), variables, custom functions. The possibilities are many.

Unused Keys

If you’re familiar with the FileMaker Data API, the one from FileMaker Server, then you might see there are some missing keys. If you’re not familiar with the Data API, then there are missing keys. That’s okay. Read this section still to understand what’s missing; what’s missing gives you further information about this script step’s scope and purpose.

action: Currently the Perform FileMaker Data API only supports ‘read’. That is, you can only use this step to read data. The other actions–create, delete, update, duplicate– are not supported. There is some hope these will be in the future. There is one action, “metadata”, that we can use. We’ll get into this detail in a future blog post.

version: This script step uses a built in Data API engine; we don’t need to specify which one to use.

databases: This step only works on the file in which the script is located. So if you want to get data from another file, you would call this step inside a script in that other file.

Authorization: The authorization is the current-logged in user.

Content-Type: the response is JSON. That’s the point of this step!

The Response

Once the request has been created, the step returns the response. As I noted above, this step actually does open a window and performs the query. It does this without issue across the platform, so you don’t need to worry about compatibility. It’s fast; no one can stop this step midway to view the layout, and the window will close upon completion.

The response you get back is a JSON object with lots of useful information. You get back:

{
     "response" :{
            "dataInfo":{...}, 
            "data":[...]}, 
     "messages":[...]
}

From there you can pull what you need. Let’s look at each key in the response.

response.dataInfo

Here you get a lot of helpful information pertaining to the returned data result. In my query above, I get this:

{         
     "database":"DT_People",
     "layout":"People",
     "table":"People",
     "totalRecordCount":1005,
     "foundCount":22,
     "returnedCount":22
}

Notice the “foundCount” and “returnedCount” are two separate keys. That’s because of the offset. If my “offset” value is 2, then I’d only return the last 21 records because the return starts at record 2.

response.data

This includes the entire found set of records. Each element in the array is the data from the fields on the visible part of the target layout in JSON form.

[
   {
      "fieldData":{
         "Address":"3233 Blake Street",
         "Email":"",
         "FirstName":"Fred",
         "LastName":"Flintstone",
         "PrimaryKey":"2519874603326055443343358368631954311881461403190964389494"
      },
      "modId":"1",
      "portalData":{

      },
      "recordId":"2"
   }
]

The response.data key is an array of objects. Each object contains “fieldData” and other keys.

• fieldsData: The data from the fields on the target layout
• modId: the modification count
• portalData: any related records of the record. We’ll review this in a later post.
• recordId: the internal FileMaker ID of the record.

messages

Every API response includes some type of key that explains the status of the request. The Execute FileMaker Data API script does as well. Since we’re working with an API response, we gotta play by its rules and consider the message. Here’s the message received when I queried for some records in my table:

{
   "code":"0",
   "message":"OK"
}

Whew. My request was valid. The response sent back a number of records–be that zero, one, or more than one.

Since the query itself is a source of Indirection, the response could come back with an issue. Here’s what it looks like when I changed the name of the layout and forgot to use FMPerception to check to see where I used this step to see if I used this layout:

{
   "code":"105",
   "message":"Layout is missing"
}

Beautiful. The code is not 0, so there’s something wrong. The FileMaker Data API helpfully tells me what the problem.

If a query field is missing from the table, here’s the message:

{
   "code":"102",
   "message":"Field is missing"
}

Short. To the point.

Curiously, it seems the message only returns one code and message though there may be multiple errors in the query. I suppose if you have to look at the query, you can review all of it at one time to catch additional errors.

As of this moment, I don’t see a list of the errors returned. Luckily, as we said before, the message is simple.

To further repeat myself, working with an API–its request and response–we need to use all the right tools and considerations. We need to consider the message we get back from this step before we do anything else in the script, and we need to handle what to do when the response returns a messages.code of anything other than 0. This consideration is part of the world we live in, so we need to adopt this consideration into our practice.

Uses

There’s a ton of possible uses for this new step, and we at Geist Interactive plan on using it now to gather records in JSON format for JavaScript widgets or API payloads. We probably won’t rewrite existing scripts using different methods at this moment, but we will begin to use it for clients and products that support FileMaker 19.

As I was getting familiar with the step and as I was working on JavaScript widgets, I considered how the data can be placed into widgets that I know well. Here’s an example of the result being placed into DataTables:

This was easy. The library can accept multiple forms of data, including JSON. So my set up was simple:

Notice the highlighted part. I was able to use “fieldData.FirstName” as the key for Data Tables to pull from each element of the array (the field on the right).

That’s good stuff.

Onward

We will be back in future blog posts talking more about this step. We’ll talk about its uses and considerations and even the cool feature to get metadata from the FileMaker file.

Give the Execute FileMaker Data API script step try. It’s a simple step that returns a lot of information and information neatly structured into JSON.