Transactions

by Todd Geist

FileMaker Transactions

Although it is not talked about as much as it should be, FileMaker does indeed support database transactions.  Thank goodness because sometimes you have to use them.  This module contains all the code you need to start coding using transactions.  The modules uses techniques that have been used and tested for almost a decade.  They are rock solid.

If you want more detail than whats covered below, you can about them over at geistinteractive.com.

Basic Principles

The key trick is this. As long as you make all your edits through relationships, FileMaker will save all of those edits in a single commit.  If any edit fails, they all do.  There is no in between state where some edits go through and some do not.

The module provides a Transactions table to use as a sort of Super Parent or master record.  As long as you make all your edits in the context of this master record, they will all be batched together when you commit this super parent record.

First you have to prepare the Graph and your Transactions layout.  Create relationships between the “Transactions” table and all the tables you want to edit in your transaction.  This is often done by using a global key in the “transactions” table to point at the given record you want to edit.

You create records using relationship set with Auto-Create related records on. You either navigate to the last row of a portal that uses that relationship, or use an empty key pointing at the primary key in your target table.  That second method does not require a portal.

The only way to delete a record through a relationship is by using a portal.  You “go to portal” row that has the record in it you want to delete, and use the script step “Delete Portal Row” to delete the record.

Scripting a transaction

You start by having your script call  the script “Start Transaction”.  That create the new transaction record and prepares it for use as the super parent.  Next you set your relationship to point at the records you want to edit, create, or delete.  Then you make all those changes, being careful not to commit the record, and trap for errors.  Finally you can End Transaction and give it any errors that may have occurred.  Thats it!

History

5/15/2013 – fixed a bug in the “End Transaction” Script

9/17/2013 – added better error trapping control to End Transaction

Here is the download.

