Record and Field Locking Strategies


I was recently asked by a client about how to set up a record locking system. The idea of record locking is that when some event happens a record becomes no longer editable, preserving the contents from change. I have used two different strategies for record locking before and they are both useful, depending upon the business case.

Two strategies:

1) Lock the entire record from deletion and editing by employing a customized privilege set and a field for tagging the records to lock. Related table records can be locked as well.

  • Pro: Simple, few moving parts.
  • Con: Less flexible

2) Rather than locking an entire record, instead lock specific fields while still allowing some fields to still be edited. The lock field and privilege set are still employed, but in addition each field you wish to protect has a validation calculation attached to it.

  • Pro: More flexible
  • Con: More field definitions to modify.

Demo file

media_1391927659493.png

Download Demo File here.

Four User Accounts are defined:

  • Admin
  • Manager
  • User
  • Data Entry

All have no password, just enter the Account Name and leave the password field blank.

  • The User account demonstrates the record locking approach
  • The Data Entry account demonstrates the field locking approach
  • The Manager and Admin accounts can run the Lock Record script.

1) Simple Record Locking Approach

media_1391920626170.png

Add a text field called “Locked” and a Lock Icon calculated container field.  The Lock Icon technique employed in the demo is FileMaker Pro 13 only. If you are working with an earlier version, substitute the Base64 Encoded text with links to a set of global icon fields, or omit the icon and simply use the text field ‘Locked’.

media_1391920068292.png

Create a new privilege set in the File Menu: Manage > Security…

media_1391919133532.png

Create a new Privilege Set called “User”. I did this by selecting the default Data Entry Only and clicking Duplicate. FileMaker Pro prevents you from modifying any of its core example privilege sets, although you can render them inactive by not assigning any users to them.

media_1391919296831.png
  1. Name the new privilege set something meaningful
  2. Give it a description.
  3. Then go to the Records area
  4. Select ‘Custom Privileges…’ under the Records pop up.
media_1391919673525.png

Change the Edit and Delete capabliities to ‘Limited’.

media_1391919432376.png

Records can be Edited (or Deleted) when the field Locked ≠ “Locked”

media_1391919918584.png

Finally, you need to assign a user or users to use this new privilege set. Go back to the Users tab, still in the Security area. Select a user and set the Account User to the Privilege Set “User”.

media_1391920578685.png

Record Locking script

  • Test for PrivilegeSetName “Manager” or “Full Access”
  • Then allow the script to proceed
  • If the Locked field is empty, set it to Locked, if is equal to Locked, set it to Empty.
  • Most importantly, click toggle for “Run script with Full Access privileges”
media_1391921119739.png

Lock field and Lock Icon setup

  1. Turn off data entry in Browse Mode. Leave it on in Find Mode so you can still search on records that are locked or unlocked.
  2. Set the Lock Icon field to be a button to the script “Toggle Record Lock”.
  3. Toggle Lock On, Toggle Lock Off.

That is the basic set up. To test this in the Demo file: log in first as a Manager or Admin. Click on the Lock icon on one or more records. Once the record is locked, try editing any of the fields. You should see an error message asking you to revert to the previous value in the field. As Manager or Admin, you are able to toggle the lock icon. Now log in as User and try editing or locking a record. In both instances, you will be locked out and receive an error message. That is Record Locking.

2) Locking Specific Fields

media_1391922458953.png

In this scenario, which uses much of the same infrastructure as above, except that we don’t lock the entire record and prevent deletion and modification based on the status of the Locked field.

Instead of locking the entire record via a Privilege Set, instead we lock individual fields using Validation criteria. The way to do this is to go into the Field Definitions of the fields you wish to restrict:

  1. Go to the Validation tab
  2. Set the “Allow user to override during data entry” option to off
  3. Set the “Validated by calculation” to on (see next step for exact definition).
  4. Enter a Custom Message if validation fails to help the user understand what is happening.
media_1391922471494.png

The validation criteria for each of the fields you wish to lock down, each field should look something like:

Locked ≠"Locked"

Note: It is also possible to lock related records using this same technique. Look in the Demo file for details on how this is done. In the field definition for whichever fields are to be secured, it will look something like this:

Customers::Locked ≠ "Locked"

The related record is referencing the Locked field in the table above it.

media_1391922954904.png

It is also probably a good idea to lock the Data Entry user out of the ability to delete records in the table you are working on. It is one thing to not be able to modify the record, but if the user can still delete the record, field locking is not as useful.

Using a similar approach as above, where we created the ‘User’ privilege set, ‘Data Entry’ is a duplicate of the ‘Data Entry Only’ default set, that has been modified to only allow Create and Edit in all tables. I have set the Data Entry account to use this modified Data Entry privilege set.

Locking Records or Locking Specified Fields Scripting

media_1391924116338.png

To test the field locking, again log into the demo file as Admin or Manager. Lock a record. Now log in as Data Entry. Note that you can still enter data into the fields at the bottom of the main Customers layout that are designed for Deletion|Changes tagging. You should still be able to edit the above fields, but not  the fields with Validation turned on, and you won’t be able to lock/unlock records or delete records.

Scripting the Lock Records Process

How and when should the record locking take place are questions you will have to answer for your own solutions. Ultimately this is where the business logic of an individual solution will come into play. If the records are to be locked automatically after a defined period, server side scripting after business hours to avoid interfering with any in-use records is probably the best option. I have left this scripting out of the demo, because it is likely to be very particular to your solution.

Some options:

  • Immediately upon Exiting or Saving the record—use a Script Trigger
  • After a certain number of days, run a script that searches for all unlocked records older than x days—Script, On Open/On Close/On Server
  • Manually locked by a manager when project is completed—Script

Related Articles

Soliant Consulting: Easy FileMaker modal edit dialogs with full rollback support

Advertisements

4 Responses to “Record and Field Locking Strategies”

  1. Record locking can be a valuable feature to an administrator. Excellent tutorial and I look forward to using this method.

  2. Great tutorial and demo on record and field locking.

Trackbacks/Pingbacks

  1. Record and Field Locking Strategies | Filemaker Info - February 14, 2014

    […] See on hbase.net […]

  2. FileMaker Record and Field Locking Strategies | HomeBase Software - FileMaker Pro Gurus - February 17, 2014

    […] – is something you will run across in your development career.  Douglas Alder offers a solid method for each in this blog […]

%d bloggers like this: