FileMaker 21 Adds Write Operations for Execute FileMaker Data API (eDAPI) 

The Execute FileMaker Data API (a.k.a. eDAPI) script step was first introduced in FileMaker 19.0. It made it possible to interact with data in a way that is independent of your current context, similar to the ExecuteSQL function. By “independent of current context,” we mean that we don’t need to commit our current record, navigate to a different layout, create a found set, and then interact with the records in that found set. 

But, as with the ExecuteSQL function, the eDAPI step was limited to read operations only. FileMaker 21 adds the ability to perform these write operations: 

• Create 
• Update 
• Duplicate 
• Delete 

Sample Requests 

If we have a log table with a “Log” layout and a “LogData” field, we can now perform these operations:

Create

{ 
	"action" : "create", 
	"fieldData" :  
	{ 
		"LogData" : "Placerat Elit Vulputate Penatibus"
	}, 
	"layouts" : "Log" 
}

Update

{ 
	"action" : "update", 
	"fieldData" :  
	{ 
		"LogData" : "Turpis Class Lectus" 
	}, 
	"layouts" : "Log", 
	"recordId" : 14 
} 

In SQL-speak, the ‘where’ clause is limited to matching on recordId. In other words, we aren’t able to specify which record to update in any way other than by using the internal record ID, which corresponds to what is returned by Get(RecordID). 

Note that internal record IDs aren’t as persistent as you might think. Their values can change, for example, if you recreate or reimport the record.

Duplicate

{ 
	"action" : "duplicate", 
	"fieldData" :  
	{ 
		"LogData" : "Ligula Lobortis Senectus" 
	}, 
	"layouts" : "Log", 
	"recordId" : 14 
}

Delete

{ 
	"action" : "delete", 
	"fieldData" :  
	{ 
		"LogData" : "Fermentum Etiam Ultricies Arcu Cubilia" 
	}, 
	"layouts" : "Log", 
	"recordId" : 15 
} 

Creating Related Records by Using portalData 

The ‘create’ action only allows creating one record at a time in the table used by the target layout. But you can create (or update) multiple records by placing a portal on the layout. Make sure “Allow creation” is checked on for that portal’s table occurrence. 

The following request will create one Log record and two LogChild records: 

{ 
	"action" : "create", 
	"fieldData" :  
	{ 
		"LogData" : "Consectetur Enim " 
	}, 
	"layouts" : "Log_withPortal", 
	"portalData" :  
	{ 
		"Log_LogChild" :  
		[ 
			{ 
				"Log_LogChild::ExtraLogInfo" : "Commodo Justo" 
			}, 
			{ 
				"Log_LogChild::ExtraLogInfo" : "Sapien Pulvinar" 
			} 
		] 
	} 
}

This behavior could be used to create records in batch, so long as the relationships are set up correctly. 

Layout Field Requirements 

The eDAPI operations are independent of your current context, but the step does set up its own layout-based context. When you construct the request JSON, you need to specify a target layout by name. And depending on the type of call you’re making, you might need to set up the layout to have the fields you want to interact with. 

The previously-existing read operation (not new in FileMaker 21) requires that the fields you want to access are present on the target layout. 

The new create/update/duplication/delete operations do not require fields to be present on the layout. The exception is if you are specifying portalData, then you need those fields to be there. 

Error Handling 

As mentioned before, when you construct the request JSON, you need to specify a target layout by name. 

Hardcoding solution elements like layout names, field names, etc., is brittle. If you later change the name, your code breaks. For some solution elements, like table occurrence and field names, we can mitigate this by using indirection. If we refer to a field using GetFieldName(field), we don’t have to hardcode those names. But we don’t have this capability with layout names and must do extra error handling to check for missing layouts. 

In the demo file provided with this blog post, I use a custom function to specify the layout name. The name is still hardcoded, but this way, hardcoding is centralized in a single place, making it easier to change the value if the layout is ever renamed. 

Here’s what the eDAPI response looks like when the target layout is missing.

{ 
	"messages" :  
	[ 
		{ 
			"code" : "105", 
			"message" : "Layout is missing" 
		} 
	], 
	"response" : {} 
} 

The Get(LastError), Get(LastErrorDetail), and Get(LastErrorLocation) have been updated to include information about Data API errors, so that you don’t have to parse the eDAPI response yourself. 

• Get(LastError) – returns the eDAPI response error code from messages[0].code 
• Get(LastErrorDetail) – returns the eDAPI response error code from messages[0].message 
• Get(LastErrorLocation) – returns the script name, script step name, and line number 

Layout Best Practices 

Follow these best practices to minimize the risk of things breaking and to optimize performance so that only fields that are needed are returned by the eDAPI calls: 

• Use dedicated layouts for eDAPI calls. Name the layouts with an “edapi_” or similar prefix. 
• Place the layouts in a folder that makes it clear what the intended use is and reminds developers not to rename the layouts haphazardly, e.g., “Used in eDAPI – DO NOT RENAME” 
• Only add the fields to the layout you need for the eDAPI calls you’ll make. 
• When sharing the layout among different eDAPI calls, create a new layout if you need to add unstored calcs, portals, summary fields, or fields from related tables. This is to avoid sharing the performance hit caused by those new fields/portals. 

Performance Considerations 

Having this new capability in our toolbox is great, but are there any performance considerations to be aware of? 

My colleague, Karl Jreijiri, ran some tests to see how eDAPI writes compare to the “regular” way of making these changes, i.e., switch layouts, make data changes, commit, and return to the original layout. 

The tests were done in a controlled environment where the latency between Pro and Server was simulated at 35 ms. Here is the average duration for creating a new record using both methods. 

Frustratingly, the eDAPI step is more than twice as slow as the “regular” method. 

Both methods are drastically faster (TWO orders of magnitude) if the work is sent to the server (via Perform Script on Server), but even then, the eDAPI step is slower than the “regular” method. 

From the help documentation, “[c]hanges made by the Execute FileMaker Data API script step are not included in the script’s transaction. Any operation completed within this script step, even if enclosed within a transaction, will not be reverted.” 

Each eDAPI write operation is run in its own transaction outside of whatever transaction you may or may not have set up in your script. In other words, the Open/Close Transaction steps do not impact this new eDAPI functionality. 

When creating records using Open/Close Transaction and the “regular” approach, the write operations speed up. But that isn’t the case with eDAPI. 

Local vs. Hosted 

To use the Data API, a file must be hosted on FileMaker Server. Because of this script step’s name, you might assume that this script step requires the same. It doesn’t. This script step works internally inside the FileMaker file; it does not make an external call over the network to the FileMaker Server Data API. As a result, this script step is available when a file is opened locally. 

Demo File 

You can download a demo file to learn more about how the new write operations work:

The login for the file is admin / admin. 

Your Next Steps with Execute FileMaker Data API 

The new write capability for the Execute FileMaker Data API step is a welcome new addition. It simplifies how we can perform write operations (create, update, duplicate, delete) in a way that doesn’t interfere with our current context and requires minimal setup (in most cases, we only need a layout to target). 

But there are some caveats to keep in mind: 

• The new write functionality is slower than the regular “go to layout – new record/set field – commit” steps. 
• eDAPI write operations are much faster when run on the server. 
• If related records are being targeted, the layout requires the portal to be on the layout. 
• Each eDAPI write operation always runs in its own transaction that operates outside the bounds of other scripted transactions. 

If you need help implementing this in your FileMaker application or want to further customize your solution, our team of FileMaker consultants can help. Contact us to set up a meeting with one of our consultants.