How Transactional is the FileMaker Data API?

I’m always excited each time the FileMaker Platform gets new capabilities. It isn’t just the new features on their own that make things interesting, but what happens to the platform as a whole which provides for some interesting and inspiring innovations. In this case, it is a new way to do transactional record editing in FileMaker. This is the first in a multi-part series on this topic.

Let’s start at the beginning.

Way back before FileMaker version 7.0

It used to be that as soon you put the cursor into a field, you would be the owner of the record. And you would be especially popular if you left your cursor in a field and went to lunch. When you returned from lunch you might have some strange looks coming your way. It is because you had locked that record and no one else would have been able to make changes to it until you returned from your long, leisurely lunch break.

Fast forward to present day

Since FileMaker v7.0, just putting your cursor in a field no longer locks the record. You actually have to start editing the record to get a lock on it. But a lock is still a lock; if you pause to look up the spelling of the name, you are keeping that record locked for every precious millisecond. In this screenshot, not only are you keeping the “U2 Tour” record locked but you are also locking users from making any changes to any of the tasks records as well.

Thus came training users to hit the Enter key or click anywhere outside a field to commit their changes, and unlock the record. They quickly learn how FileMaker saves the data when you are done with your edits. And might be one of the first things you learned when you started with FileMaker, seeing as there is no “Save” menu option.

Pessimistic record locking

This model of locking the records for users is called pessimistic record locking. And that is the way FileMaker has been for years.

However, there is a new kid on the block: the FileMaker Data API. It follows many of the old rules about record locking, except it’s new, and “young” — and the young by nature tend to be optimistic!

Optimistic record locking

The FileMaker Data API was officially released under the FileMaker 17 Platform. Now that it is officially released, and pricing is established, people are going to start using the Data API more and discover some really creative uses for it.

That the Data API is transactional, is probably no surprise for some. But it was a surprise for me and here is why: it works under the optimistic record locking model. This means that it updates all the records with regards to the data (JSON) submitted. If any one of the related records are empty or are in use by another user then nothing gets updated. It’s all or nothing.

Darren Burgess and I have been exploring the transactional nature of the API and have created a Data API Transactions demo file (User: admin; Password : admin) to help you see this functionality in action. Download the file and host it on FileMaker Server 17. If you need a primer on the Data API, check out Using Rest and cURL with FileMaker 17’s Data API, which also includes a nice demo file.

Here is the relationship graph for the Data API Transactions demo — a really simple example of PROJECTS and TASKS:

Creating Data Transactionally

If you want to create a Project record with all its related children, so long as the JSON payload is valid and your privilege set allows you to create records, then you are all set. Sending the correct JSON payload will create the parent and child records all in a single step. Here is an example of the JSON required to create this one parent record and all the related records using the Data API:

{
 "fieldData": {
  "PROJECT": "U2",
  "ROAD_MANAGER": "Tony Menanno"
},
  "portalData": {
     "road_manager_tasks": [
        { "TASK::DATE": "5/26/2018", "TASK::DESCRIPTION": "Review Contract" },
        { "TASK::DATE": "5/31/2018", "TASK::DESCRIPTION": "Plan Travel" },
        { "TASK::DATE": "6/1/2018", "TASK::DESCRIPTION": "Book Hotel" },
        { "TASK::DATE": "6/10/2018", "TASK::DESCRIPTION": "Rehearsal Schedule" }
     ]
 }
}

For this transaction of parent record and the related child records to work, you’ll need to have the “Allow creation of records in this table via this relationship” option enabled.

So, this allows us to to create the parent and any number of child records all wrapped up under one transaction. This is a HUGE DEAL! If, for example, you had validation for any of these records and validation failed, then NONE of what you sent gets put in. It’s all or nothing.

NOTE: Sure, we can create the same thing completely in FileMaker, but I am focusing on the Data API here and what has now become possible with the Data API and the subtle differences.

Editing Data Transactionally

Now we’ll look at how we can edit data transactionally, meaning that if I make multiple changes and submit the JSON, those changes only get committed to the database if they can all go in. Let’s say all the tasks require that a date be present. If you remove the date and send the JSON, then it will fail. Alternatively, one could capture it locally, even before building the JSON, but as a fail-safe, it’s always a good idea to have the validation in the actual table.

You can use the demo file to see this in action. Click the 2A Edit Record button, then modify the values in the JSON payload that displays in the popover. Just modify the values in parent and child records, then click Edit Record.

You should see the record and its children update accordingly and you will get success JSON object in the result:

{
  "messages" :
  [
    {
      "code" : "0",
      "message" : "OK"
    }
  ],
  "response" :
  {
    "modId" : "20"
  }
}

Now try locking one of the child records. Click the Lock Child Record button. This will open the task records in a new window and start editing one of them, thus locking a child record. Notice now what happens when you try to edit the parent: the Data API request will fail and you get a 301 error in the JSON result. No changes will be made to the data. The same thing will happen if you try to delete the parent.

NOTE: Currently it looks like to create a record or edit a record (not related data in a portal), you don’t need a field to be on the layout. I wish we could just address the table occurrence of the table rather than have to give the portal an object name. And I think I know the reason for this is because, otherwise, you could have the same portal on the layout more than once…so you would need a way to distinguish them.

Why Bother?

You must be wondering now… If I can do this natively in FileMaker why go though all the trouble of packaging data up and submitting it through the Data API. I already have transactions working in FileMaker, and if I am editing a record I don’t end up conflicting with another user. Yes, I agree and… I still think it is worthwhile for some situations.

There are many projects that we work on where users have to access the system from any place in the world. And we are always striving to build solutions that are as performant as possible. This gives you options for that.

The other reason is that it gives a single point of control. By having that single point of control we can take advantage, and provide auditing. If the transaction is successful then we can reliably audit what was changed. To be clear, transactional editing via the FileMaker Data API has a lot of moving parts (as we’ll try to cover in future posts) and diverges somewhat from the traditional FileMaker spirit. So, you’ll want to evaluate on a case-by-case basis whether it provides enough benefit to justify the complexity.

Development choices are always about finding the right balance between elegance and complexity. Still, it’s our hope that you’ll find use cases for, and derive inspiration from, this exciting discovery.

More to come

In future blog post(s) we’ll plan to go into more details on techniques you can use to edit your data, transactionally, via the FileMaker Data API. Stay Tuned…as the Exciting Optimistic Record Locking Adventures continue!

Download the Demo File (User: admin; Password : admin)