Under certain circumstances, the users of your database systems might want records to be locked, in order to prevent further modifications to occur. One common example would be after an order record gets printed and sent to the customer. Locking a record (so that no further changes can be made to it) is a fairly trivial task if you turn to FileMaker’s Security feature set, however this problem becomes a lot trickier if the users who can lock records also need the ability to unlock records.

While there are certainly many different ways to handle this type of functionality, I would like to try and explain how I handle this type of scenario.

Dissecting the Problem

The most logical approach to this problem (for most people) is going to involve adding a new field to your table that will indicate whether or not the record is locked. It’s probably just a simple number field that will store a boolean result. If you implement this approach though, and then use FileMaker’s security to prohibit modifications to the record whenever that new field is set to 1, you will quickly realize that users will never be able to clear that boolean flag (since the security you set up prohibits modifications to that record).

Coming Up With a Solution

Since we won’t be able to modify flag fields if they exist in the same table that can potentially be locked, we’re going to need to move our flag field elsewhere. It’s not often that we get to use 1:1 relationships in our databases, but I think this problem here is an ideal candidate for one. What I’m proposing is that we create a whole new table solely to keep track of what records have been locked.

We won’t need anything fancy in our new table, all we need is a link back to the table that we want to lock — a foreign key. With that new table (including foreign key) built, the next step is to create a 1:1 relationship between our tables

Summary of What I Have Created So Far:

New Table –> Locks
New Field –> zk__orderID__f (this is a foreign key pointing back to the table that you want to be able to lock)
Relationship> Order::OrderID = Locks::zk__orderID__

Relationship Between Orders and Locks

Next Steps

The premise behind where we’re going with this is basically that if a related Locks record exists, then the current Order is locked. If one does not exist, then the current Order is not locked, and the user should be free to modify it.

The next thing we’re going to need to do is to manage the creation and deletion of these Locks records. The way that I handled this was quite simple. Any time the user clicks on a lock button that I added to my screen, a script will run that does the following:

1. Stores the current ID’s primary key in a variable
2. Opens a new window
3. Goes to a layout based on the Locks table
4. Performs a find for an exact match of the orderID stored in a variable
5. If a record was found, delete it. If one was not found, create it, and set its foreign key to the value of your orderID

Now you’re done with managing the creation and deletion of Locks records.
If you need some help with this scripting, or just want to see this in action, stay with me, there’s an example file available below

Locking Down The Right Records

In order to secure records that are marked as locked, I would recommend we first create a simple calculation field in our Orders table.
All the calculation needs to do is to return a boolean value of whether or not a related record exists.
The calculation I used for this purpose is set up as follows:

not IsEmpty ( Locks::zk__LOCKID__p )

Lastly, we can use the calculation we just created in order to restrict modifications to locked records.
To do this, you’ll need to go through FileMaker’s Security settings, and modify the custom record access for a non-standard privilege set.

Custom Record Access

That’s all there is to this technique.
Here’s a sample file for you to test it out for yourself.
Also, while this technique is relatively easy to use, and should be pretty reliable, I’d love to hear of other approaches to solving this type of problem. Let me know in the comments below.