FileMaker Transactions

FileMaker Transactions

Although it isn’t talked about much, FileMaker does have solid support for database transactions. You can write scripts with full commit and rollback support, just like MySQL and most other Relational Database Management Systems (RDBMS). This is a Very Good Thing. Without this support it would be very difficult to build robust multi-user apps with the FileMaker Platform.

FileMaker Transactions are not explicit. There is no “Start Transaction” script step. Rather, they are implicit under certain conditions. If you know how to set those conditions up, your FileMaker script will use database transactions to save your changes to the database. The result is much more robust, and scalable solution.

Anytime you need to edit more than one record with a script, you need FileMaker Transactions!

Why do you need FileMaker Transactions?

I went into detail in another article about the harsh realities of building multi-user, networked apps, but the short answer is that without them you can’t be certain about what happens when a FileMaker script runs. A networked multi-user application can run into many problems that stem from its very nature. It’s an application where multiple users may be fighting over the same records, with rules that may prevent editing, over a network that may die. To make matters worse, the operating system may force the script to abort, or the computer may crash, or the application itself may crash.

Transactions can’t stop these bad things from happening, but what they can do is make sure that the integrity of your database is not adversely affected when they do. They do this by saving all the edits you want to make to the database into batch, and then committing that batch in single atomic operation. That operation is guaranteed to either completely fail or completely succeed. Partial success is not possible.

Complete success or complete failure are both acceptable outcomes. They both provide complete certainty about the state of your data. There is no need to guess or to try to determine what changes succeeded and what didn’t. The data in your database moves from one known state to another known state. Database transactions make it impossible for some in between state to occur.

History of FileMaker Transactions

FileMaker has had good support for transactions since FileMaker 7. If you could look back in time and see the press releases for FileMaker 7, you won’t find any mention of “Transactions”. It wasn’t talked about as a feature at all. But some of the new behaviors hinted that some major changes had been made under the hood effecting how FileMaker saved data to the disk.

The behavior change that tipped me off was with Revert Record. Starting in FileMaker 7, Revert Record, reverted changes to all the edited related records. This was a major change. Prior to 7, the most records you could have open at any given time was three. If you edited a record in a portal, you locked open that record, the parent record, and the first record in the portal. Therefore you could only ever undo the changes made to three records. But with FileMaker 7 you could have unlimited open related records, and Revert Record would revert all of them.

I suspected that the most likely way for FileMaker to do this would be through the use of true database transactions. At DevCon, I asked Sean Flisakowksi, who was then one of the lead engineers at FileMaker, if this was true. He confirmed that it was. FileMaker’s new database engine was indeed using true database transactions to save data to the disk.

Using FileMaker Transactions

The key is to make changes to your database through relationships. Changes made to related records are cached until the parent record is committed. At any time, you can use “Revert Record” to rollback the changes you have made to the parent record and all its related records. When you commit that record, all the changes you made to all the related records will be committed at once.

You can create records through relationships when the “Allow creation of related records…” check box is checked. Many FileMaker users know about using a portal setup on such a relationship to create new records. You can use a script to Go To Portal Row Last and set fields on the other side of the relationship. But there is another way as well. You don’t need to use a portal as long as there are no related records in the target table yet. Setting a field through that relationship will create a new record as well. There are some very interesting things you can do with that little trick.

Editing records through a relationship is easy. All you need is relationship to the record you want to edit. If you don’t have a natural relationship to the record you want to edit, just create one. You can create a relationship to any record in a table if you have its primary key. I call these special purpose built relationships “ad hoc” relationships. I posted a Transactions module to Modular FileMaker that has my code for using up these “ad hoc” transactions.

You can delete records through a portal as well. You will need to make sure that “Allow deletion of portal records” is checked in the Portal setup. Use the “Go To Portal Row” script step to select the related record you want to delete. Then use the “Delete Portal Row” script step.

Operations that don’t use FileMaker Transactions

Batch edit operations, like replace and import, do not use FileMaker transactions. If something happens halfway through either of these operations, some of the changes will have been committed and some won’t. If a replace encounters a locked record it will not be able to edit it. You will get an error. But it will not tell you which record wasn’t edited.

Importing records does not use FileMaker Transactions

If your script does anything to commit the record before it gets to the end, you will break the transaction. You will want to avoid Script Steps that implicitly commit the record.  These include but are not limited to Go To Layout, Enter Find Mode, Close Window, and Go To Record.

Stay on one layout. Create the relationships you need to target the tables and records you need to. Do that and you won’t have any trouble keeping the records open until you are ready to commit them.

Caveats

FileMaker Transactions are very solid. But there are a couple of caveats. If the server crashes at just the right moment, a transaction may be damaged.  But as long as a the server doesn’t crash you can be confident that everything worked.

You may have to change the way you think about writing FileMaker scripts. Doing everything in the context of a single layout and under lying table occurrence does take some getting used to. Sometimes you will have to get creative. But I have yet to find a scenario that I couldn’t make into a single transaction.

Flexible, Scalable, Fault Tolerant

You can create, edit, and delete as many records as you want in a single FileMaker Transaction. You are only limited by the memory constraints of the machine that is executing the transaction safe script. You could create a million records, edit a million more, then commit everything in a single operation. Or revert the whole thing and the database will be left untouched.

Using transactions will make it easier for you to use techniques like de-normalizing and posting to speed up your database. They will reduce the number of unexplained issues you have. They will make your complex, multi-user, networked apps better able to survive record locking, crashes, and shaky networks. They may even help you sleep better at night. 🙂

FileMaker Transactions work on all FileMaker Clients including FileMaker Pro, FileMaker Go, WebDirect, Server Side Scripting, and Perform Script On Server.