"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Create a Complete Contact Manager



Become a patron of this FREE web site!


Recent Blogs:

Currency Formatting
Currency Formatting

Gathering Portals
Gathering Portals

Multiple Choice Picker
Multiple Choice Picker

Popups and Pickers
Popups and Pickers

Window Locking
Window Locking


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


FileMaker 20 Video Tutorials


Quick Tip:

Semi-Sorted
A database can either be sorted, unsorted or semi-sorted. The first two are pretty obvious, but it is less clear how a database becomes semi-sorted. A database becomes semi-sorted when a new record is added to a currently sorted database and the option to keep records sorted is unchecked. It can also become semi-sorted if a record is edited -- but only if one of the fields used in the sort is edited. Deleting a record will not make a database semi-sorted.



FileMaker 20 Video Tutorials


Fun Stuff:

DevCon History
Here's a list of the FileMaker Developer Conferences* through 2020:
#  Year  Location
23  2020  Nashville, Tennessee**
23  2019  Orlando, Florida
23  2018  Grapevine, Texas
22  2017  Phoenix, Arizona
21  2016  Las Vegas, Nevada
20  2015  Las Vegas, Nevada
19  2014  San Antonio, Texas
18  2013  San Diego, California
17  2012  Miami Beach, Florida
16  2011  San Diego, California
15  2010  San Diego, California
14  2009  San Francisco, California
13  2008  Phoenix, Arizona
12  2007  Orlando, Florida
11  2006  Orlando, Florida
10  2005  Phoenix, Arizona
9  2004  Phoenix, Arizona
8  2003  Phoenix, Arizona
7  2002  Palm Desert, California
6  2001  Orlando, Florida
5  2000  Palm Desert, California
4  1999  San Diego, California
3  1998  Monterey, California
2  1997  San Jose, California
1  1996  Santa Clara, California
* Name changed to Claris Engage in 2020 to match renaming of FileMaker, Inc. to Claris.
**Due to the COVID-19 virus, this conference was changed to a virtual conference.



Tips & Tricks Videos







RSS Feed
Reconciling Record Locking
Level: Intermediate
Version: FileMaker 16
Category: Scripting
Tuesday, January 2, 2018
One of the most important concepts to grasp hold of tightly, when moving from the design of single-user to multi-user FileMaker solutions, is record locking. It's a fairly simple concept to understand. It just prevents two people from editing the same record at the same time. The complex part of record locking is programming your scripts to manage record locking. In this article, I'll start by defining record locking, demonstrate what causes record locking, show you how to test for record locking without hosting your solution and finally how to trap for record locking. I'll also throw in a few examples for good measure so you can measure your newly found skills in the context of a real life scenario.

Reconciling Record Locking

What is Record Locking?
There are many types of record locking but for this discussion, let's define three basic types:
  • None
  • Optimistic
  • Pessimistic
If no record locking is employed, it's possible for two users in a multi-user system to overwrite each other's changes. Let's say user A decides to edit a record for John Osborne and updates his mailing information. During the process of data entry, along comes user B who decides to update John Osborne's phone number (this example uses a flat contact manager where all the information is contained in a single table.) So... who's information is saved? It would be nice if the database could keep track of each field individually and keep both user's changes but it doesn't work that way. It's far too complicated, especially when you start considering what happens if the users are modifying the same field(s). Therefore, databases keep track of changes at the record level. The question is, does the database keep the record changes from user A or user B? It comes down to who saved or committed their record last (and has nothing to do with who started editing first.) If user B saves his record last then his changes overwrite user A's changes.

You can see why record locking is so important in a database, in order to protect valuable data. But, there are two different ways to lock records. Let's consider optimistic record locking first. It allows user A and B to edit the same record at the same time and only warns the users that two people are editing the same record once one of the users tries to save the record. I don't know about you but that's seems like pure craziness to me. What if I spent ten minutes editing a record only to find out I have to start over again because my changes have been overwritten by another user. Despite the idiocy of optimistic record locking option (some technologist don't even call it record locking), Microsoft Access still allows for optimistic record locking... LOL. To be fair, this model should only be implemented when few concurrent record updates are expected. Even so, I still don't get it. Why not just implement pessimistic record locking and be done with it?

