The transactional method of working with records has one goal: to ensure all changes to records (additions, changes, or deletions) are done at once or not at all. We’ve talked about this before. The transactional method prevents some records in a discrete set (such as invoices and invoice line items) from being changed while others are left in a previous state. We’ve reviewed in the past how to create records in a transactional method (here and here). Now let’s turn our attention to the FileMaker transactions and editing records.

Review the Concepts

There are a few important points about the transactional process. If we keep these ideas in mind, we will design a workflow that works for FileMaker transactions editing and continues the trend of data confidence. 

The process needs to have ownership of the records to be changed. That means each record is open, and there are no errors in opening the records.

As the process changes records, FileMaker keeps those records open in the memory of the device running the transaction. If George is running a script that edits 15 records, those records’ changes will be on his computer only.

During the process, all commit attempts must be controlled. The process cannot go to another layout or allow the user to click outside a field. No commits can happen until all the records have been changed.

After all the records have been changed, the process tries to commit the records. 

  • If the commit is successful for each and every record, the all changes have been saved to the file.
  • If the commit throws an error for any one of the records, then all of the changes are reverted–that is, the changes are never saved to the file and instead discarded.

The use case for our consideration

As we take a look at these concepts in concrete detail, we need a use case. I chose to go with the statement: “The user wants to apply a discount to all individual line items” In this case: all the line items are assigned the same discount. It is important to do this all at once or not at all. And it is faster than applying the discount manually to each line.

Editing records

Our first step in preparation is to find the starting context. I’ve talked a lot about starting context in other posts, and the same information applies here. Karbon and DBTransactions uses a Transaction Log table as the start, and this is what I prefer. I want to log each transaction and what it is going to do. So let’s go over to the Transactions Log layout.

The starting context sans-portals
Portals in the starting context.

Editing the records

Now that we’re ready to edit the records transactionally, and, keeping the above concepts in mind, our script proceeds as follows:

One record at a time

Here’s what it looks like. Study this workflow diagram and see if you understand this. I’ll clarify a few things below.

Editing without a portal

If you’ve set up your FileMaker transactions editing process to work without a portal (and they can) then you need to follow these steps.

  1. Gather the primary keys of the records you want to edit.
  2. Go to the starting context. Create a new record.
  3. Add any logging information you might wish. Here’s what I might do:
    1. Describe the type of transaction: “Editing”.
    2. Identify the table we’re going to work on. “OrderLineItems”.
    3. Place the list of primary keys into a field.
    4. Enter the Start Time.
  4. In a loop, set the value of the OrderItemID field creating the relationship between this transaction and the one OrderItem record.
  5. Open the record. Check for an error
  6. Update the field. Check for an error.

Editing with a portal

If you’ve set up your FileMaker transactions editing process to work with a portal, then you need to follow these steps:

  1. Gather the Parent record Primary Key (in my example, the Order record primary key)
  2. Go to the starting context. Create a new record.
  3. Add any logging information you might wish. Here’s what I might do:
    1. Describe the type of transaction: “Editing”.
    2. Identify the table we’re going to work on. “OrderLineItems”.
    3. Place the list of primary keys into a field.
    4. Enter the Start Time.
  4. Set the Order primary key field with the value you gathered. This creates a relationship to the Order Item table and shows this order’s order Item table.
  5. In a loop, set update the record. This opens and then sets the record with the new value.
  6. Check for an error.

Check for errors

After each step in the above procedures, it is important to check for errors. We’re checking for errors each time we try to open any of the line items records and when we edit each record. If there’s any error in one of them, even if it’s the last one, we have to discontinue the process.

It seems extreme to discontinue and cancel the process when we get an error after either of the steps. It seems especially cruel to cancel if there’s an error on the last to-be-edited record. But remember what’s key about transactions: all of the records must be changed or none of them can be changed. If the last record has an error, then we can’t edit that one, so none of the other records can be edited.

Commit or revert

If we have edited all the records without incident or error, then all the records are committed. In our case, all the order line item get a discount applied to them and are updated in the file.

If there is an error and we need to discontinue the editing process, we have to revert the records.

We’ve talked about this before. The process is the same.

The FileMaker transactions editing process

The process of transactions, the main concepts, apply to all method of changing records. Editing FileMaker records transactionally is one way to change records, and it should be considered if it is vital all the records or none of them should be changed.

The process of actually transacting, of creating or editing or deleting records may seem like a complex concept, but once you understand the idea it’s actually rather simple. We create or edit or delete records from the starting context through the relationships we’ve created. Let’s take a look at the FileMaker transaction process with portals.

