Transactions in FileMaker 19.6: Part 1

What Are Transactions?

We each conduct transactions every day. We transact when we talk with other people, when we buy goods from a store, and when we check out a book from the library. In the database world, transactions have a similar meaning – exchanging this for that, whether it be ideas, encouragement, or money.

Transactions are also a database concept for maintaining data integrity. When working with databases, there are times when you want to make several changes to the data in a single operation. If something happens that prevents one of the changes from completing successfully, you want to undo the other changes and return the database state to where it was before you made the changes.

Consider an online purchasing system. Users can place orders for items In Stock and if an item is Sold Out then it should be marked as unavailable for purchase. The following operations might be part of that purchase:

1. An invoice for the item gets created, and the user’s account shows a charge for the item
2. The inventory quantity for the item reduces by 1
3. If the inventory drops to zero, the item becomes marked as unavailable for purchase in the sales system

If during this process you discover that the inventory quantity is already zero (perhaps because someone else has purchased the last unit), you will want to undo changes you made as part of this sale.

Transactions help you accomplish this goal. They keep all of the changes in a temporary space and then attempt to save those changes to the database. If any one operation fails nothing is written back to the database. This is called an “atomic” transaction.

Understanding the role of Error Trapping is important when working with transactions. FileMaker Pro uses the LastError flag, available via the Get ( LastError ) function, as a means of indicating whether the steps of a transaction succeeded or failed. For example, if our script attempts to decrease the inventory quantity by 1 yet another user has that record locked, our attempt will be blocked. The LastError flag will be set to ‘301’ indicating “Record is in use by another user.” For more on error codes, see the FileMaker Error Codes reference in Claris online help.

Transactions in FileMaker

FileMaker Pro has had transactions for some time now, although they have gone unnoticed by many developers. Whenever you initiate a change in the database, that creates a transaction. Creating a record, editing a record, and deleting a (related) record all open a new database transaction. With the exception of deleting a record, each of these transactions could be rolled back by initiating the Revert Record command. Saving a transaction, however, is less explicit; any action that causes FileMaker to move away from the current record would implicitly attempt to save the transaction. Clicking on a non-interactive background element of a layout would also implicitly attempt to save the record.

Recognizing this behavior, developers created formal patterns to impose structure and regularity around the use of database transactions. Because related records are included in the transaction, patterns arose where an unlimited number of records could be created, modified, and deleted by manipulating record keys and configuring special transaction-focused layouts. These transactions relied on the Relationships Graph, Layouts, and various Script Steps provided in FileMaker Pro for managing transactions.

Script Steps

FileMaker Pro provides several script steps for interacting with transactions. The three primary steps are:

Open Record/Request

Commit Record/Request

Revert Record/Request

The names of these steps show you, historically, they have been based on the current record. In addition to these three, two other steps are critical to the reliable operation of transactions in FileMaker Pro.

Allow User Abort

Set Error Capture

These two steps allow you to better control whether or not a transaction is saved (committed) or discarded (reverted) through user interaction or manually if scripted modifications fail (for example due to a validation error) and determine whether our transaction operations were successful or not.

• Open Record/Request
  • This step attempts to lock the current record for editing by the current session. If the lock can be obtained a transaction is initiated; if not an error code is written to the LastError flag. This step is optional to the operation of transactions; any attempt to modify a record or related record will implicitly attempt to obtain a lock for the record before allowing the modification.
• Commit Record/Request
  • This step attempts to save all modifications to the database that have been performed during the transaction. If any modification fails to be written, this operation will be rolled back and an error code will be set in the LastError flag.
• Revert Record/Request
  • This step will roll back any modifications to the database that have been performed during the transaction.
• Allow User Abort
  • This step controls whether the script can be canceled by user interaction. While not explicitly about transactions, the Allow User Abort script step can prevent users from inadvertently exiting a script, leaving a transaction open.
