Transactionsby Todd Geist
Although it is not talked about as much as it should be, FileMaker does indeed support database transactions. Thank goodness because sometimes you have to use them. This module contains all the code you need to start coding using transactions. The modules uses techniques that have been used and tested for almost a decade. They are rock solid.
If you want more detail than whats covered below, you can about them over at geistinteractive.com.
The key trick is this. As long as you make all your edits through relationships, FileMaker will save all of those edits in a single commit. If any edit fails, they all do. There is no in between state where some edits go through and some do not.
The module provides a Transactions table to use as a sort of Super Parent or master record. As long as you make all your edits in the context of this master record, they will all be batched together when you commit this super parent record.
First you have to prepare the Graph and your Transactions layout. Create relationships between the “Transactions” table and all the tables you want to edit in your transaction. This is often done by using a global key in the “transactions” table to point at the given record you want to edit.
You create records using relationship set with Auto-Create related records on. You either navigate to the last row of a portal that uses that relationship, or use an empty key pointing at the primary key in your target table. That second method does not require a portal.
The only way to delete a record through a relationship is by using a portal. You “go to portal” row that has the record in it you want to delete, and use the script step “Delete Portal Row” to delete the record.
Scripting a transaction
You start by having your script call the script “Start Transaction”. That create the new transaction record and prepares it for use as the super parent. Next you set your relationship to point at the records you want to edit, create, or delete. Then you make all those changes, being careful not to commit the record, and trap for errors. Finally you can End Transaction and give it any errors that may have occurred. Thats it!
5/15/2013 – fixed a bug in the “End Transaction” Script
9/17/2013 – added better error trapping control to End Transaction
Here is the download.