Multi-field, multi-record script - locking issues?

[sal88]

Hi all

With the amount of scripts and users I now have it's time to look seriously at record locking implications. Any pointers greatly appreciated!

I have a Labour, Items and Invoices table (Labour and Items are actually in the same table but are generally treated as though they were in separate tables).

A Labour record has multiple Item records assigned to it. Items can only be added via a portal within the Labour record layout.
Labour and Item records are assigned to an Invoice record via the Invoice_IDF field. The assignment process is done by a script that is run once a month, this script is run when everyone (apart from the user running the script) is out of the database, so record locking is not an issue in this case.

Where I need advice is when labour/items are being manually added/removed to/from newly created invoices.
There are two ways that this works:

1. Within the Invoice record:
There is a portal displaying both uninvoiced items and labour (as they are actually in the same table)
A button within the portal row is clicked, and all the relevant records are assigned with the Invoice ID of the currently open Invoice Record. e.g. if an Item is selected, then it is given the Invoice ID - this Invoice ID is also assigned to the Labour record that that Item it is attached to, and any other Item records that are in that same Labour record.

2. Within the Labour record:
There is an 'Invoice Assignment' button. When this is clicked a dialog pops up with the invoice_ID field. The user then populates/blanks the field and the labour and item records' Invoice_IDF is populated accordingly.

How can I safely run these assignment scripts so that they don't do half the job - for example assigning the labour record but not the items record because someone is editing it.

If it is advisable/simpler for the second method to be done away with (and add the option to 'unassign' labour/items via the invoice record in the 1st method) then that can be done.

TIA

Edited March 14, 2016 by sal88

[sal88]

I've just had a play and found out that if you are editing a field of a record, then you lock not only that record, but its child records/parent record/fellow child records. Therefore no one else can 'partially' edit a group of related data.

Therefore could my script look like the following:
Set Labour record Invoice_IDF field
If error then "record is locked by x, please try later"
Else
Set Items records Invoice_IDF fields
End IF

My queries:
1) would it be OK to use SQL UPDATE (via 360Works SM plugin)
2) This is really nitpicking, but what if, just after the 'Set Labour record Invoice_IDF field' step, another user goes in to the record thus locking the Items records that have not yet been populated?

[comment]

29 minutes ago, sal88 said:

I've just had a play and found out that if you are editing a field of a record, then you lock not only that record, but its child records/parent record/fellow child records.

No, that's not true. Please read the article mentioned here:

In addition, I suggest you look at:
https://www.geistinteractive.com/filemaker-transactions/

[sal88]

Thanks comment, will have a read.

[Wim Decorte]

1 hour ago, sal88 said:

I've just had a play and found out that if you are editing a field of a record, then you lock not only that record, but its child records/parent record/fellow child records.

 

Let's be very clear about this: by editing a record you do not lock its children, children or parents.

Except if you edit a record in a portal.  By doing that you lock the record (child) that you are editing AND its parent.  But not the siblings or the children of the child.

It's one of the reasons why building "heavy" layouts with portals for people to edit records can be bit of a problem.

 

1 hour ago, sal88 said:

With the amount of scripts and users I now have it's time to look seriously at record locking implications.

 

Record locking issues and multi-user aspects should not be an after-thought though; it should be something that you take care of from the onset

Only the # of users in the system play a role here, not the # of scripts.

Do keep in mind that if you are going to do live modifications to the system then you have a whole other area of locking issues to contend with:

Error 301 is your typical "record locked by another user" but here are all of them; you can run into any of these when you do live development.  Not saying that you shouldn't do it; just that you should make your code take care of these when you do.

 

300	File is locked or in use
301	Record is in use by another user
302	Table is in use by another user
303	Database schema is in use by another user
304	Layout is in use by another user
306	Record modification ID does not match
307	Transaction could not be locked because of a communication error with the host
308	Theme is locked and in use by another user

 

[sal88]

To come back to the question of sibling records, I opened up the same parent record on 2 machines. On one machine I clicked on a field of an existing row of the Items portal, put an 'X' at the end of it and remained in that field. On the other machine, if I now try to type in either a field of the parent record, any of the Items portal records, or even the Mileage portal (completely different table as well as different occurrence), I get a "[user] is modifying this record" message and I can't make any amendments.

Both machines are using the same layout (and therefore table occurrences).

Are we talking about the same thing?

[comment]

As Wim said, editing a record in a portal locks both that record and its parent. When another user tries to edit another portal row, Filemaker tries to lock that record and its parent - but fails, because the parent is already locked by the first user. The message you see refers to the parent record. If the second user tries to edit the same record in a layout of the child table, they will not be blocked.

[sal88]

I see, so the sibling/child/parent records are not actually locked; you could edit them via a different layout. In my case however, given that there is only the one layout (with portals) in which to edit these parent/sibling/child records, could it be said that on a very superficial basis, there is a 'lock' on all records that are on display, if any one of them is in the process of being edited.

[Wim Decorte]

40 minutes ago, sal88 said:

In my case however, given that there is only the one layout (with portals) in which to edit these parent/sibling/child records, could it be said that on a very superficial basis, there is a 'lock' on all records that are on display, if any one of them is in the process of being edited.

 

No.  It means that the parent record (of the entity showing the portal) is locked.  You can't say that all records are locked not even on a superficial basis.  The thinking/understanding here is crucial.

 

Knowing nothing of your design, let me say this: show things through portals where you really mean list views is a bad design choice.

If you want to keep to your design, then simply give your users their own parent record.  That way they can at least change the desired (fake) child records without running into conflict with the other users.

 

 

1 hour ago, sal88 said:

Both machines are using the same layout (and therefore table occurrences).

That is not a factor.  It's the records that are important.  If all users land on the same parent record.  Then only the first user to change a child / portal record will work because that action will also lock the parent.  All other users will be prevented from changing ANY child record because the parent can not be locked.

Which is why using a 'fake' parent and showing portals in lieu of real lists is not as easy as it sounds...

Aside from this locking issue; you're probably also loading more data than you really need to, causing FMS and the client to work harder than it has to.  That's largely a UX issue more than anything else: what user can comprehend all the data that you throw at it through those kinds of portals?  Isn't there a way where you can guide them to the data they need to see when they want to see it instead of showing it all?

(Again with the big caveat that I'm making assumptions about your layouts and your solution does - I'm just going by the limited description and having been through troubleshooting on similar designs).

[sal88]

Wow I have a lot of learning to do. Thanks for all your input guys, I'm going to have fun looking in to this much overdue issue