From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer
We have discussed in the past what a great asset the replace or relookup feature can be when updating all the records in a found set. There is a known behavior that will not allow you to do so successfully when working with a database that is actively being used by multiple users. The behavior is known as record locking.
If two users are editing the very same FileMaker record at the very same time, what does FileMaker do? Does it accept the information from the guest that came into the record first, came into the record second, left the record first or left the record second? Again, two guests are wanting to edit the same record at the same time.
When a FileMaker database is being hosted over a network, only one user can modify the same record at one time. The user that first enters a record locks it until they commit the changes or leave the record. If you try to modify a record that another FileMaker user is currently browsing or editing, you will get a message telling you that modifications cannot be done. The dialog box will also provide the name of the user that is on that record. This is how databases protect themselves from editing conflicts.
Record Locking is a very good thing but it can cause problems for commands such as replace or looping scripts that try to modify a set of records in a batch like process. In fact, the problem can even come up if you try the "Go To Field" script step in a record that has been locked. In most cases, FileMaker scripts will simply pass over locked records. You won't know the record was passed over, it will simple do it. This can be a bummer!
Other ScriptMaker steps besides the Go To Field step that can be thrown off by record locking include Set, Insert, Paste, Replace, Cut, Copy, Clear, Import ( synch and update existing), Relookup, Select All ( in a field), Delete Record, Delete All Records and Delete Portal Row. I may have missed one or two but that is close to all of them.
Do not despair, as with most FileMaker problems, there are techniques that you can use that will help you address the problem. They can be complex and take time to implement and test.
ACID is a data integrity compliance term that centers around the four key areas of Atomicity, Consistency, Isolation and Durability. In the FileMaker world, almost any discussion of record locking issues and overcoming them will eventually refer to a technique being (or not being) ACID compliant. A google search for the term of FileMaker and ACID yeilds some interesting results. In particular, one of the results is from AMAZON.COM and few preview pages from the book Special Edition Using FileMaker 8. This shows some excellent information from a book that is full to the brim with useful information and I would recommend adding it to your FileMaker book collection.
More info about the author and FileMaker in general, contact me at email@example.com.
© 2008 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.