FileMaker 2023: OnWindowTransaction Script Trigger

FileMaker 2023 includes several useful new features, like Perform Script On Server with Callback. Another noteworthy feature we want to explore is a new script trigger, OnWindowTransaction. We can find this script trigger within the list of script triggers that appear within the File Options selection of the active FileMaker file.

Where this script trigger appears is the first indication that it is a little different than the others. While other script triggers fire when we modify a field, enter a layout, or change modes, the reach or context of what causes this script trigger to execute is a bit larger. Any time we commit a record in any table, it will fire. Considering how many times we commit a record in FileMaker, it’s clear we must handle this event with some care.  

The other unique feature of this script trigger is that, when called, it will automatically create a JSON block of data, and FileMaker will pass that data to the script it calls as a script parameter. We created a test file with one record to demonstrate this function and wrote a script to set a global variable with that script parameter. Then we created a dialog that displays that global variable. Here’s the script: 

We associated this script with the OnWindowTransaction script trigger. Next, we specified a field in the Field Name section, in this case, “PrimaryKey.”  

After setting this, we returned to the database and edited one record. The minute we committed that record, the script fired. Again, it will fire whenever we commit a record in any table in the FileMaker file. So, handle it with care. Here’s what the screen looked like when we committed that record. 

Let’s break that down line-by-line:

1. “TestFile” is the name of the active FileMaker file.  

2. “TestTable” is the name of the table where an action has occurred.  

3. The Third section is a JSON array with these elements:

1. Action taken
2. Record ID of affected record 
3. The value of the specified field name

Thus, here it tells me that we 

1. Modified a record
2. The record ID was 1
3. PrimaryKey value was 6CE2F8A1-0915-4C7A-8C9F-E041E7E23E2C

We will see an array of all committed records for this transaction. Let’s think about the “actions” part of the JSON block for a bit. This script trigger will fire whenever a record is created, modified, or deleted. It will do this regardless of whether these actions occur one record at a time or within a batch of records simultaneously.

Again, this script trigger allows developers to create a transaction log, and as such, we would naturally want to document those three critical types of transactions. If we create a new record, the JSON block looks like this: 

If we delete a record, the JSON block looks like this: 

So that’s the basics of how the script trigger works. It will show us the file we’re working on, the table where the action took place, the action that took place, the record ID of the impacted record, and then whatever information is in the field we specify to pass along.

Now, to step beyond the basics, we’d like to review two things:  

• The field we specified when setting up the script trigger

• What to do with the captured data

THE SPECIFIED FIELD

The field we type in is unique in FileMaker land. First, this field should be in every table we want the trigger to work on and the name should be the same in each table. Second, we expect similar results for each table, or at least consistent results, so we’ll have to think about ways to set up this field to work and report back data from the context of all tables in our file. For example, we could capture who the current user is and have the script trigger add that to the data it reports back.

With some work, we can also use the script to report the current values of all the fields in the current table. A great use case would be to have it document all the data in a record – right before it is deleted. That way, we’ll have a catalog of the data and a way of rolling it back if necessary. The script can include any data we can think of to store in a field in the JSON results.

WHERE TO STORE THE DATA

Second, let’s discuss what to do with this information once we have it. This trigger fires every time we modify a record. Let’s say we create an internal audit log to store data whenever a record changes. When we do that, we’ll make a new record, which will trigger the OnWindowTransaction event, then try to create a new record, and so on. Suddenly, we’re stuck in a never-ending cascade of events.

The solution is to move the data outside of our file somehow. We can send it to another FileMaker file, write it to a text file, or push it to an external API. For example, let’s try the first option – create a separate FileMaker file and push the data to that file as part of the script associated with the script trigger.

Before coding, let’s think about what that would look like. We want a simple table that captures the log data without causing a never-ending cascade. We want to see when a record in our tables has been created, modified, or deleted. To do that, we’ll create a new FileMaker file named “auditLog.” The file has one field named “Log.” We’ll change the script associated with our OnWindowTransaction to run a new script in our auditLog file that will receive the JSON block as a script parameter and save it into the Log field. Here’s how the script in our TestFile looks now:

And here’s the script in the auditLog file.

So then we created a new record, deleted that record, and modified a different record. Here’s what our auditLog file looks like.

Now we have three new records, each with the JSON block from the action that triggered the event. 

A GOOD PAIRING

One final word on transactions: this script trigger makes the transactions in our solutions more explicit. By using it, we become more aware of how often they happen. However, it also adds overhead to each action, and we should be mindful of the scripts we attach to it and how they will affect the performance of our system. Like a good wine and cheese pairing, this script trigger pairs well with the new Open Transaction and Commit Transaction script steps when thinking about the performance and overhead of this new tool. Using these script steps, we can group actions into one large transaction and thus trigger this new OnWindowTransaction just once, at the “Commit Transaction” step.