• Set Error Capture
  • This step works in concert with the Allow User Abort script step. Rather than displaying error messages to the user, errors are silently written to the LastError flag and read using the Get ( LastError ) function. By testing for this LastError value after attempting a database operation you can decide whether to attempt to Commit the transaction or Revert it.

Note that any operation that causes the current record to lose focus will result in the database engine attempting to save the transaction. If the transaction can not be saved due to an error, the operation that initiated the save attempt will fail and an error code will be written to the LastError flag. The practice of setting the ErrorCaptureState flag to ON and testing for the presence of a value in the LastError flag is known as “Error Trapping.”

Transaction Scope

Transaction Scope is another important concept when working with transactions. Transaction Scope defines the conditions in which a database operation is included or excluded from the transaction. The scope of a transaction can be inferred to be the current record in the window where the transaction was initiated. Any database modifications to records outside that window are not included in that transaction but may be included in a separate transaction. That is, each open window can have its own transaction independent of the transactions in other windows. Committing or Reverting a transaction in one window will have no effect on open transactions in other windows. However, a record can only be modified in a single transaction; attempting to modify a record in two different windows will result in a record-locking error.

What’s New in 19.6

With an understanding of how transactions have historically worked in FileMaker Pro, you can look at the changes made in FileMaker 19.6. On the surface, FileMaker 19.6 includes additional functionality that makes working with transactions more explicit, extends the number of script steps that can be included in a transaction, and improves the level of Error Trapping available considerably. Where some transaction functionality in prior versions relied heavily on patterns created by individual developers, the new functionality supports their operation more explicitly and makes them more accessible to developers who haven’t used them in the past.

How the new Transactions Work

Transactions in 19.6 work similarly to those in prior releases yet add new steps to open and save the transaction.

New Script Steps and Functions

• Open Transaction
  • Open Transaction is a new Control step in the FileMaker scripting language. By invoking this step, FileMaker enters a new transactional state for the current window. Within this control block, most record operations (see exceptions) become part of the transaction and can be reversed if the scripting engine encounters an error.
  • Transactions can not be nested; while a Transaction is open the script execution engine will ignore any subsequent Open Transaction steps it encounters.
• Commit Transaction
  • Commit Transaction is a new Control step that is paired with the Open Transaction script step and signifies the end of the transaction control block. If no errors were encountered during processing, all record changes that occurred during the transaction will be written to the database. If the script execution engine encountered an error during the transaction, the transaction will be reverted and script execution will jump to the step immediately after this one.
• Revert Transaction
  • Revert Transaction is a new Control step that allows developers to revert a transaction manually. While certain errors will prompt the database engine to automatically revert the transaction, sometimes the transaction will need to be reverted due to errors defined according to business logic. The Revert Transaction step can only be invoked within the script that issued the Open Transaction step; it is ignored by the script execution engine if encountered in any subscripts.

Claris has also provided three new options for the Get () function:

• Get ( LastErrorDetail )
  • This is an existing function formerly called Get ( LastExternalErrorDetail ) that has been extended to work with the Transaction script steps. It returns more detailed information about the error encountered during the script execution if any. The first value is the name of the script that was executing when the error occurred. The second value is the name of the script step that threw the error. The final value is the line number of the script step that threw the error. If there was no error then this function returns an empty value. If the Transaction is aborted due via the Revert Transaction command the script step name and line number will be those of that Revert Transaction command. Example:

• Get ( LastErrorLocation )
  • This function is similar to the Get ( LastErrorDetail ) function in that it returns three values indicating the script name, script step, and line number where the error occurred. The principle difference is that this function can be used in contexts other than with Transactions. If no error occurred (as indicated by the value returned by the Get ( LastError ) function) this function will return an empty value. If an error occurred within the Transaction block this function will return the name of the script, the script step “Commit Transaction” and the line number of the Commit Transaction script step.
• Get ( TransactionOpenState )
  • This function returns a 0 if no Transaction is currently open, and a 1 if a Transaction has been opened. While this function will indicate whether or not a Transaction has been opened, it does not indicate whether any records have been modified as part of that Transaction. For that, the existing Get ( RecordOpenCount ) function will return the number of records open in the current window, with some caveats (see Transaction Scope below).

