In a previous post, we wrote about the new Execute FileMaker Data API script step. We wrote about its JSON-constructed data-gathering abilities. We’re pretty excited about this step. It’s useful for gathering a found set of records to display in a JavaScript-based add-on and for passing records to an API. But this new script step offers so much more information about the local file in which it is called. This post will explain those and show how to get the FileMaker metadata about the tables and fields.

The More Obvious Feature

If you recall in the previous post, I explained the query object you pass as a parameter, has a default ‘action’ key of ‘read’. Here’s an example query:

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

By default, this step performs the action of ‘read’. This key/value pair isn’t included since that’s the only thing this step does.

Getting the FileMaker MetaData

However, late in the game, the engineers added another action to this script step: metaData. Just like in the FileMaker Data API call, you can get the a lot of information about the tables and fields in the file. Actually this script step gives you more information than the web API. Here’s the list of supported queries.

Get All Tables

With the following query, you can get all the table occurrences with data sources and base tables in the calling file.

{ 
  "action": "metaData", 
  "tables": "" 
}

With this query, I can look at LedgerLink’s tables. Here’s the response.

{
   "messages":[
      {
         "code":"0",
         "message":"OK"
      }
   ],
   "response":{
      "tables":[
         {
            "baseTable":"Account",
            "name":"Account"
         },
         {
            "baseTable":"Class",
            "name":"Class"
         },
         {
            "baseTable":"Customer",
            "name":"Customer"
         },
         {
            "baseTable":"PaymentLine",
            "name":"Invoice_PaymentLine"
         },
         {
            "baseTable":"Customer",
            "name":"Invoice_Customer~all"
         },
         "..."
      ]
   }
}

The result of this query is an array of all the base tables and the table occurrence names (the name key). Notice the base table “Customer” is returned twice. That’s because there’s two table occurrences in this file with that table source.

By the way, this is not in the FileMaker Data API. This FileMaker metadata query was created just for the script step.

Get All Fields of a Table

Applied to a specific table occurrence, you can get all the information you need:

{ 
"action": "metaData", 
"tables": "Invoice_Customer~all" 
}

Here’s a partial response:

{
   "response":{
      "baseTable":"Customer",
      "fieldMetaData":[
         {
            "autoEnter":true,
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"id",
            "notEmpty":true,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"taxable",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"number",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"contactGivenName",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         }
      ]
   }
}

Notice it returns the table occurrence’s name and an array of every field in that table. As you can see, the FileMaker metadata provided for each field is plentiful and thorough.

Again, this step is not in the web api!

Get All Layouts

In the web api, you can use the following query to get all the layouts:

{ 
"action": "metaData", 
"layouts": "" 
}

In the Execute FileMaker Data API script step, the response includes table occurrence names. Wow. Look at this response. It even includes the folder structure.

{
   "layouts":[
      {
         "folderLayoutNames":[
            {
               "name":"Splash",
               "table":"meta"
            },
            {
               "name":"About",
               "table":"meta"
            },
            {
               "name":"PivotTable",
               "table":"meta"
            },
            {
               "name":"-",
               "table":""
            },
            {
               "name":"Home",
               "table":"session"
            },
            {
               "name":"Sales",
               "table":""
            },
            {
               "name":"Main Menu",
               "table":""
            },
            {
               "folderLayoutNames":[

               ],
               "isFolder":true,
               "name":"Utilities"
            }
         ]
      }
   ]
}

Get Fields and Table Occurence MetaData from a Layout

{ 
"action": "metaData", "
layouts": "CustomerTest" 
}

And finally, with this query:

You get the information back about the fields (and only those fields) on a specific layout.

{
   "messages":[
      {
         "code":"0",
         "message":"OK"
      }
   ],
   "response":{
      "fieldMetaData":[
         {
            "autoEnter":false,
            "displayType":"editText",
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"billAddressCity",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "displayType":"editText",
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"billAddressCountry",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "displayType":"editText",
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"billAddressLine1",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "displayType":"editText",
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"billAddressLine2",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         },
         {
            "autoEnter":false,
            "displayType":"editText",
            "fourDigitYear":false,
            "global":false,
            "maxCharacters":0,
            "maxRepeat":1,
            "name":"billAddressPostalCode",
            "notEmpty":false,
            "numeric":false,
            "repetitionEnd":1,
            "repetitionStart":1,
            "result":"text",
            "timeOfDay":false,
            "type":"normal"
         }
      ],
      "portalMetaData":{

      },
      "table":"Customer"
   }
}

Note: by specifying a layout, this script step returns the Table Occurrence name of the layout as well.

Uses

This function of the Execute FileMaker Data API script step is extremely useful. Generally, code that is portable, such as add-ons, often need to ‘sense’ where they are located. They aren’t hard coded into the context of the host solution. This step fills the gaps left in the Design Functions and the ExecuteSQL functions. So there’s a lot of use. Here’s a few other examples.

We’ve been using this new action to query information about the file for JavaScript-based add-ons. We can build a query about a specific table or field and use that to tell the JavaScript the fields from which to return data.

Also, the metadata, as I said above, is the only way to get the table occurrence upon which a layout is based.

Onward

We’re just scratching the surface at what can be done with this FileMaker metadata action. We’ll keep playing with it and exploring more we can do with the Execute FileMaker Data API script step. And we’re interested to see what the Claris community comes up with for this step.

Leave a Reply