Jump to content

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


sal88

This topic is 2935 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by sal88
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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
301
302
303
304
306
307
308

 

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 2935 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.