A practical guide to Transactions in FileMaker

By Jonathan and Bert.

FileMaker support for transactions

Support for Transactional scripting was introduced in FileMaker 19.6, creating much excitement in the developer community. This was something we had looked forward to for a long time and implemented various workarounds to make it work anyway. Transactions are a great way to ensure data consistency when performing complex tasks, particularly those that write data to multiple records, such as invoicing and stock management. If the process encounters an error, you are not left with a half-completed set of records; the whole process is just rolled back. This helps to keep our application reliable and error-free.  

Now that we have been working with Transactions for a few months, it’s time to take stock and consider how we use them, and highlight some potential limitations and caveats. 

What are transactions

Before we take a deep dive into the matter, it’s best to take a step back and define just what we mean when we talk about a Transaction. A Transaction consists of a set of data operations that we need to execute together. If something goes wrong with any one of the operations, we need the system to revert to its initial state, as if nothing ever happened. So either everything succeeds, or nothing happens. The main goal is to make sure that our data never ends up in an invalid or inconsistent state. 

For example, if money needs to be transferred from account A to account B, then we need to perform two data operations; account A needs to be debited and account B needs to be credited. Both operations always need to succeed together, or else money would magically disappear or appear out of nowhere. Similarly, if we are creating a customer invoice, we need all the invoice lines to be created successfully, or the invoice total will be incorrect. 

See the attached demo file below. In “NewInvoiceNoTransactions”, we try to create an invoice record and two invoice lines. Due to a faulty parameter, one of the invoice lines is not created and that leaves us with an incomplete invoice in our database. 

How did we solve this problem in the past?

In the past, many of us have tried ‘bake your own’ Transactions, making use of the existing commit and revert record script steps. However, FileMaker implicitly commits the data when moving to between records, changing the layout, or closing a window. The only way to avoid these implicit commits is to stay on the same record and to handle all operations with related data through portals. Then, when we commit, the current record and all modifications we’ve made through portals are validated and committed in one go. 

Code about how transactions were written in the past

The big disadvantage of this way of working is the added complexity, requiring additional relations, layouts with specific portals, and scripting. This in turn means more time spent developing and maintaining the app. 

See “NewInvoiceWithOldSchoolTransactions” in the demo file where this method is used. It works, but it is a bit more complex, and we’re also dependent on specific layout objects. None of the portals may be deleted or renamed, as they are vital to our script.

What has changed now?

Three new script steps were introduced in FileMaker 19.6 to make native Transactional scripting easy to implement: Open Transaction, Commit Transaction and Revert Transaction. FileMaker 2023 introduced a new function TransactionOpenState, which returns 1 if called within a Transaction, and 0 if called outside a Transaction. 

Transaction script steps

The Open and Commit Transaction script steps must be used together to define the start and end of the Transaction; rather like the Loop and End Loop, or the If and End If steps, they define the ‘scope’ of the Transaction. With a few exceptions (discussed below), all data operations that occur between these two script steps are Transactional and are committed or reverted together. Just like the Loop and If statements, the Open and Commit Transaction must be in the same script, and can not be split up or nested separately in a subscript. 

A Transaction can be explicitly reverted using the Revert Transaction step, with a logical test similar to an If statement. However, it can also automatically revert: 

  • If certain script steps result in an error (see below). 
  • If the script encounters certain field validation errors — depending on the validation type, this may be triggered when you use the Commit Record/Request step, or when you try to commit the whole Transaction (see below). 
  • If the window where the Transaction started is closed. 
  • If the Halt Script step is used, or you stop the script via the debugger window. 
  • If the script fails to reach a Commit Transaction step (for example, if the app crashes or there is a power failure). 

All modifications are only permanently saved when the whole Transaction is successfully committed. See “NewInvoiceWithTransactions” where we have added the new script steps to improve our faulty first example. 

In the Revert Transaction step, we can even specify a custom error code and message. In “TransactionWithRevert” we use this to revert the Transaction conditionally and to pass a custom error along. The custom error code must be a number between 5000 and 5499 — if the error code is outside of this range it is ignored, and the custom error message won’t function either. When it passes the Commit Transaction step, the script debugger returns the error detail and the exact script step where it occurred. This is also returned by the Get ( LastErrorDetail ) and Get ( LastErrorLocation ) functions. 

We only need to add two script steps to make our code Transactional. Sounds great, right? Unfortunately, this is not always the case — there are a few behaviors that can catch out the unwary developer. 

Reverting a Transaction

The first thing to note is that a Revert Transaction step operates very much like an Exit Loop, and will cause the script to jump straight to the end of our Transaction. All the code in the Transaction block that comes after the Revert Transaction step will not be executed. This could be a problem if you’ve got some kind of navigation logic at the end of the Transaction. By exiting the Transaction prematurely, you could end up on a different layout (and in a different context) than you expect.  