28 responses to “Transactions

  1. Simon Plint says:

    Todd,

    “Although it is talked” to “Although it is not talked”. We all knew what you meant 🙂

    Great great stuff with all of this. You get my vote for filemaker evangelist of the year.

    Regards,
    Simon.

  2. […] Modules” ( ModularFileMaker Modules ).  mFM Modules provide features like Navigation Bars, Transaction Frameworks, search utilities , SQL helpers, etc.  These are all below the level of application or solution. […]

  3. Scott says:

    Hi, Todd:

    Thanks for getting this going. I ran into a near the end of the “Start Transaction” script:

    #
    #
    #
    Set Field [Transactions::; $transactionLayout]
    Commit Records/Requests []
    Open Record/Request
    End If
    Set Field [Transactions::TransactionCounter[]; Transactions::TransactionCounter + 1]
    #

    • Todd Geist says:

      Hi Scott,

      I am not sure if you are reporting a bug or not. Your comment seems incomplete. Do you have a question?

      -Todd

      • Scott says:

        Hi, Todd:

        John Renfrew figured out what was occurring. I tried to point out that there as a “Field Missing” reference in that script, but the code editor stripped anything between brackets. I was lamenting the inability to “edit” my post…

        Thanks for responding so quickly with an updated file.

  4. john renfrew says:

    question should read..

    Set Field [Transactions::; $transactionLayout]

  5. john renfrew says:

    Interesting
    What the code editor here does is remove the angled brackets

    should actually be
    Set Field [Transactions::< Field Missing > ; $transactionLayout]

  6. john renfrew says:

    Todd
    can you expand a bit on the ‘or use an empty key pointing at the primary key in your target table’ above – or direct to some other clever article…. I have a demo working two layers deep (each child record may have one or more grandchildren) which I have working with multiple got to last portal row script steps but not sure how this might be implemented on the second level….

    • Josh Ormond says:

      John,
      I believe it references FM’s obligatory record creation.

      For those that don’t already know ( which I know you do John )…If you use a Set Field [] on a related table where there is no “related record”, FileMaker creates the record. So an empty key has no related records, and Set Field [ TargetTable::FieldName ; Value ] will create a record and populate the key field to force the relationship. Something similar to the article Kevin Frank posted about “Magic Key”.

      The records are still bound by the same ‘Commit all at once’ transaction.

    • Todd Geist says:

      Hi John,

      There are two methods you can use to create a new record. Both require that the relationship have “create related record on”. The first is going to the last portal row ( the empty one ) and setting a field. This one is well known.

      The second is to make sure the primary key field is empty before setting the field. For this 2nd method to work you also need to make sure the right side key has an auto-enter calc or serial number set up. When the set field occurs and the related record is created the right side key is popped back across the relationship into that empty Primary ( or left side key ). This one is not so well known. And I think, used to be considered a bug. But that was long ago.

      Both techniques have their uses. The second one is nice because it lets you target a record by its primary key. You can locate the record by setting the Primary Key on the left, and you can create a new one by clearing the primary key and setting a field in the target record. It also doesn’t require a portal on the layout, to create a record. I use this method first, if I can. But sometimes you can’t use it. So then I go back to the first method.

      When can’t you use it? Here is where you have problems. Lets say you need to create two records with this method. You create the first record. Then you clear the key field and create the second. Then later on within that same transaction you need to get that first newly created record back, you can’t. It is still open, and uncommitted, but you can’t “reach it”. Its in this kind of limbo state, and won’t again be reachable until you commit the record. This only occurs with records created with in a single transaction. I just got dinged by this yesterday.

      It is often useful to bring each relationship back to one level deep. This gives you the most control, and is the easiest to reason about. So I tend always create relationships from my main transactions table to the target tables, even if in a normalized schema you may thing of them as being 2 or 3 tables away. You can always create a single step relationship to any table from the main transactions table.

      In ten years of using this method, I have only come across one or two scenarios that couldn’t be made into a transaction using these techniques. But sometimes you have to get pretty creative. 🙂

      Hope that helps

      Todd

      • Justin Close says:

        Todd,
        I started implementing the transactional method for a solution and ran across a question: how does one create multiple related child records? The original technique I am re-writing did the classic GoToLayout, New Record, Duplicate record. I get the feeling that this ‘magic key’ technique may be my answer for creating multiple related child records through the relationship. Since I only need to create subsequent child records (no editing of previous ones) I don’t believe that I will run into the issue you describe of ‘limbo’ child records.

        It does have the drawback of having to define common fields repeatedly, it seems. I.e. with ‘duplicate’ you get all fields copied and only have to update a few of them, but with this relationship technique you always get a blank record on the other side. I suppose you could define the relationship to use all the fields you want duplicated. 🙂

        Thanks for posting this module and the great articles on the transaction technique in general.

        — Justin

        • Todd Geist says:

          HI Justin,

          You can create as many related records as you want. Simply clear the Left Side key and start again. Works great!

          Yes it definitely takes longer then duplicating a record. But duplicating is not a transaction safe operation. You can’t revert it. So if you want or need to be transaction safe, you can’t use duplicate. 🙂

          Thanks

          Todd

  7. John Renfrew says:

    Extremely helpful Todd, thanks.
    I have to do the portal version as there are from 0 to 7 grandchild records, but I forgot to correctly build a new relationship to the second table rather then going ‘through’ the first.
    This is where I am grabbing information from multiple SQL tables behind a website someone else wrote, and need to make sure a whole order is completely transferred before commit.
    Time to plug in the module now I have it working.

  8. Dickie Chung says:

    If this “Transactions” module is not used, won’t Filemaker handle transactions properly with proper comit/rollback of records? If it does, what are the advantages of using this “Transactions” module? What could go wrong if I just do multiple edits, inserts or deletes with just one Commit Records/Requests command at the end of a script without using this “Transactions” module?

  9. Todd Geist says:

    Hi Dickie,

    FileMaker automatically commits records as the result of lots of common actions. For example if you want to create two records in a table, and you do it by using two New Record scripts steps in a row FileMaker will commit the first one before it creates the second one. Changing layouts and changing records are both common actions that commit all the open records before proceeding.

    IF you want to create edit, and delete more than on record in the context of a single transaction that you can commit or Rollback, you must do it through relationships. There is NO OTHER WAY to get multi-record transaction support in FileMaker.

    This module contains scripts and technique for doing it this way.

    Hope that answers your question.

    Todd

    • Dickie Chung says:

      Thank you very much for your elaboration. It helps very much. Is there any sample/demo that is available to help newbies like me understand how this Transactions Module is implemented?

      1. When I set the relationship between the Super Parent and the tables to be edited, should I use Cartesian Joint “X”? (There is no need to add in each table a foreign key to point to the Super Parent. Am I right?)
      2. If a Transaction Layout must be used, does it mean that I have to duplicate all my original layouts to add the Transaction as Super Parent on top for all the tables that I need to take actions of insert, edit or delete with transaction control? If so, is there anything that I should take care of when I switch from my original layout to the Transaction layout and then back to the original layout?

      • Todd Geist says:

        Sorry I don’t have any demo files available right now. This module is just one way of doing transactions with FileMaker. I use this a lot but not always. It’s more important to understand the basic concepts.

        If your scripts create, edit and delete, records through relationships then you can commit or rollback all the changes in one transaction.

        Thats the key. How you do that is less important.

        I will try to put together some demo files soon

        Hope that helps

        Todd

      • Todd Geist says:

        To answer your questions specifically

        1. You don’t need a Cartesian join. With the super parent method ( the one shown here ) you create a fields in the Super parent you use as the Primary Key pointing at all the tables in your solution.
        2. The super Parent method does require to switch layouts. And that may have side effects, like script triggers firing, default tabs popping back. So you will need to deal with that.

  10. Alejandro Banderas says:

    Just wanted to let you know that I was looking for this technique for a long time now.
    THANK YOU for taking the time to make the videos.

    If you ever come to Ecuador let me know. I will be glad to invite you some beers!

  11. Electon says:

    I’m a bit confused about the End Transaction script.
    Do I need to call it when an error occurs in the script I’m running and the script will revert entire transaction, or at the end of transaction maybe?
    Or do I need to take care of reverting the changes myself, then call the End Transaction?
    Also is there an example to construct the script parameter $errorLetVars and what it actually parses to?
    In short is the End Transaction part of the process or is it used for internal module errors?
    Thank you.

    • Todd Geist says:

      It is an essential part of the process. It handles tracking nesting transactions, checking for errors on commit, and reverting if necessary. If you look in the “End Transaction” Script, you should see the Revert Record script step in there. Remember you do need to pass any errors to End Transaction, so it will know that it needs to revert.

      Look in the Scripts themselves to see how $errorLetVars are constructed. The script steps are very well documented. If you need to learn more about Let Vars or Let Notation check out this link.

      http://filemakerstandards.org/pages/viewpage.action?pageId=5668879

      Todd

  12. Justin Close says:

    Hey Todd,
    I was working some more with this transaction technique and came up with a scenario I haven’t quite been able to figure out. I am trying to create a related record (transactionally) that is two tables occurrences away. I have been using portals up to this point for creating the records, but when I create a new record in Table3, I also get a new blank record in Table2. I don’t want a bunch of blank records showing up…
    A bit more detail: it is essentially the Quote -> LineItems paradigm, except that there are SubLineItems as well. So my layout is based on the Quote table, and there is one portal showing LineItems. New LineItems can be created here as well, no biggy. But then you click on a LineItem to view a portal of SubLIs (this is currently on the same layout, but a different slide panel). So my context is still the Quote record. When I create a new SubLI in this portal, that’s when I get the new blank LI record in the middle.
    Is this kind of set up supposed to work, and I just have it messed up? Or does the transactional technique only allow for single-depth relations? I could make a new relation that is keyed by both the QuoteID and the LI_ID I suppose, and then store both of those in the SubLI table.

    However, I would like to create a structure that would be reusable in a second use case: copying an entire quote and all related records (this is really where I want the transactions to be employed). Any hints on a structure like this? Or do I just have to create separate structure for each use case?

    Thanks,
    Justin

  13. Todd Geist says:

    HI Justin,

    I didn’t want to leave you hanging, but I don’t have any time to go into detail on this. But yes it sounds like something is messed up. You may want to look into Selector Connector ( google it ). It might give you some ideas on how to handle this.

    Sorry I can’t help more.

    Todd

    • Justin Close says:

      Not a problem, I know how it can go. 🙂 I will look into that.

    • Justin Close says:

      Todd,
      Yes, I think that the Selector-Connector discussion helped solve my problem. Perhaps it didn’t answer the question about what was going on in my current structure, but did provide ideas on how to structure it differently to achieve the goals.

      In your description, and Jason’s too, it was mentioned to not create layouts on the Connector or Selector tables, even though those fields were all globals. This is intriguing: basing a layout on those tables and using those global fields would cause the record to lock, but if your layout is based on another table but uses those global fields it WON’T lock. That’s a fine-line distinction and is a good thing to know about. (Hmmm…what if the Selector or Connector table has no records, does that make a difference in whether or not it locks?)

      Thanks again for these great write ups! This open and helpful community is a great place to swim in.

      — Justin

  14. Matteo Krings says:

    Hello,

    just a note for those who might have the same problem as I had:

    As this method requires a portal on the master layout to delete records, I have around 50 table occurences attached to my transaction table and therefore 50 portals on the layout. This leads to a considerable decrease in performance if I target a portal to delete a record.
    If I remove all portals but the one I have to use in a particular process, I regain initial speed.

    The solution to this: I have made all the portals invisible without losing any functionality and thereby regained the initial performance.

    The process needed 0,8 seconds for editing the record and 4,7 seconds for deleting it.
    Now both take 0,8 seconds over the WAN.

Leave a Reply

Your email address will not be published. Required fields are marked *