Reconciling Record Locking

Pessimistic record locking is really the only way to go and thankfully, what FileMaker supports. As soon as a user starts editing a record, it is locked and no other users can edit it until the record is committed. This allows the changes from the first user's editing to be available to the second user before they start making changes. Clearly, this is the only sane way to approach editing of records in a multi-user scenario. Pessimistic record locking protects your data from being destroyed even if you don't expect many records to be concurrently edited. Why take the chance.

SIDETIP: In FileMaker, editing a record is constituted by typing into a field. Some people believe clicking into a field is enough to lock a record, and they'd be correct if they were using FileMaker 6.

From a FileMaker perspective, basic record locking is really easy to understand. When a FileMaker file is hosted to multiple users via FileMaker Pro or FileMaker Server, only one user can edit a record at a time. If a second user tries to edit a locked record, FileMaker will display the record locking message.

Reconciling Record Locking

You as a developer don't need to do anything. Record locking just happens. Easy, right? Yes and no. As you read on, you will begin to understand that record locking requires no additional effort on your part unless you start writing scripts. Before we write a record locking aware script, let's delve deeper into the nuances of record locking.

SIDETIP: When a record is locked, a script cannot edit or delete the locked record.

Sharing Locally
I always find it beneficial, for better retention, to try out the concepts being discussed in an article using a real FileMaker file. That's why I want to show you how to share a file locally without using FileMaker Server or even another computer. Instead of copying a new version of your file to the FileMaker Server or working on a hosted file, this little known sharing method allows you, as a developer, quicker testing of multi-user scripts or, in this case, testing of the record locking concepts that will be discussed below.

The first step is to open your FileMaker file in FileMaker Pro or FileMaker Advanced. Then, choose Share with FileMaker Clients... from the Sharing sub-menu under the File menu.

Reconciling Record Locking

SIDETIP: Sharing a FileMaker file locally doesn't require any additional licenses. However, there were versions of FileMaker in the past that erroneously resulted in licensing error messages when attempting to share a file to yourself on the same computer. This bug was fixed long ago.

When the FileMaker Network Settings dialog appears, set the Network Sharing option to "On" and make sure the file you are trying to share locally is set to "All users" or "Specify users by privilege set". I find it easier just to specify "All users" since this is just for testing record locking scenarios.

Reconciling Record Locking

The last step is to use the Open Remote... menu item under the File menu to open your shared file. Remember, this is all being done from the same machine. Once you have opened the file, the easiest way to tell the difference between the two versions of the file is to look at the title bar of the window. The original file will simply be the name you assigned the file in the operating system, unless the Set Window Title script was utilized to change the window name. What's important is that the new window will have the same name followed by " - 2" and the name of the machine where the file is being shared. For example, mine appeared as:

PhilosophyOfFileMaker - 2 (John Mark's Mac Mini)

Now you have two users of the same file on the same machine. This will make testing record locking concepts much easier. Let's try a basic record locking scenario. You can use any file you have on hand but the example described here will be a flat contact manager with a single table since it's easy to follow. In either window, click into a field and start typing. Move to the other window, locate the same record and try to type into any field in the contact manager file. If you're file has multiple tables, just make sure the two fields are from the same table being used as the base table for the layout. If all has gone correctly, you should get a similar record locking error message.

Reconciling Record Locking

How is a Record Unlocked?
We already know that record locking occurs when two users attempt to edit the same record at the same time. If you are reading the italicized tips, you also know that a record only becomes locked when a user starts typing into a field on that record. Pretty straightforward. So, what unlocks a record so another user can edit it? The committing of a record is what unlocks a record. Commits can happen many ways including:
  • Typing the Enter key on the keypad
  • Clicking anywhere on a layout where there are no fields
  • Changing records
  • Changing layouts
  • Closing the window
  • Quitting or Exiting FileMaker
SIDETIP: There's a feature in FileMaker Server to disconnect inactive users after a specified period of time. This prevents records from being locked too long while a user is out to lunch or gone for the day.

What Does and Doesn't Cause Record Locking?
Any field on any layout will lock a record from that table no matter where it is being displayed. It doesn't matter if the record is being locked from a list view layout and someone else is trying to edit it from a form view layout. Once it is locked, it is locked everywhere. Even related fields lock records. For example, let's say you have an invoicing layout and are displaying customer fields. Those related customer fields, when edited from the invoice layout, will lock the customer record from a layout showing records from customers.

In fact, a related field will also lock the parent record. In the invoicing example, or any multi-table solution, editing a related field from customers on a layout showing record from invoices will also lock the invoicing record. Crazy, huh? That's because FileMaker considers related fields on the current layout to be part of the parent record when it comes to record locking. However, editing the customer fields from a layout showing records from customers will not lock any invoicing records containing the customer data being edited. Of course, the related fields on the invoicing record will be locked, just not the invoicing fields. Try all these different record locking scenarios in an example file to make sure you really understand them well.

Deleting a record also falls under the restrictions of record locking. It's not possible to delete a record that someone else is editing. A lot of people forget about this limitation but it makes sense when you think about it. Here's an example of a script that tests for record locking when deleting a record.

Reconciling Record Locking

The "Allow User Abort" and "Set Error Capture" sub-scripts simply test for "[Full Access]" to determine whether Allow User Abort and Set Error Capture should be turned on or off. If you want a longer discussion about these sub-scripts, see the article Ultimate Find. There's also some dynamic coding in the Show Custom Dialogs that is also discussed in the Ultimate Find article. What you really want to focus on for this article is the If statement after the Delete Record/Request step. First it checks if the user has privileges to delete a record and then determines if the record is locked. Both scenarios prevent the deletion of a record. And, since Set Error Capture is on, custom messages need to be displayed.

Interestingly, global fields don't cause record locking. Since global fields are local to each user in a multi-user scenario, they can be edited without locking the record where they are being shown. This allows for the possibility of cool interface techniques such as merge letter creation in browse mode, which I will discuss in a future article.

But, what happens to record searches, reports, etc. while a record is being edited? Until the record is committed, the previous data will be searched and displayed in a report. If a record is committed after a report is displayed in Preview mode, a refresh needs to occur. Just enter Browse mode and then return to Preview mode, or use the Refresh Window menu item under the Records menu.

Scripting the Opening and Closing of a Record
There are many ways to script the locking of a record. You can simply use the Set Field step and attempt to write a value to a field on a record. If the value gets written an error of 0, or no error, is generated and the record is locked until it is committed. If the value isn't written then an error of 301 will generate (record in use by another user.) This is easy enough to test for using an If construct but what about the value that was written to the field? Was it a value you wanted written? Was it simply a test to see if the record was locked? What if the script is lengthy with many Set Field steps?

There's a much better way to test the status of locking on a record. The Open Record/Request script step doesn't require the modification of a record to gain control of it. The same errors are generated but there's no need to attempt a write to the record to test for locking. Open Record/Request simply locks a records without modification, if possible, until it's committed. Think of Open Record/Request and Commit Records/Requests as opposite sides of a coin.

Here's a simple example of opening a record with a script. Let's say you are trying to mark an invoice with the current date to designate it as paid. Seems simple enough but what if someone else is editing the invoice? As discussed previously, you could just try writing the current date to the field using Set Field and test for errors. While this works as well as opening the record in this simple example, it's shortsighted. If there are multiple fields that need to be edited then all of the Set Field steps will generate an error. It's better just to do one test with Open/Record Request, IMHO.

Reconciling Record Locking

This simple example is the basis for all record locking scripts. Put as many Set Field, Insert Calculated Result or any step that modifies a field in the Else portion of the script. That's as complicated as it gets when working with a single record. Also, I like to end any script that employs Open Record/Request with Commit Records/Requests. I don't like leaving records open, especially when I locked it with a script.

Record Locking on Multiple Records
Now for the tricky part! It's pretty easy to open and close a single record with scripting. But, what happens when the script tries to modify multiple records, such as occurs with Replace Field Contents or a looping script? For example, let's say you are looping through one hundred records and the first fifty get written but the fifty-first is locked. Do you code some logic into your script that remembers that record and tries to write it later? What if the record doesn't become unlocked for twenty minutes? Do you tie up the users machine while it continues to test? Maybe you pass off the script to the server so the user can continue to use their computer. Sounds complicated and unreliable to me. Maybe you decide to use a different approach and remove the data you wrote to the first fifty records and tell the user to try running the script later. The only problem is it's possible one or more of the first fifty records are now locked. It's a difficult issue to overcome, so much so that I almost never try to write to more than one record in a multi-user scenario.

So, what's the solution? Well, you could save the process to run server side in the middle of the night when there are no users locking records. It all depends how quickly you need the results. If you need the results immediately, it's time to think creatively. Unfortunately, any discussion of clever workarounds requires complicated example which require an entire article. Don't worry, I plan on writing about these techniques but you'll need to wait a bit for me to publish them. Thanks for your patience.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Salman Javaheri 09/13/2022
  Thanks for the response John much appreciated! What if incorrect data e.g. text in a date field, was entered. What is the best way of capturing such an error, so that the script can revert (and unlock) the record.
Salman Javaheri 04/23/2022
  Is it worth error checking (and reverting if errors found) on the final commit?
Response by:   John Mark Osborne 08/24/2022
Sorry for the log response time. If you've been able to edit the record then there is no record locking. In other words, you have control of the record once the record is being edited. This is automatically done by FileMaker. If you are referring to a looping or Replace Field Contents script step, I don't recommend running these types of scripts (in general) unless you know the record will never be locked (e.g. you just created them) or there is nobody on the system. Hope this helps and sorry for the late response.
Henning Orth 10/23/2019
  Great post, thanks so much for the insights...!
Response by:   John Mark Osborne 10/23/2019
You are most welcome! Thanks for reading the article!
Jonathan Ackerman 08/06/2018
  What I have been using in my solutions is when performing updates on multiple records (i.e. updating invoice status on multiple invoices) is to use FM built in transactional abilities. When looping, I create a relationship to the target record from a global field set to the targets primary key, update that record, then go to the next one. Only commit at the end. If any errors Ii.e. 301) are thrown, revert record and all changes to all records are reverted. I let the user know that the process could not complete since 1 or more records are open by another user.
Response by:   John Mark Osborne 08/07/2018
Yes, transactional processing is a great way around record locking. Invented by Todd Geist, it's definitely a tool I keep on my belt but it shouldn't be the only tool. Every unique record locking scenario should be looked at with a fresh perspective rather than just applying the same solution every time. I think it's about time I got to that followup article. Thanks for the reminder!
Mauricio Navarro 01/07/2018
  you wrote"...they'd be correct if they were using FileMaker 6." FM6? it must be 16, right?Now, if I understand the step indicating how to work on two copies of a file from a single computer, I share the file; no problem here. I can open the file on my iPhone (making sure sharing works). I have the original file open, then i use 'Open Remote' to open the same file but I do not get anything, the original file remains open but no second file's showing. Am I doing it wrong?
Response by:   John Mark Osborne 01/11/2018
I actually meant FileMaker 6 because record locking only required clicking into a field in this old version of FileMaker. Since FileMaker 7, record locking has required typing into a field. Also, the ability to open a locally shared FileMaker file is not supported on iOS. You'll need to use a copy of FileMaker Pro or Advanced on OSX or Windows. And, just to be clear, there is no need for a second device. All this occurs on one desktop machine. Happy FileMaking!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!