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.
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.
- Gather the primary keys of the records you want to edit.
- Go to the starting context. Create a new record.
- Add any logging information you might wish. Here’s what I might do:
- Describe the type of transaction: “Editing”.
- Identify the table we’re going to work on. “OrderLineItems”.
- Place the list of primary keys into a field.
- Enter the Start Time.
- In a loop, set the value of the OrderItemID field creating the relationship between this transaction and the one OrderItem record.
- Open the record. Check for an error
- 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:
- Gather the Parent record Primary Key (in my example, the Order record primary key)
- Go to the starting context. Create a new record.
- Add any logging information you might wish. Here’s what I might do:
- Describe the type of transaction: “Editing”.
- Identify the table we’re going to work on. “OrderLineItems”.
- Place the list of primary keys into a field.
- Enter the Start Time.
- 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.
- In a loop, set update the record. This opens and then sets the record with the new value.
- 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.
I noticed that when I create a record in a portal and want to get the sum (related_table::field) it is not able to get that value. I have to first commit the record and then I am able to get the Sum (field) of a related table. Is this normal behaviour? Kind regards,
Baran
Howdy.
Yes. That is correct, and not really a bug. In a transactional state, the records are created but not committed to the database file, so the calc fields don’t get triggered to update themselves. Also the transaction process is meant to be sort of a complete process: not one you’d interrupt to see what’s going on with the records. All the records in the set should be created, committed, and then be available for viewing.
If you really need to get the sum of the related records, I’d recommend storing the value in a variable and updating that variable sum each pass through a transaction record.
I hope that helps!