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, whether hosted or not, and receive the data in JSON format. This script step doesn't make a web service call to a host using the FileMaker Data API, nor does it depend on whether the API is enabled on a host. The similarity between this script step and the FileMaker Data API is only that the JSON format of the returned data is the same. 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.

For more information about the keys listed above, see the topics under "Work with records" as well as "Get metadata" 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 either 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.

• script and any keys that start with script. are ignored. To perform another script, use the Perform Script script step instead.

Error handling

The JSON result in Target 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.

The Get(LastError) and Get(LastErrorDetail) functions return the same values as in the code and message keys, respectively. Get(LastErrorLocation) returns where the error occurred in the script.

Notes 

• This script step executes in its own session unrelated to the context of any window or of a script that may be running, including the script that performs this script step. Therefore, use this script step as if you're making a FileMaker Data API call to a host over the network. As such, script triggers and any 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
        }
    }
}

Example 5 

Based on the Products layout, modifies the record specified by recordId, updating the values of the Stock and Name fields.

Execute FileMaker Data API [ Select ; Target: $$result ; 
JSONSetElement ( "{}" ; 
    [ "action" ; "update" ; JSONString ] ;
    [ "layouts" ; "Products" ; JSONString ] ;
    [ "recordId" ; "4" ; JSONString ] ;
    [ "fieldData" ; "{ \"Stock\" : 14 , 
       \"Name\" : \"Vanilla Cake, Large\" }" ; JSONObject ] 
) ]
Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

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

{
    "messages" : 
    [
        {
            "code" : "0",
            "message" : "OK"
        }
    ],
    "response" : 
    {
        "modId" : "6"
    }
}

If the record specified by recordId didn't exist, Get(LastError) would return 101 and Get(LastErrorDetail) would return "Record is missing", which would be the same as code and message in $$result.

Example 6 

Based on the Products layout, creates a record and sets the values of the Stock, ID, and Name fields. Because the ID field is set to auto-enter a serial number and the Prohibit modification of value during data entry option is enabled, setting the prohibitmode key to script in the options object overrides that auto-enter requirement and sets the ID field as specified.

Execute FileMaker Data API [ Select ; Target: $$result ; 
        JSONSetElement ( "{}" ; 
        [ "action" ; "create" ; JSONString ] ;
        [ "layouts" ; "Products" ; JSONString ] ;
        [ "options" ; "{ \"prohibitmode\" : \"script\" }" ; JSONObject ] ;
        [ "fieldData" ; "{ \"Stock\" : 14 , 
           \"ID\" : \"FB42\" ,
           \"Name\" : \"Croissants\" }" ; JSONObject ] 
        ) ]
        Set Variable [ $$result ; JSONFormatElements ( $$result ) ]

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

{
        "messages" : 
        [
           {
               "code" : "0",
               "message" : "OK"
           }
        ],
        "response" : 
        {
           "modId" : "0",
           "recordId" : "7"
        }
        }

If the ID field also had validation set to require a unique value and a record with the ID value of "FB42" already existed, then Get(LastError) would return 504 and Get(LastErrorDetail) would return "Value in field is not unique, as required in validation entry options", which would be the same as code and message in $$result.

Related topics 

• FileMaker Data API Guide

• Script steps reference