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