Locking related records for editing

[davidnickerson]

I'm working on a client database with several tables and relationships.

 

The majority of the child tables are related to a primary parent table.

 

My client wishes to have a lock/unlock button that effectively restricts or enables editing for a given record (from the parent table) and all of its related records.

 

I have done this before using Privilege Sets, where each table has a "RecordLock" field that is set to 0 or 1 for locking/unlocking.  A script button toggles 0 or 1.

 

However, in this case, I have dozens of relationships and potentially hundreds of records in play, so the script would need to go to 1 table, set 0 or 1, go to related records for that table, "replace" 0 or 1 in the found set, return to the parent table, go to another set of related records, replace, return, and so forth.  This script is going to be huge, and time-consuming to create.

 

I was wondering if there was some clever way of achieving the same result (i.e., locking all related records in multiple tables) without navigating to each related record separately.

 

Thanks in advance for any suggestions.

[djc728]

I don't know if this will help but you could lock the parent record with a boolean field 0 or 1 and if another user trys to access that record you could bring them to another layout where all the fields are display only - it would mean duplicating the layouts but it would solve the issue of anyone else being able to edit the record.

[davidnickerson]

Thanks for the suggestion.  Currently, there are 59 layouts that need to be accessed.  Duping these and scripting the navigation would take longer than the original script, I think.

[Steve Angier]

Hi David,

 

I think your original approach combining Privilege Sets and a single "flag" field (i.e. RecordLock or similar) in each table is the correct one. The RecordLock field in the child tables can be based on an unstored calculation with no problem (i.e. referencing related fields) so that all of the logic of what makes the field locked resides in the field definition and not buried within the privilege set calculations. You can also reference the RecordLock field to control layout navigation, conditional formatting, portal hiding etc. based on that field.

 

Steve

[eos]

I'm curious as to what you need 59 layouts for.

[davidnickerson]

I'm curious as to what you need 59 layouts for.

It's not a simple database.  It's for a laser company, and there are 2000+ unique fields required.  It's set up as a "tabbed" interface, with mutiple sub tabs, but I don't like the restrictions of FileMaker's built-in tab objects, so I have a separate layout for each tab.

[davidnickerson]

Hi David,

 

I think your original approach combining Privilege Sets and a single "flag" field (i.e. RecordLock or similar) in each table is the correct one. The RecordLock field in the child tables can be based on an unstored calculation with no problem (i.e. referencing related fields) so that all of the logic of what makes the field locked resides in the field definition and not buried within the privilege set calculations. You can also reference the RecordLock field to control layout navigation, conditional formatting, portal hiding etc. based on that field.

 

Steve

 

Thanks, Steve.  Yes, I think that may simplify things a bit.  I don't need to restrict access to layouts, portals, etc., just editability of the record, but this will help.

[Wim Decorte]

and there are 2000+ unique fields required.  

 

I would seriously challenge that.  No entity requires 2000 attributes to describe it uniquely.  There must be some sub-entities lurking in there.

That is where I would start: at the data architecture level.

 

From a FM perspective,  having a table that wide is going to cause performance problems.  So even if it really does require 2000 fields to describe one record it would be best to break it out in a series of narrow tables.

 

As to locking child records when editing a parent; Todd Geist pioneered a lot of that.  Check out modularfilemaker.org, specifically the transactional model.

[Fitch]

Let's say you have a field in the parent table called "locked" that you set to one or empty (or zero). You can then set up privileges in the parent table to limit editing to "not locked"; AND you can use the same privileges in the child table, where it will appear as "not parent::locked"; you DON'T have to create a flag field in every child table and set them.

 

The only wrinkle is that if you use an "allow create" relationship to create child records, record-level privileges can't stop the user from creating child records, only from editing or deleting them. The solution is to use buttons and scripts to create your related records (which you may already be doing).

 

One other thing is you can't control the wording of the "you don't have privileges" error message. Unless you put a script trigger on every field... which is what you're trying to avoid in the first place.

 

PS: 59 layouts ain't a lot. 2000 fields is kind of a lot, even for a file... but 2000 in one table... yeah that's a lot.