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.
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.
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).
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.
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.