5 Replies Latest reply on Apr 24, 2015 5:59 AM by edpod2101

    Is there a best practice in FM to lock a group of records without using db record locks?


      I have designed an order fulfillment solution that supports multiple users filling orders.  Once an order is entered it may be selected from a queue by someone who picks and ships the items.  These are multi-line orders so the process can start and stop.  If the fulfillment process is started I am updating the order record with the user (Account Name).  Anyone trying to access that order, the associated order lines, the order picking, or order shipping is stopped by a script that checks the In Use field on the Order record to determine if the account name value is different from their own (users must user different accounts).  If the process is not completed, i.e. the user leaves the layout for the order or the related records, the order is updated leaving the In Use field empty.  The process can be completed by a different user at a later time.


      I'm using OnLayoutEnter and OnLayoutExit to trigger the script to check for and update the In Use field on the Order record.  This solution is working.  Though I'm finding that the OnLayoutExit doesn't always fire.  This is still puzzling me.  I'm investigating the conditions under which this does not occur.  It seems like once I think I understand triggers something else surprises me. The FM script trigger reference document was only marginally useful.


      I admit that I am not the biggest fan of script triggers.  It seems to me that relying on them can be a little risky.  But this seems like the only way to lock out users from working on a process related to a record where multiple layouts and multiple related records are involved.  In my case, once the Order is selected all the downstream processes need to be locked until the user leaves that order.


      So while the solution appears to be working I am still a little uncomfortable with it.  And I'm left wondering if there is a better approach.  How are you accomplishing this in your solutions?


        • 1. Re: Is there a best practice in FM to lock a group of records without using db record locks?

          Have you considered using record level access through the filemaker security.  You can control who can edit or create records and you can use calcs to determine what conditions need to be meet to be able to edit or create records.

          • 2. Re: Is there a best practice in FM to lock a group of records without using db record locks?

            I have not used FM security for controlling access to records.  I'm only casually familiar with it so it was not something that came to mind when I was building this solution.  It seems as though I would still need to update the "in process" order record with a value, i.e. the account name or a blank value, and then query that value to disable or enable access.  Is that the approach?  Has this worked well for you?


            I'll need to look at this in more depth to get familiar with it.



            • 3. Re: Is there a best practice in FM to lock a group of records without using db record locks?



              Strong +1 for using record level access or field validation. It's not difficult. For both I typically use a number "flag" field that I set to "1" to lock the record.


              1) Record Level Access works through File/Manage/Security - Privilege Sets. When you define a Privilege Set under the "Records" drop down, you choose "Custom Privileges". Choose the table, and under the "Edit" drop down choose "Limited". In the calc box you would use "<Flag Field> ≠ 1". Assign the Privilege Set to the account(s). If your flag field has a 1 in it, those accounts won't be able to edit the record. NOTE: This can cause scripting problems, as script processes can lose the ability to set fields in the record. You can deal with this by running scripts with Full Access privileges, or use the next option.


              NOTE: You can also use the


              2 Field Validation. This uses the same number flag technique, but it only locks certain fields, not the whole record. When you define a field, choose the "Validation" tab, and check "Validated by Calculation" but you have to flip the above calc as a "true" or a 1 result will allow editing and a blank or 0 result will prevent it. There are several fancy ways to do this, but the most readable is something like "Case (<flag field> = 1, 0, 1)".


              Both of these methods are highly secure and require no more maintenance than the setting and clearing of the flag field. However the alerts they spit out aren't very user friendly. I'll usually use script triggers, calculated alerts, conditional formatting and the like to control the user interface. (i.e. a big red calculated text field that says "Locked for Editing" when the record is locked).



              Bob Gossom

              • 4. Re: Is there a best practice in FM to lock a group of records without using db record locks?

                I would suggest that you attempt a transactional approach to this process. Seems like you will eventually bump up against FileMaker internal record locking mechanism at some point using triggers and scripting as you describe.

                FileMaker Transactions - Transactional Scripting - Scale FileMaker

                • 5. Re: Is there a best practice in FM to lock a group of records without using db record locks?

                  Point taken. Without any means to prevent users from accessing the same record, an Order in my solution, I would encounter frequent record locks.  I have always used transactional processing in other large db's.  However, I find this same approach difficult and somewhat convoluted in FM.  Trying to build a complex workflow like order entry and order fulfillment using one layout just doesn't seem practical, and I'll admit, a little overwhelming.  Creating unnecessary relationships also seems wrong.  There are 3 distinct pieces of this workflow:  order entry/order maintenance; order picking; shipping.  So maybe 3 layouts rather than 1.  But designing it this way still doesn't feel right.


                  I probably need to take a deep dive into the technique to make transactions work in FM even though I feel that the tool shouldn't make you do it this way.  This is probably my biggest criticism of FM.  Commit and Rollback (Revert) are already available.  Adding explicit transaction operations (Begin Transaction, End Transaction) would take away some of this complexity and overall would make designs simpler.


                  Things always seem more complex when you aren't familiar with them so maybe I'm making this more difficult than it actually is.


                  Anyway, so far I have not encountered any problems in the solution as is.  But as I stated in my original post I'm not completely comfortable with it.


                  Thanks for the feedback.