Transaction Logging in FileMaker 2023

OnWindowTransaction is a new event trigger in FileMaker 2023.

Before we get into the details of how it works exactly, let’s clear up the terminology.

The transaction that its name implies is not limited to the transactions introduced with 19.6. It covers every commit, even if you are not using the new Open Transaction / Commit Transaction construct. Every record creation, record modification, and record deletion will fire this event, whether you do those in batches or individual records.

And it covers all those operations in all tables from all files modified from an action taken from that window where you enable this trigger. Even tables that belong to files where the OnWindowTransaction is not set. That has consequences for where you want to write out what you want to capture as part of the transaction. You cannot choose the tables you want to trigger; it applies to all tables in the file.

What Information is Captured?

Out of the box, the event trigger will include a JSON object with the following data:

The action indicates what happened to the record and will be New, Modified, or Deleted. The record ID is FileMaker’s internal ID, not your primary key. And as you know, the record ID is not a fixed ID, so it has relatively limited usefulness. The third element in the record array is for any extra data you want to pass along. That is where things get interesting.

Note that the information does not include the before and after data of the fields in the record. This feature is not meant to be an audit log.

The script you assign to the trigger will receive that JSON as a script parameter. You can write it off to a FileMaker table, write it out to a text file with the Data File script steps, or make an API call to somewhere else to shuttle the data to where you need it.

Important!

Remember that you cannot choose the tables you want the trigger to cover. It applies to all tables in the file where the trigger is enabled.

For that reason, you should NOT try to log the transaction info in a table in the file where the transaction happened. If you do, your script will then, in turn, trigger the OnWindowTransaction event, which will spawn another run of your script, which will write a record to your log table and again trigger the event…you will end up with a runaway series of events.

You need either another FileMaker file that does not have the trigger enabled or write the data to something that is not FileMaker.

Adding Your Own Information

In the trigger setup, you will notice a placeholder for a Field Name. If you do not specify a field name, FileMaker will look for a field named OnWindowTransaction in the table where the action happens and use the content of that field. If you didn’t specify a custom field and the OnWindowTransaction field does not exist, then that 3^rd element in the record’s array of data will be empty, like in the screenshot above. In our case, we are using a field named zz_onWindowTransaction to keep the field name in line with our naming conventions.

Since the transaction creates JSON, it makes sense to add your data in JSON format, although you can just add a return delimited list of things if that works better for you.

A simple example would be a calculated field with this formula:

When I now run an import against a table in a file with the trigger enabled, the log will show my extra three data elements, one for each record imported:

Certain bulk operations like Truncate Table do not trigger this event. But imports do fire the event.

What is the Performance Impact of Transaction Logging?

We used a test script that creates 10,000 records, setting four fields, including one container.

And we were using the supplemental data as per our calc earlier. Multiple runs with the trigger turned off resulted in an average runtime of 12,280 microseconds per iteration or 0.0123 seconds per pass through the loop. Doing the same with the trigger on resulted in 14,790 microseconds per iteration or 0.0148 seconds per pass through the loop. A difference of 0.0025 seconds or 1.5 hundreds of a second. Or one added second per 400 iterations.

It is fair to say, then, that for most individual actions, the added time is negligible. On operations where higher numbers of actions are required, the extra time might be significant.

In our testing example above, by running the whole operation as a single transaction like this:

Has the effect of FileMaker having to log only once at the end of the transaction, and the extra weight of collecting the data and the logging goes down from 2,500 microseconds per iteration to a mere 700 microseconds. Seven-ten-thousands of a second. It takes just over 1400 iterations to add 1 second to the overall runtime.

The impact, of course, varies by the complexity of your calculation for the extra data you want to pass along, how complex your script is, and what resources are available at the machine where the execution happens.

Next Steps in Your FileMaker Solution

If you’d like more insights into how to make your FileMaker application more efficient, we can help. Contact us to learn more.

Want to learn more about FileMaker 2023? Explore more content on the platform from our team as well as resources from Claris.