Benefits of the new Transactions model

Given that transactions have worked reliably since the release of FileMaker 7, it’s easy to question if there’s anything to gain from using the new transaction script steps in FileMaker 19.6. If you have working transaction code in your systems then there is generally no need to change it. If you’re starting a new project, though, or in the midst of development, you might find the new transaction benefits compelling.

Speed

The new transactions script steps are considerably faster than previous methods. In a test of record creation using the legacy and new methods, the new method was 31% faster. While not as dramatic, speed improvements were found with record updates and deletions too.

Simplicity

Complexity is the bane of systems design. Complexity makes systems difficult to maintain, prone to error, and increases the time it takes to bring new developers on board a project. With FileMaker development, complexity often leads to degraded performance as well. In the past you needed to trade off complexity for data integrity. The new transaction model greatly reduces the need for that trade-off.

• Relationships Graph
  • No single area in FileMaker development lends itself as readily to complexity as the Relationships Graph. Particularly in larger systems, or systems that have been in production for years, updates to those systems have typically resulted in the proliferation of table occurrences as features are added, functionality is changed, data schema has evolved, and developers have adopted the heuristic that “adding new” breaks fewer things than “changing existing” does. To an already complex graph, the legacy Transactions method typically added a whole new set of table occurrences to allow scripts to address all of the data tables from the transaction context. In some scenarios, such as when a script needs to access multiple records in the same table as part of the transaction, multiple copies of the table occurrences may need to appear on the Relationships Graph. With the new Transactions model, none of this is necessary in the vast majority of cases. Record operations can be performed using existing layouts bound to existing table occurrences.
• Layouts
  • Legacy Transaction methods typically used one or more special layouts for transactional processing. In particular, these layouts were required if you wanted to include any Delete Record commands within the scope of a Transaction. To do so, in addition to the table occurrences required on the Relationships Graph, you also needed to add a portal object for each table in the system from which you might delete records. In addition, each of these portals needs to be named in such a way that your script can reliably target the correct portal before issuing the Delete Portal Row command. In contrast, the new Transaction model allows you to go to any layout based on that table’s context, locate the record in question, and issue a Delete Record command to remove the record.
• Scripting
  • Scripts using the new Transactions model can be simpler to write as well. Comparing the example above of deleting a record using the legacy and new Transaction models, the scripting required to accomplish that task using both models really highlights the benefits of simpler scripting. Compare the scripting required with the new method
  • to that of the legacy method

More Flexibility

The legacy transaction model in FileMaker 19.5 and earlier allowed you to modify and delete multiple records, all with the ability to revert back if necessary. Because the transaction was limited in scope to the current record in the current window, developers needed to get creative to implement a flexible mechanism for transaction record processing. One limitation of the legacy model exists around record creation. While multiple records could be created via relationships, if the relationship key was modified to access a different record you no longer had access to any records previously created.

The new transactional model in FileMaker 19.6 removes this limitation by expanding the scope to the current window. This gives developers the ability to work with multiple records in a single table or across tables, delete records without specially configured layouts, and access any records previously created within the transaction. The new model allows for more script steps to act on the newly created records, including Replace Record Contents among others. With the new model, far more record modification script steps are available for use within a transaction that can be rolled back at any point.

Prior to FileMaker 19.6, the developer needed to ensure the current record was not inadvertently committed or reverted. With the new transaction model, the developer must explicitly commit or revert the transaction so fewer actions will lead to unexpected transactions. When a transaction is aborted, whether when the scripting engine encounters an error or the Revert Transaction script step is invoked, script execution immediately jumps to the Commit Transaction script step and any steps after the error are skipped. This enhances script readability and reduces the need to guard script steps against execution after encountering an error.

Continue Reading: Transaction in FileMaker 19.6: Part 2

If you’d like to learn more about using transactions, check out Transactions in Claris FileMaker 19.6 with Corn Walker.