It’s also important to keep in mind that Transactions can also revert automatically if certain script steps fail. In some cases, the error will cause the transaction to revert immediately, in other cases the script will continue, and only revert when it encounters the Commit Transaction step (see below). The demo file has a series of scripts showing which steps cause an automatic revert, and at what point.  

As a rule of thumb, the Transaction will revert immediately after errors involving an incorrect context, record-locking or insufficient permissions. A notable exception to this is attempting to use Truncate Table while a record is locked. The truncate will fail with an error 301 (a record is in use) and no records will be deleted, but this will not trigger an automatic revert. 

Some field validation rules can also trigger an immediate revert, but only if they can be evaluated without referring to information from other records — in practice this means Not empty, Strict Data Type, In range, and Maximum number of characters — and then only if the validation is set to Always Validate

A failed validation of the Member of value list will cause an immediate revert, but be careful when using this: if the value list uses related records, or involves looked-up data, any changes made during the transaction (such as adding records or updating fields) will not update the value list for the purposes of the validation rule. 

Other validations, such as Unique value, Existing Value, and Validated by calculation may require information that is outside of the scope of the Transaction, and so they are not assessed during the transaction itself (even if you include a Commit Records/Requests step). However, they may cause the Transaction to revert when it reaches the Commit Transaction step. This means that, although the validation rule will cause the Transaction to revert automatically, your user may have to wait for the entire script to run before finding out that the Transaction has reverted and they have not achieved anything! 

Missing fields, and attempting to Go To Related Records with broken or invalid relationships will not cause the Transaction to revert automatically, nor will any action in Find Mode; you must catch these errors yourself to avoid ending up in an unexpected context. If you are unsure, always add an explicit Revert Transaction step!  

Limitations

There are a few other things we need to keep in mind when working with Transactions and the way they function in FileMaker: 

  • Transactions can’t be nested. If a Transaction is already open, any subsequent Open Transaction steps are ignored (you will see an error code 3). This is also the case for subscripts containing an Open Transaction or a Revert Transaction
  • Every Transaction is “bound” to the window it started in. If this window is closed, the Transaction is immediately reverted. All steps that run in another window are no longer part of the Transaction and will not be reverted. Only the operations that were executed in the original window are part of the Transaction and can be committed or reverted. See the example script “TransactionsIncorrectUseOfNewWindow” where windows are used incorrectly. This can be used to our advantage, to debug a Transaction. We can open a new window to create a log record tracking the progress of the Transaction; this will remain even if the Transaction is reverted.
  • Data that has been created or modified during a Transaction is not visible in another context. For example, if you create an invoice record, then go to another layout and create a related invoice line, the line record won’t be able to look up the invoice number from the invoice record. See the example script “TransactionsFetchRelatedData” where this works without Transactions, but not during Transactions. 
  • Field validation rules that analyze other records (such as Unique value or some custom validation rules) will not be assessed until the Transaction is finally committed.  
  • Global fields completely ignore Transactions and retain their values even if the Transaction gets reverted. See the example script “TransactionsAndGlobals”. Similarly, any data files that are created or exported will remain on the hard drive. 
  • Using Delete Record or Delete All Records will apparently remove the record straight away. For example, the found count is reduced and it won’t contribute to any summary fields. However, until the Transaction is committed, the record can still be recovered by a Find (for example, by searching for the record ID). Any attempt to edit the deleted record will result in the Transaction being immediately reverted with an error 101 (record missing). But, if you do not attempt to edit it, and it is still the current record when you commit your Transaction, it will be deleted but will remain displayed on the layout. This can be a bit confusing! 
  • Truncating a table can’t be reverted.  
  • Certain script steps like opening Manage Database, Open Manage Container, Open Manage Data Source, Re-login, and Save Copy directly commit the Transaction. However, you don’t jump to the end of the Transaction. Rather, you continue to execute the code within the Transaction block, but you are no longer working Transactionally (the function TransactionOpenState will return 0). When you come to the final Comit Transaction, it will cause an error 3, as there is no Transaction to commit.  

Unexpected benefit: Performance gain

During a Transaction, all modifications are done in a local temporary file and are only saved to the main file when the Transaction is committed. All regular commits in between are “suppressed” and not executed. Although this is purely done to allow Transactions to work In FileMaker, it comes with an unexpected benefit of slightly improving the speed of the operations. Since nothing is saved on the server until the final commit where everything is saved in one go. Note that this only applies to scripts run on FileMaker, and not PSOS or scheduled server-side scripts. 

Conclusion

The new Transaction script steps are a powerful way to maintain data consistency. However, as with any new feature, we need to keep an eye on the exact way they behave, or we may get caught out.  

  • Have some navigation steps after the Commit Transaction step, to make sure that you end in on the correct layout, even if the Transaction reverts. 
  • Use the explicit Revert Transaction step to catch errors that do not cause the Transaction to revert automatically. 
  • Keep an eye on the actions performed in subscripts and in new windows. 
  • Beware of Re-login and other steps that can prematurely (and silently) commit your Transaction. 

Read part 2: Error Reporting in Transactions