Execute FileMaker Data API

Executes a FileMaker Data API request.

Options 

  • Select entire contents replaces the contents of a field or variable. If you don’t select this option:
    • For a field, replaces only the selected portion of the active field, or inserts data at the insertion point. The default insertion point is at the end of the field's data.
    • For a variable that doesn’t have container data, inserts data at the end of the variable’s current value. For a variable that has container data, replaces the contents of the variable.
  • Target specifies the field to insert the result into or the variable to set. If the variable doesn’t exist, this script step creates it (see Using variables).
  • Request is a calculation that specifies the request as text. The text is a JSON object in the format described below.

Compatibility 

Product Supported
FileMaker Pro Yes
FileMaker Go Yes
FileMaker WebDirect Yes
FileMaker Server Yes
FileMaker Cloud products Yes
FileMaker Data API Yes
Custom Web Publishing Yes

Originated in version 

19.0

Description 

The FileMaker Data API is a REST API available as a web service for FileMaker Server and FileMaker Cloud products. Web applications can use this API to send requests and receive data in JSON format from hosted FileMaker Pro files.

Using the same underlying functionality as the FileMaker Data API on FileMaker hosts, this script step enables a script performed by any FileMaker product to request data in the current file and receive the data in JSON format. The request is a simple JSON object, rather than the combination of URL, header, and request body required by the FileMaker Data API to use a FileMaker host's web service. The result returned in Target is in the same JSON format as when requested from a host via the FileMaker Data API.

Request format

This script step supports the following key-value pairs in the JSON object specified by the Request option. If one of these keys isn't specified in the request, the default value is used.

Key Default value Description
action "read" read and metaData are the only supported values. Use metaData to retrieve information about tables and layouts.
version "v1" v1, v2, and vLatest are supported values. Behavior and generated results differ based on the API version. See FileMaker Data API Guide.
layouts   A layout name. Required for read and layout metaData actions.
tables   A table occurrence name. Required for table occurrence metaData actions. Works like the layouts key. If the table occurrence is specified, the metadata for that table is returned. If no name is specified, the list of table occurrences is returned.
query all records An array of JSON objects, each specifying a field and find criteria. See "Perform a find request" in FileMaker Data API Guide.
recordId   The unique ID number of a record. You can't specify both a query and recordId key. See FileMaker Data API Guide.
sort   A JSON object that specifies sort order. See "Get a range of records" and "Perform a find request" in FileMaker Data API Guide.
offset 1 The record number of the first record in a range of records. See "Get a single record," "Get a range of records," and "Perform a find request" in FileMaker Data API Guide.
limit 100 The maximum number of records that should be returned. See "Get a single record," "Get a range of records," and "Perform a find request" in FileMaker Data API Guide.
layout.response value from layouts To retrieve the data in the context of a different layout, specify a layout name. See FileMaker Data API Guide.
portal all portals A JSON object that specifies a portal. See "Get a single record," "Get a range of records," and "Perform a find request" in FileMaker Data API Guide.

The following keys are ignored:

  • databases is ignored because the database is always the one belonging to the window that the script is running in.
  • Authorization is ignored because the script's privileges are those of the current user or full access, if the current script has been granted full access privileges.

  • Content-Type is ignored because the request must be in JSON format.
  • fieldData, portalData, and modId are ignored because the create and update actions are not supported.
  • script and any keys that start with script. are ignored. To perform another script, use the Perform Script script step.

Error handling

After this script steps runs, the Get(LastError) function always returns 0 ("No error"), even if there's an error, unlike other script steps. To check for errors, use the JSON result in Target instead.

The result contains at least a messages key with an object containing message and code keys. The code values are those defined in FileMaker error codes. Most of the message and code values are the same as those returned by the FileMaker Data API available as a web service on FileMaker hosts. Some additional errors are unique to this script step and help to identify problems when parsing the Request option. These additional errors use code values of 3, 1708, and 1710 but use many different message values to provide more detail about the error.

Notes 

  • When this script step is performed, it operates on a hidden window using the layout specified in the request. The window is closed after the results are returned.
  • Script triggers and error reporting dialog boxes are disabled while this script step is performed.
  • If the file is hosted by FileMaker Server or a FileMaker Cloud product, the returned values of container fields are URLs that can be used to fetch the field contents. If the file is opened locally, only the name of the file in the container field is returned.

Example 1 

Returns metadata for all table occurrences by specifying no value for the tables key. In this case, there's one source (or base) table (Products) and two table occurrences (Products and Products_InStock). This example uses the JSONSetElement function to create the JSON object in the Request option.

Copy
Execute FileMaker Data API [ Select ; Target: $$result ; 
JSONSetElement ( "{}" ; 
    [ "action" ; "metaData" ; JSONString ] ;
    [ "tables" ; "" ; JSONString ] 
) ]
Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

The JSON data returned in the global variable $$result has this form.

Copy
{
    "messages"
    [
        {
            "code" : "0",
            "message" : "OK"
        }
    ],
    "response"
    {
        "tables"
        [
            {
                "baseTable" : "Products",
                "name" : "Products"
            },
            {
                "baseTable" : "Products",
                "name" : "Products_InStock"
            }
        ]
    }
}

Example 2 

Based on the Products layout, returns the first record in the current found set.

Copy
Execute FileMaker Data API [ Select ; Target: $$result ; 
JSONSetElement ( "" ; 
    [ "layouts" ; "Products" ; JSONString ] ;
    [ "limit" ; 1 ; JSONNumber ]
) ]
Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

The JSON data returned in the global variable $$result has this form.

Copy
{
    "messages"
    [
        {
            "code" : "0",
            "message" : "OK"
        }
    ],
    "response"
    {
        "data"
        [
            {
                "fieldData"
                {
                    "ID" : "FB1",
                    "Name" : "Donuts",
                    "Stock" : 43
                },
                "modId" : "6",
                "portalData" : {},
                "recordId" : "1"
            }
        ],
        "dataInfo"
        {
            "database" : "Favorite Bakery",
            "foundCount" : 3,
            "layout" : "Products",
            "returnedCount" : 1,
            "table" : "Products",
            "totalRecordCount" : 3
        }
    }
}

Example 3 

Based on the Products layout, performs a find for records where the Stock field is less than 40, and returns the found set sorted by the Stock field in descending order.

Copy
Execute FileMaker Data API [ Select ; Target: $$result ; 
JSONSetElement ( "{}" ; 
    [ "layouts" ; "Products" ; JSONString ] ;
    [ "query" ; "[ { \"Stock\":\"<40\" } ]" ; JSONArray ] ;
    [ "sort" ; "[ { \"fieldName\":\"Stock\" , 
                    \"sortOrder\":\"descend\" } ]" ; JSONArray ]
) ]
Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

The JSON data returned in the global variable $$result has this form.

Copy
{
    "messages"
    [
        {
            "code" : "0",
            "message" : "OK"
        }
    ],
    "response"
    {
        "data"
        [
            {
                "fieldData"
                {
                    "ID" : "FB3",
                    "Name" : "Baguette",
                    "Stock" : 34
                },
                "modId" : "1",
                "portalData" : {},
                "recordId" : "7"
            },
            {
                "fieldData"
                {
                    "ID" : "FB2",
                    "Name" : "Chocolate Cake",
                    "Stock" : 23
                },
                "modId" : "1",
                "portalData" : {},
                "recordId" : "6"
            }
        ],
        "dataInfo"
        {
            "database" : "Favorite Bakery",
            "foundCount" : 2,
            "layout" : "Products",
            "returnedCount" : 2,
            "table" : "Products",
            "totalRecordCount" : 3
        }
    }
}