A big part of our work as FileMaker developers is to design workflows that create or edit or delete records. If your scripted process requires that it completes the changes (adding or editing or deleting) to multiple records, then a database transaction is required. If a discrete entity (an invoice and invoice line items, for example) needs to be completely changed, then a database transaction is required. Let’s take a look at how database transaction works so that we can have confidence in FileMaker data changes.

An example to consider

Let’s imagine a custom app for a printing company. They create estimates with estimate lines.

The business workflow requires an estimate and its lines be copied into the order and order line items tables respectively. Here would be a typical script we might write.

There are issues with this script. Take a moment to study it. Based on what I’ve written above, what problems do you see?

I’ll wait. 🙂

The biggest problem is that I’m committing the new order record and the new order line items records individually. Here’s the sequence of events:

  1. I go to the Order Table and Create a record
  2. I go to the Order Line items table. In this action, the Order record gets committed
  3. I create a new record in Order Line Items table.
  4. After the first iteration of the loop, I create a new record. That commits the previously-created line item record.

We can see this commit after commit after commit happening. In the Data Viewer, Get(RecordOpenCount) shows 1. There’s only one record open. The previous records have been saved to the file.

If, while the script is running, the power goes out or the network collapses, some of the order line items records will have been created; others won’t.

That’s a problem, right?

The solution

FileMaker is a transactional database platform. A transaction is the complete process of getting records created or edited or deleted. The process is set up so that all records get saved to the file after creating or editing. The process is also set up to rollback changes made to records. The transactional model gives us peace of mind to have confidence in our FileMaker data

Commit all records

Your goal as a FileMaker developer is to provide a trusted workflow that keeps a user’s data intact and complete. Transactions ensure that all changes you made to records in the database get committed and saved to the database at once.

Rollback records

If there’s an error anywhere–power loss of incomplete data or record ownership issues–FileMaker Transactions rollback the data to their previous state. The changes (additions or edits or deletes) made against records, using a transactional model, do not get saved. The Pre-saved data is kept.

Transaction steps

The complete steps of a transaction are as follows:

  1. Start the transaction
  2. Create or edit or delete records
  3. End the Transaction

We can do all of these in FileMaker, and none of them are that difficult that even a new-to-FileMaker person cannot handle them. I’ll briefly explain these and then we’ll look at these in more detail in further posts.

Start the transaction

To perform the transaction, we need to start it. We get the correct context to the front, we get up the data to be added, and just get ready. The start step can include any of the following:

  1. Go to the proper transaction layout
  2. Validate the data to be added or edited.
  3. Create or open the transaction record. Record any transaction log information.
  4. Take ownership of records.

No matter the steps we take here, the last one is to continue or stop the transaction process if something’s not right.

Change multiple records

Once the transaction has started and we can continue, we add the records, edit the records, or delete the records.  This is the easy part. It’s what we script for every day. We’ll talk through this in detail in an upcoming post or tw.

End the transaction

In the transaction’s ending we:

  1. Try to commit the record changes made
  2. Check to see if there’s any errors
  3. Revert the records if there’s any errors
  4. Optional: Clean up. Record the success (or failure) and any other information about the transaction.

Transaction structure

There’s a simple structure to FileMaker Transactions. It requires just a few things:

  1. A Starting context
  2. A relationship to table occurrences in which records will be added. This relationship is set to “Allow Creation of records in this table via the relationship”.
  3. One or more scripts that control the record creation or editing or deletion and prevent any commit of records accidentally.

Have Confidence in your FileMaker data

Okay. This is enough for now. It’s a process, and one that deserves a through discussion. We’ll talk in the next few posts about the three steps: starting the transaction, doing the transaction, and ending the transaction. We’ll talk conceptual and practical. The whole point is to have confidence in our FileMaker data, to make sure that all of the changes get made or none of it. This topic merits more details. So we’ll talk through it here in upcoming posts. Stay tuned.