FileMaker transactions are essential to a well-functioning system. I haven’t stated it for awhile, so I’ll do now. If a scripted process changes (creates, edits, deletes) many records at once, a transaction is required. If a discrete entity (such as invoice and invoice line items) needs to be added or changed in some way, a database transaction is required. We’ve looked at how to do with with portals, but we can do it another way too: FileMaker transactions without portals.

Portals, as we saw in the previous post, are good for debugging and viewing, but they’re un necessary. Chris Irvine, at scalefm.com said something to a group of us chatting late-evening about transactions one day (that’s what I do in my evenings: Talk FileMaker) He said “I avoid using layout objects to do this transaction work”. That’s an interesting idea. Layout objects are user-facing things. Scripted processes should not require any layout objects to do their work. FileMaker is transactional, and through some magic, we can perform transactions from a blank layout. Let’s take a look at how to accomplish FileMaker transactions without portals.

SIDE NOTE: Chris Irvine is a smart FileMaker developer. Be sure you check out his blog posts and his speaking times at FileMaker DevCon.

Start at the very beginning

We’re back at the starting context. In our case, it’s DBTransactions layout based on that same named table occurrence. Here’s what we’ve got.

• We are on the same DBTransactions layout.
• The data was gathered and stored as a JSON object in a field.
• There’s a relationship from DBTransactions and Order, and DBTransactions and OrderItem tables. They’re set to Allow creation of records via relationship”.
• The relationship is created using a “orderID” and the primary key of the Order table. It’s a similar set up for OrderItem. These two fields in DBTransactions are visible on the layout (though they don’t have to be).

And we are ready.

Here’s the thing

The key to this transaction process is that we’re doing it the same way as with portals. We are creating records through the relationship. That’s all.  We do not traverse a portal to create or edit records. Instead we just set fields across the relationship. We can create the order record and all of the order line items records through their respective relationships without the need for a portal. Let’s pause a second and consider that for a moment. Is your mind blown? (Mine was the first time I saw this happening).

Oh. Another thing

Let’s talk about the relationship I’ve got set up first and establish some ideas about it.

You can see in the picture below my relationship to Order is set up from a field called OrderId and the primary key of the Order table.

That’s not normal. Usually we go from primary key to foreign key. But this relationship is essential to the transaction process. We want one unique record across the relevant relationship each time we’re set to create a record in that table. In a normal primary key to foreign key relationship setup, we’d have multiple related records (as the portal technique shows). We can’t have that. We tap into the power of the Magic key method to set a relationship to each unique record as it’s created. Check out Kevin Frank’s article on the magic key.

FileMaker transactions without portals

Here are our script steps.

Step 1: Create the order record.

In this step we simply set fields in the Order table with the values from, in my case here, the JSON object.

Set Field [ Order::Date ; JSONGetElement (DBTransactions::Data ; “date”) ]

As soon as the first field is set in the Order table, the primary key of that record is generated, and it pops back to the DBTransactions OrderID. The relationship between DBTransactions and the Order record is valid. Setting additional Order fields can commence.

Since a record was just created, our open record count has gone from zero to two:

{  
   “OpenRecords”:{  
      “DBTransactions”: 1,
      “Order”: 1
   }
}

Step 2: Create first order line item record.

This first order line item record is created very much the same. We set the value of the Order Line Item::amount field with its corresponding value in the first element of the LineItems array. Notice the set up I do before actually setting the field:

And once this field has been set, that OrderLine record’s primary key pops back to The DBTransactions table. There’s a valid relationship to an Order Line Item Record.

And guess how many records are now open. There are three open.

{  
   “OpenRecords”:{  
      “DBTransactions”:1 ,
      “Order”: 1,
      “OrderItem”: 1
   }
}

Step 3: Create the 2nd and remaining Line Item records.

Here’s where FileMaker transactions without portals gets crazy, so let’s pause a moment and consider what happens next. When I grab the next line item from from the $lineItems (in my example) and I go to set the OrderItem::date field, what is going to happen?

Does the 2nd order line item get created?

BUZZZ!!!

The 2nd line item does not get created because we still have a relationship from DBTransactions to the first order line item record. Setting the OrderItem::amount field with the second order line item’s information from the $lineItems overwrites the first record’s data.

So how do we create a new record in OrderItem? We clear out the DBTransactions::orderItemID field.

Woo. Wait a minute! Won’t that commit the record?

It won’t. For some magic reason, FileMaker keeps that record in its memory. We can’t see that record anymore or access it it, but we can be confident that FileMaker has that record open and uncommitted.

Now that the OrderItemId field is cleared, we can create the 2nd record across the relationship. At this point there are four open records:

{  
   “OpenRecords”:{  
      “DBTransactions”: 1,
      “Order”: 1,
      “OrderItem”: 2
   }
}

And we keep doing this until all records have been created across the relationship. By the end of the process using my example, I have six records open:

{  
   “OpenRecords”:{  
      “DBTransactions”: 1,
      “Order”: 1,
      “OrderItem”: 4
   }
}

We keep these open until we end the transaction (coming in the next post).

Transact across the relationship

It’s pretty cool how FileMaker can keep all these records open and in memory when I’ve destroyed the relationship between my starting context and the records. I was blown away when I saw this happening. I swore you needed a portal to create records. But you don’t. You can do FileMaker transactions without portals.

There are a few downsides: For one, you can’t see the records created. You know they were created. Open, but you can’t see them. Secondly. um. I can’t think of another downside. This technique eliminates the UI objects (the portals) to create the records. It’s consistent with our other actions: We can set fields without the field on the layout.

I’d encourage you to try this method. Perform FileMaker transactions without portals. Skip the objects and all that setup and use what FileMaker can do to transact the records. Karbon works this way; Karbon transacts records a lot. So give it a try.