Total recall

Let’s remember what we set up as we started the transaction.

  1. We are on a starting context of some kind: either the Estimates table or the DBTransactions-kind of table. In the following explanation, we’ll be on the DBTransactions table, just as Karbon or the FileMaker transaction module in modularfilemaker.com.
  2. There is a relationship between the starting context record and Orders and OrderLineItems Tables set to “Auto Create”. We have named portals set up on the DBTransactions layout,
  3. We’ve collected the data in some manner Either an API request has returned a response or you’ve stored the entire Estimate and estimate line items data in a JSON object. Or the starting context is one that you can grab data from it and related tables.
  4. The current record is owned (thus opened) by the Start Transaction process. There were no errors in creating a new DBTransactions record or opening the Estimate record and its related ones.
    Any log information has been set if applicable.
  5. Oh and I’ve set up the Relationship to look like this. I’m going to store the ID of the Transaction in the order and Order Line table.

Simple & complex

The concept is simple and complex. Records can be created or edited or deleted across the relationship. This is the simple part.

It gets complex when we begin to create the one order and many order line items records in the respective related tables. There’s two ways to do it: in a portal for each relationship (we’ll discuss now) or without a portal (yes, this can be done, and we’ll discuss in the next post).

And now we’re ready to go. Let’s copy the estimate and its lines to Order and its lines.

In this section we’ll talk about creating records. There’s a separate discussion have when we are editing existing records and when we’re deleting records. The concept is the same for all three instances, just a few refined specifics for each.

Our transaction script continues like this (or a separate, “Do Transaction script”, begins):

Step one: create the order record

The first step of the FileMaker transaction process is to create the order record. Simple enough. Navigate to the ‘order’ portal. Go to the portal Row [last] and create that row’s data with data from the JSON object. Set the fields in the related table with data.

Some notes about this:

  • I have only a few fields in the portal. These few are simply to have a visual to confirm the record is created. Any fields will do. You might want to put one data field and any utility fields in the portal for debugging purposes.
  • Once the relationship has been created by setting data in the Portal Row[last], the rest of the record can be filled in with data from the estimate JSON.
  • The moment I create the order record, that record is open. In the Data viewer the Get(RecordOpenCount) is 2: The DBTransactions record and the Order record.

Step 2: Get the order ID

Since our goal in the FileMaker transaction process is to create an order with related order line items, we need to capture the primary key of the order we just created. Nothing’s simpler.

Step 3: Create the order line items

Next we create the order line items through the Order Line Items portal. We have captured the estimate line items previously in the JSON object ( in the “LineItems” attribute), and are ready to parse through it. We do the same thing:

  • Navigate to the Order Line items portal
  • Go to Portal Row[last]
  • Set all the fields in that row with estimate data from one estimate line item
  • Set the foreign key (ID_Order) with the primary key of the order we created above.
  • Continue this process until all data from all elements of the array have been set into the Order Line items table

Some notes:

  • As each order line item record is created, a new record is opened. After creating three line items records, five records are opened:
    • DBTransactions record
    • The Order Record
    • Three Order Line items records
  • We always go to the last row in the portal because it is the place to create a record. Even, as in the Order creation step above, only one record is to be created, we still should go to the Last row. Just keep the process consistent.

Commitment issues

Every time the FileMaker transaction process with portals creates a record in the object, one more record opens up. I’ve made it a point to highlight that fact. We want to keep these records open and we do not want to commit them until the entire record set, the order and all its order line items, have been created. So don’t commit or do anything that commits a record:

  • Do perform a find
  • Click outside a field
  • Commit Record script step

Set let the process run. Let the records build up in memory. And then, when all the records have been created, commit them together as we finish the transaction. Or revert them and NOT create them if there’s an issue somewhere.

FileMaker transaction process with portals

You don’t technically have to use the portals in the FileMaker transaction process. With a few changes, I can do all the work of creating records without a portal.

But portals are a great way for a developer to debug the transaction and watch the process. While running the script debugger, you can watch the records being created (but not committed).

I’m also storing the ID of the transaction in the order and order line item tables. So I can continue to view the records created via the transaction after the transaction has ended.

The FileMaker transaction process, ongoing

In the next post we’ll talk about how FileMaker transaction process without the portals. Stay tuned. It’s pretty cool.

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.

Starting context

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.

Schema

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”.

The DBTransactions starting context.

The Estimate starting context

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.

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.