What Does It Do?
If you've not read it yet, we'd encourage you to check out our previous article on the Execute FileMaker Data API script step, as this article is a follow up to that. In brief, and as we described previously, this script step allows a developer to query the database, have the results returned in JSON format, and you can perform your query out of context, similar to the ExecuteSQL script step.
What's Changed?
The exciting couple of changes to the Execute FileMaker Data API script step bring parity with the actual Data API. Previously, although the Data API supported Portal Offset and Limit parameters, when including these in the script step, the result would be:
{
"messages" :
[
{
"code" : "1708",
"message" : "Unknown key (offset.email_portal_object)"
}
],
"response" : {}
}
where email_portal_object is the name of the portal on the layout being queried. Well no more folks... In FileMaker Pro 19.4, support has been added for Portal Offset and Limit and now if we include these parameters in a query we get a more satisfying:
"messages" :
[
{
"code" : "0",
"message" : "OK"
}
]
followed by the response of the query we passed.
What's The Format Of The Query?
The idea of these parameters is to return some related data from a portal but to start at a different row than the first by using Offset, and then limit the number of records being returned by using... yup, you guessed it... Limit. This can be useful in all manner of circumstances, especially if paginating through records, taking maybe ten at a time, doing something with them and then going to get the next ten.
It's important to name the portal object on the layout you're requesting the query be run against. In our example, and demo file, we have named the Email Address portal on the Contact Details layout: email_portal_object. The last contact record in the demo file, John Williams, has five email address records related to the contact record as below:
As is shown in Example 12 in the demo file, we can set up our Execute Data API query as follows to skip the first email address and limit the returned results to three related email address records - note the parameters in bold:
{
"action":"read",
"version":"v1",
"layouts":"Contact Details",
"layout.response":"Contact Details",
"limit":1000,
"portal":["email_portal_object"],
"offset.email_portal_object":2,
"limit.email_portal_object":3,
"query":[{"First Name":"==John"},{"Last Name":"==Williams"}],
"sort":[{ "fieldName": "Last Name", "sortOrder": "descend" },{ "fieldName": "First Name", "sortOrder": "ascend" }]
}
We specified our portal, and then underneath that parameter requested that the results returned start at record number two and limit the results to three. The response for the portal data section for John Williams is this:
"portalData" :
{
"email_portal_object" :
[
{
"Email Addresses::Address" : "This email address is being protected from spambots. You need JavaScript enabled to view it.",
"Email Addresses::Type" : "Personal",
"modId" : "1",
"recordId" : "10"
},
{
"Email Addresses::Address" : "This email address is being protected from spambots. You need JavaScript enabled to view it.",
"Email Addresses::Type" : "Personal",
"modId" : "0",
"recordId" : "11"
},
{
"Email Addresses::Address" : "This email address is being protected from spambots. You need JavaScript enabled to view it.",
"Email Addresses::Type" : "Personal",
"modId" : "0",
"recordId" : "12"
}
]
},
"portalDataInfo" :
[
{
"database" : "EXECUTE_FILEMAKER_DATA_API",
"foundCount" : 5,
"portalObjectName" : "email_portal_object",
"returnedCount" : 3,
"table" : "Email Addresses"
}
],
Under the portalDataInfo key, you can see while the foundCount was 5, the returnedCount was 3... Just what we expect! Awesome.
Conclusion
The updates Claris have made to this script step in FileMaker Pro 19.4 are very much welcomed. To have parity with all parts of the FileMaker ecosystem allows for cleaner and more efficient development and deployment of solutions solving business problems around the world. Don't forget you can download a demo file that explores these new features and all the other aspects of the Execute FileMaker Data API script from here. If you need some help with this, don't hesitate to get in touch.
And if you need general help with your FileMaker app, or want to save money on your licensing of Claris products, contact us and we'll be glad to assist.
