FileMaker Transactions give us the complete tools we need to ensure records are completely created, edited, or deleted. We’ve seen in a previous post the idea behind it. Let’s now take up the idea of starting a FileMaker transaction. Our Karbon framework goes to extensive lengths to start the transaction, but your process can be pretty simple. Check out Karbon or look at the transactions module on modularfilemaker.org. Both are great examples of the entire transaction process.
It’s important to get started on the right foot before a transaction begins. We need to begin the transaction from somewhere.
In Karbon and in the Transactions module, we use a DBTransactions table.
It contains fields for logging the transaction’s data, start and end times as well as any information about the success or failure. And it contains fields which are used for the relationships to tables in which records will be created or edited or deleted.
If you use the DBTransactions table, it is the starting context for every transaction in your entire custom app. It’s helpful to have all of them start at one place.
But your starting context can be any context that makes sense. If the use case is to copy Estimates and estimate line items to order and order line items tables, you could use the Estimates context as my starting point. If you need to copy estimate #4392 to an Order record, your starting point is Estimate #4392.
The starting context contains fields that are used in relationships to the tables where data will be added or edited. All the relationships are set to “Allow Creation of records in this table via the relationship”.
In the starting context table I’ve got two fields, “TR_OrderId” and “TR_OrderItemId”. These fields are related to the primary key fields of the respective tables. We use the primary key fields because the relationship needs to be unique per record we will create.
Collect the data
Of course it’s always good to collect the data that will be added or edited. In the example we are following (copying estimate to order), we could collect all the data, both the estimate record information and each estimate line item data as a JSON object and store that somewhere temporarily. This gives us confidence that the data we have stored is correctly synced with itself. It is one complete object and one complete record. Once it is stored, changes can’t be made to individual fields in this one estimate record.
In our current example, if the starting context is indeed the Estimate record, then we don’t really have to collect the data since it’s already in the current record.
Validate and take ownership
Additionally we might want to validate the data. We want to make sure there is actually data to process and that there’s no errors in the data.
Also, we want to take ownership of the vital records. I don’t want anyone to edit the Estimate record and EstimateLineItem records while I’m copying its data to the Order table, so my process needs to own the record (lock it) during the process. If we’re editing records, it is possible to lock each of those in the starting process. So we open the Estimate and individual line items records.
Call a start transactions script
In Todd’s post about Transactions long ago, he states that there is no ‘start transactions’ script step that we can run. But we can put all of the starting tasks into a subscript and call this at the start. Our Karbon framework contains a script called “Start Transaction”. This file set and the transactions at modularfilemaker.org are good models for the script.
Any additional tasks
If you’re using the DBTransactions (or something similar) you might find it useful to create a new record for every transaction process (the entire process: creating all records in the order and order line item table). You can record the binary result of the process: did the transaction go through or was it rolled back.
The process to start a FileMaker transaction is fairly straightforward. Whatever the details, this step sets up a likely successful transaction. The process gets all the ducks in a row before the transaction takes place.
In the next post, we’ll talk about the transaction process itself.