Serialized Fields & Table Locking

If, like me, you have been developing and supporting FileMaker solutions for many years, and even if you’re newer to the platform, you have no doubt learned when (and when not) to make changes to schema for apps being used in production. When making these changes, FileMaker will lock the table and prevent the creation of new records. 

In a recent conversation, Danny Mack and I were discussing how to maintain data integrity for a highly complex application in which we knew that database administrators would be in Manage Database often. The big question was how to (or could we) protect data integrity when making schema changes? The more obvious choice is to ensure that your scripts check for and manage any errors they encounter, including errors encountered if a table is locked, but not all developers use such extensive error trapping. As our discussion continued, the recurring theme was; “Is there anything more we can do in these cases”? Danny then shared an idea he had been introduced to by Kevin Frank that I’d not heard before; that a contributing factor to FileMaker locking a table had to do with how FileMaker manages fields that auto-enter a serial number. This was a new idea to me. Fields set to Auto-enter Serial numbers are used all the time and for many different reasons. I was very curious to know more.

Read Kevin’s post on this topic here: Live Development + “New Record”.

Auto-enter Serial number versus Get (UUID)

Any time a new table is created in FileMaker, the first attribute I typically created was an ID field with an auto-enter option set to enter a serial number. This ID would serve as the unique identifier for the record.

FileMaker Pro 18 (and FileMaker Pro 17) will auto-create the Primary Key field for you, using the Get (UUID) function, when a new table is created.

The only exception to this was if data in a particular table would need to be merged or synced with a mirror of that table in another file. For example; a sales team working in the field and where each team member collects contact information in a local copy of the application in FileMaker Go. In such a case, a serial number is not sufficient as a unique identifier as each team member’s copy of the application would be creating records with an incremented number. When the data is combined into a single table, records would lose uniqueness; presenting a major problem.

FileMaker gave developers another way to create a unique identifier which addressed these issues when they introduced the Get (UUID) function in FileMaker Pro 12 and Get (UUIDNumber) in FileMaker Pro 17.
The Get (UUID) function, and Get (UUIDNumber) functions return a universally unique identifier (UUID) which solves the uniqueness problem. 

Maybe I’ll stop here for a moment. I used the example of a Primary Key because it is one of the more common uses of an auto-enter serial number, but it is not the only use by any stretch. Auto-enter serial number fields are used all the time for a variety of reasons (invoice numbers, part numbers, check numbers, etc, etc). It is so easy to set up a field to manage these things that most developers do not even think twice about it. 

Table Locking

I think it is worth taking a moment to review what happens when a table is locked by FileMaker, and perhaps the best place to start is to know what specific activities will lock a table. The following activities will lock a table in FileMaker:

1. Creating a new field
2. Duplicating a field
3. Viewing or modifying field options
4. Viewing or modifying a Calculation field formula

This is really important; when a table is locked, no new records can be created in the locked table by a user or a script. If a user tries to create a new record, manually, while a database developer is doing one of the above activities, FileMaker will display a dialog alerting the user that an error was encountered.

This will occur if, say, a user clicks the New Record button in the Status Area, but what would happen if the record creation occurs during a scripted process? And to further complicate things, what if a developer has added a Set Error Capture [On] script step? Depending on where the Set Error Capture step is added, FileMaker might not display a dialog. This is really important, and is where data integrity issues can be introduced. Even though records cannot be created until the lock is released, existing records can still be updated. It is a very common practice, immediately following a New Record / Request script step, to use a Set Field step (or multiple Set Field steps) to set values into various fields in the new record. If the table is locked and there is no error handling for such a case, however, the script could update the wrong record and the user would never know about it. And really, these are the types of errors that can go hidden for a very long time, possibly forever. This is never a good thing with any data, but think about financial data being overwritten in this case. That could potentially have dire outcomes. Perhaps I will take a moment here and just put out a friendly PSA. You should always trap for errors and manage them. You should also use the Set Error Capture step sparingly and with intention. It is a mistake to just add this step to the top of your script, unless what you are programming for requires it.

Once FileMaker has locked a table, that lock will remain in place until the Manage Database dialog has been closed and changes either committed or reverted.

Testing Theories

With the file hosted, it was time to test and observe. I opened the hosted file in two different copies of FileMaker Pro Advanced. In one copy, I opened the Manage Database dialog for the table with the auto-enter serial field. I then opened the calculation field as if I were going to modify the formula and just left it there. In the other copy, I navigated to the layout based on the table with the serial field and tried to edit a field value in the text field. As expected, it failed and FileMaker gave me the warning dialog shown above. As soon as I closed the Manage Database dialog, I was again able to make changes.

On the next pass, in one copy, I opened the Manage Database dialog for the table with no auto-enter serial field and did the same thing. I opened a calculation field and started to modify it. I then opened the other copy and navigated to the layout based on the table with no serial field and tried to edit a field value in a text field. This time, there was no error. I was able to modify the record and save changes. No issue. 

I find this really interesting. Does this mean I should simply never use auto-enter serial values? I don’t think so. I do think it’s something to consider depending on what you’re developing. There are some smart FileMaker folk who can, I’m sure, answer the question about why FileMaker works this way. I’ll try to find some time with a FileMaker engineer at DevCon this year and see if I can get some light shed on this. So, stay tuned. I’ll probably follow this up.

Alternative Strategy

In the meantime, and assuming you want to explore a strategy to stay away from adding auto-enter serial values, you could create a utility table that holds one record with a field for each auto-enter field that you require (or stores a record for each serialized field needed). You can then choose any value to start with and you would manage the values in this table, and fields, by script. This does mean that you would need to either give a custom interface with a button for a New Record, or use Custom Menus (my preference). For example, an invoice solution may have a New Invoice script like this one.

As shown on line 7 in the script above, the New Invoice script would call the Retrieve Serial Value and Update script shown here.

As with any scripted process, there are considerations. For example, it is likely that multiple users may need a serial value at the same time which could create a record lock issue and not allow the serial values to be managed correctly. To deal with that, I have placed the gathering of the needed value (and the update to the field to hand out the next value) in a Loop that simply checks for any errors. Once the process is able to gather and set what is needed with no errors, the script will commit those changes and pass the needed value back to the calling script.

Summary

I’m always a bit careful to suggest that any particular way of doing something is a “right” way. As with any development effort, there are many things to consider when you are choosing your approach. Depending on the application you are building, there could be times where it is an advantage to not use serialized fields in favor of Get (UUID) and manage the needed serial values with a script and a utility table as demonstrated. No matter the approach you use, you should always trap for and manage errors to ensure that your data integrity is not compromised; whether by a failed user process, or due to database administrators who need to make changes quickly by modifying schema in live files.