Execute FileMaker Data API in Claris FileMaker 19
How to Execute FileMaker Data API in Claris FileMaker 19
If you don’t know about FileMaker data API, we explain everything you need to know about FileMaker’s data API, including what it is, how it can help your business, and example integrations to leverage. The possibilities are endless.
- The Changing World of Travel
- The Changing World of Data Retrieval
- Data Retrieval Tools Compared
- It Takes a Village
The Changing World of Travel
My much-anticipated first trip to Mexico was in March of 1998. I was looking forward to being immersed in Spanish to practice my new language skills, but when my parents, two siblings and I got to the airline ticket counter at the airport an hour away, we were not sure if we would be allowed to fly. My mom had forgotten her driver’s license, so all we had was one driver’s license and two driver’s permits. We discretely put them on the counter all together, and no questions were asked! We left in faith not knowing if it would be a problem to re-enter the country. With the modern-day security restrictions, especially for international travel, I look back in disbelief that we managed to make the round trip without any issues. The world of travel has sure changed a lot since then!
The Changing World of Data Retrieval
Claris FileMaker 19 was just released and provides us with a new script step called “Execute FileMaker Data API”. While travel restrictions have gotten tighter, I’m thankful to see that data retrieval restrictions are lessened with this script step as compared to the FileMaker Data API, introduced in version 16. Using the FileMaker Data API could be likened to modern-day international travel that strictly requires a passport for identification, the authorization key, and it has a price tag in that you use some of your FileMaker Data API Annual Limit. The new script step could be likened to “virtual travel” in that it operates locally on the database of the window the script is called from and does not require the authorization key or consume any of the FileMaker Data API Annual Limit.
Data Retrieval Tools Compared
Exploring new tools to understand how and when to use them is important for developing your skill set. It may be tempting to reach for the new and shiny tool right after exploring how to use it, however, it takes time and experience working with a tool to recognize the less obvious overall implications of using it.
What are some of the tools you should compare with the new script step “Execute FileMaker Data API” to determine if it is the right one for your use case? From what I understand, the intention of the script step is to enable users to create an array of records as per specific criteria in a JSON format, therefore, the criteria to identify the tools for comparison are:
- The ability to gather data according to specified criteria, and
- The format of the result
Claris FileMaker was designed and built to perform finds and then display the found set within a list or a form view. This native, basic FileMaker Find functionality is the first tool you should compare. The second tool to compare is the “ExecuteSQL” function.
You may be hoping that I will provide specific use cases for each tool, but that is difficult to do since the answer would be “it depends”… you as the developer need to evaluate what the needs are as well as the potential benefits or pitfalls of using each tool. At Skeleton Key, we prize simplicity of code, which I appreciate more and more over time. I would encourage you to learn how to use the new tools and select the one that will accomplish the goal with the simplest code. Whoever comes in to review or revise your code at a later date, which could be yourself, will appreciate the clarity!
Perhaps you are reading this blog to learn how to use the new script step and see example JSON. Jeremy Brown of Geist Interactive has an excellent blog with this information that is linked in the references below.
FileMaker Find vs. the “Execute FileMaker Data API” Script Step
As you most likely already know, the native FileMaker Find is comprised of entering find mode, navigating to a layout to set the context for the find, specifying the search criteria, and performing the find (including constraining or extending the existing found set). The results are displayed in either the list or form view. A standard find is very powerful and is still the most straightforward way to display a found set of information and to summarize it using sub-summary parts. The FileMaker Find could be likened to a hammer or a screwdriver in the carpenter’s toolbox, it is a fundamental tool that fits many use cases in such a way that a future developer can easily understand how something was built and revise it as needed.
Similar to a FileMaker Find, the new script step “Execute FileMaker Data API” requires the context of a layout in order to perform a query, however, the script does not require you to explicitly navigate to that layout. The query is also able to retrieve data from related fields based on what fields and portals are present on the target layout. “Execute FileMaker Data API” returns the results of the query in a JSON format versus as a found set of records like a FileMaker Find.
“ExecuteSQL” Function vs. the “Execute FileMaker Data API” Script Step
Albeit in infancy form, the “Execute FileMaker Data API” script step seems to most closely resemble the “ExecuteSQL” function that was added in FileMaker version 12. ExecuteSQL is a powerful tool to enable the gathering of data according to specified criteria within the query statement. This function was added to potentially reduce the complexity of the relationship graph since you can execute queries on table occurrences independent of relationships. Over time, some of the pitfalls of “ExecuteSQL” have become obvious. The performance of SQL queries is substantially less when employing certain aggregate functions on large data sets as compared to standard FileMaker Finds. In addition, there is a significant performance impact when a user does a local query (not Perform Script on Server) and that same user has as an open record in the target table. Due to this behavior and the need to have all records committed when a SQL call is made, the use of ExecuteSQL should be limited to scripts and avoided in field definitions or other locations such as conditional formatting or object hiding. (See the linked blog by Wim Decorte and FMForums thread below.) The new “Execute FileMaker Data API” script step offers similar promise to retrieve data without making the relationship graph more complex, but with the advantage of being in a JSON format. It is too early to say what the pitfalls may be, and we haven’t had the time to do significant testing yet.
In the table below, the standard “ExecuteSQL” SELECT statement clauses are mapped to the “Execute FileMaker Data API” script step keys:
| ExecuteSQL SELECT statement clauses | Execute FileMaker Data API script step keys |
|---|---|
| SELECT [DISTINCT] {* | column_expression [[AS] column_alias],…} | read (using the fields from the layout specified by the layouts or layout.response key) |
| [ WHERE expr1 rel_operator expr2 ] | query |
| [ GROUP BY {column_expression, …} ] | (not available) |
| [ HAVING expr1 rel_operator expr2 ] | (not available) |
| [ UNION [ALL] (SELECT…) ] | the query key can be an array with more than one element to mimic the AND / OR find behavior |
| [ ORDER BY {sort_expression [DESC | ASC]}, … ] | sort (both permit specifying ascending or descending order as well as allowing multiple fields to sort by) |
| [ OFFSET n {ROWS | ROW} ] | offset |
| [ FETCH FIRST [ n [ PERCENT ] ] { ROWS | ROW } {ONLY | WITH TIES } ] | limit |
It Takes a Village
This post would not have been possible without assorted resources available from the Claris Community, such as:
- Claris FileMaker 19 Data API Guide
- Claris FileMaker SQL Reference
- Claris FileMaker 19 Execute FileMaker Data API Script Step by Jeremy Brown of Geist Interactive
- ExecuteSQL() – the Good, the Bad and the Ugly by Wim Decorte of Soliant Consulting
- FMForums thread about using ExecuteSQL – see Wim Decorte’s comments about the instances that negatively impact the performance of ExecuteSQL
About Skeleton Key
Skeleton Key develops apps on the Claris FileMaker platform making them easy-to-integrate, easy-to-use, and quick to build and deploy. Our team of experts takes a comprehensive consulting approach to focus on learning how your organization operates. With deeper insights into the way your team works, we’re able to create an ideal solution built around your operations while forming a partnership founded on trust and transparency. We hope you found this content useful and we would love to hear from you if we can be of any further assistance.
