Execute FileMaker Data API
FileMaker Pro provides a variety of methods for extracting your data from its database. From memory, we have standard relationships and calculated concatenations, the Copy All Records script step, scripted data loops and using the ExecuteSQL function. In FileMaker 19, we're provided with yet one more method for pulling data out of the system. The Execute FileMaker Data API script step allows us to use what was formerly available only from outside of FileMaker Pro via a url.
Now, within our scripts, we can use this fast retrieval method for extracting data in the popular and common JSON format. Able to use that data in both web viewers and many other places within your solution. Knowing how to use this new script step will help you extract the data you need - when you need it.
By carefully crafting a specific layout, and using the Execute FileMaker Data API script step, you'll be able employ techniques such as Virtual List and using the data for presentation within Web Viewers. You can also simply pull the data for use within UI elements as simple as a button.
Let's take a tour of this relatively new feature and understand how we can use all of its numerous features.
Comments
Layout.response and the period problem...
As I never use periods in field or table names the only issue I have is with layout.response. I have discovered that this works well for me, however it MUST be the first item in JSONSetElement as it overwrites any earlier entries.
JSONFormatElements ( JSONSetElement ( "{}"
; [ "" ; "{\"layout.response\": \"DataAPI - EVE\"}" ; JSONRaw ]
; [ "layouts" ; "DataAPI - EVE" ; JSONString ]
; [ "query[0]._kf_OrgID" ; "=" & $orgID ; JSONString ]
; [ "query[0].Date" ; $startDate & "..." & $endDate; JSONString ]
; [ "limit" ; 500 ; JSONNumber ]
; [ "offset" ; 1 ; JSONNumber ]
) )
ExecuteSQL is faster
Although the Execute FileMaker Data API may be sometimes more convenient or easier to use, ExecuteSQL seems to perform about twice as well in my performance tests. At least when extracting data from a single table. I haven't tried getting related data from other tables yet, but it's worth trying both ways especially in cases where it's used to feed virtual list with data and virtual list is used for performance reasons.
Extract data error
Hi Matt
In the 'Process Data' script the While loops should be initialised with ~i = -1 otherwise the first data item (at index 0) gets skipped in each case, as you mentioned in the video, all things JS are zero based!
Bob Avery
field doesn't need to be on layout to do a find.
As usual, good video.
I noticed that you said that when doing a find operation, the field that you are searching on has to be on the layout. Trying it out now on FM 20.3, this doesn't seem to be the case.