Transactions Without Portals

Transactions Without Portals

18/09/2015 | Jan Zelenka

SQL Transactions

First, let’s have a quick overview of what SQL Transactions are.

In a standard SQL based database system a Transaction is an Atomic Data Modification. This means that a Transaction is a guarantee of either all your changes being stored in the database, either none of them. Typically you start a Transaction by the SQL command BEGIN TRANSACTION, then perform modifications to your data and finally, if everything adds up correctly, you execute COMMIT TRANSACTION or, if thing didn’t go as planned, ROLLBACK TRANSACTION.

Semi-transactional FileMaker

In FileMaker we don’t have this luxury. There is a limited Transaction support but it’s far from the comfort SQL systems offer. We can modify data in related tables and as long as we stay in the same context, we can Commit or Revert changes. But once the context changes, everything gets automatically committed. And when it comes to working with related arrays of records, to be able to do this, the solution has to use a layout with a portal present and simulate user interaction with this portal.

Transactions Without Portals

Or has it? As it turns out, not really!

We were able to come up with a technique that allows modification of any amount of related records without using a portal.

The Principle

The idea is actually rather simple. When woking in a context of a certain Table Occurence, use a global field in that table and link it with a unique ID in a table you want to modify transactionally. Every time the Global Field gets set to a value of existing ID in the related table, that record becomes available through the relation and can be modified. Then when the value in global foreign key is changed, another record is available for modifications.

At the end, when the Commit command gets executed, all the changes to all the related records we modified get saved together. Likewise, upon Revert, all the changes get dropped and the database is safely in the state it was before we started the modifications.

Data Relationship Diagram

As an example let’s take Article and Order tables and demonstrate transactional modifications of Orders from within the Article context.

The following relationship is defined between table occurrences we will work with:

Article DRD

_gORDER~edit is a Global Field and is linked with a unique ID. The second match line makes sure that when a new ORDER record is created, it is automatically linked with the article. Generally only the first matching line is required to make the transactions work.

Creating Records

To create a new record, just make sure ARTICLE::_gOrder~edit contains no value, and set any field in ARTICLE_ORDER~edit to a value of your choice. To create another record, set ARTICLE::_gOrder~edit again to “” and repeat. Once you are finished, either Commit, either Revert Changes.

Note: When you create a record like this and break the link, even though FileMaker holds the created record somewhere in the memory, there is no way to get back to it and modify again. If you assign ARTICLE::_gORDER~edit a value of an ID from a record created earlier inside the transaction, FileMaker will not establish the link. If you then modify any of the related fields, filemaker creates a new record with the same ID and later fails when you try to Commit your changes.

Modifying Existing Records

If you set ARTICLE::_gORDER~edit to a value of an existing record, this record will become available through the relationship for editting. The same principle as with record creation applies, except you can always go back to a previously edited record, it will contain the latest values set by your process and these values will be visible to all other instances of this record within your current context.

Summary

As you can imagine, both of the above approaches can be combined during one transaction. You can even design multiple relationships this way and make modifications to all of them within one transaction. In fact, you can use just one Global Field to link to all different related tables but for the sake of clarity and minimising the danger of creating buggy solutions I would advice against doing this.

Happy Transactions!

Download the Demo File

Our Naming Conventions

Jan Zelenka
jan-zelenka@clickworks.be
No Comments

Sorry, the comment form is closed at this time.