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.

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. 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.

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 FileMaker Cloud, 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.

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.

{
    "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 table associated with the Products layout.

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.

{
    "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.

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.

{
    "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
        }
    }
}

Example 4 

Based on the Products layout, returns the first record in the table associated with the Products layout and if there are portal rows, return the first 2 records after the skipping the first 2.

Execute FileMaker Data API [ Select ; Target: $$result ; 
JSONSetElement ( "{}" ; 
    [ "layouts" ; "Products" ; JSONString ] ;
    [ "limit" ; 1 ; JSONNumber ] ;
    [ "limit.RelatedProducts" ; 2; JSONNumber ] ; 
    [ "offset.RelatedProducts" ; 2 ; JSONNumber ]
) ]
Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

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

{
    "messages" : 
    [
        {
            "code" : "0",
            "message" : "OK"
        }
    ],
    "response" : 
    {
        "data" : 
        [
            {
                "fieldData" : 
                {
                    "ID" : "FB1",
                    "Name" : "Donuts",
                    "Stock" : 43
                },
                "modId" : "6",
                "portalData" : {
                    "RelatedProducts" :
                    [
                        {
                            "ID" : "FB4",
                            "Name" : "Donut Holes",
                            "Stock" : 53
                        },
                        {
                            "ID" : "FB5",
                            "Name" : "Short Cake",
                            "Stock" : 15
                        }
                    ]
                },
                "recordId" : "1"
            }
        ],
        "dataInfo" : 
        {
            "database" : "Favorite Bakery",
            "foundCount" : 3,
            "layout" : "Products",
            "returnedCount" : 1,
            "table" : "Products",
            "totalRecordCount" : 6
        }
    }
}

Related topics 

• FileMaker Data API Guide

• Script steps reference