Record locking best practices advice

[jstaphse]

I'm trying to change the way users interact with data in a multi-user environment.

Is it feasible to set a designated field to a locked status when entering a field to show that it is in use? Something like "RecordLock 1". With this field set, I could then check for it with navigation logic and redirect to a read-only field accordingly. Also, It would have to be reset to "0" when exiting the record.

Is this the right direction or am I missing something?

Thanks for all help.

[mr_vodka]

If a user is trying to modify a record that is already locked by another user, it will prompt the user that it is locked. If you want to check to see if a record is locked while in the midst of running a script, then you can do it via an error check.

Set Error Capture [On]

Open Record/Request

If [ GetLastError = 301 ]

[jstaphse]

I understand the natural way filemaker does it. I'm trying to give the users an indicator when they first go to a specific record and not when they click in a field.

I don't think the 301 error is found unless 2 users are actually trying to edit the same record.

ADDITION: I got mixed results with my sample file. If user1 was in a field, but not editing, the 301 would not be valid.

Edited April 5, 2008 by Guest
Added test results

[comment]

Record becomes locked when a user begins modifying it - not by merely clicking into a field.

You can use the Get(RecordOpenState) function to indicate the state of the record. But that will only give you the state at the moment when the function is evaluated. So if two users are viewing the same record, and one of them begins editing it, the other user will not know about it until he/she tries to edit it too - or at least refreshes the screen.

[jstaphse]

This is what my users are complaining about. for example;

User1 opens a record and clicks into a field. Then User2 opens the same record and starts editing. User1 doesn't understand why they cannot edit now.

In my test file, I'm checking the status of a lock indicator and if 0, setting a field to 1 when user1 opens it. When user2 trys to open the same record, they get a read-only message and are redirected to a specific layout.

My problem with the sample file (besides the navigation hurdle) is if user1 closes the file after opening a record, the record still looks locked to everyone else.

[comment]

I am not sure where you want to go with this. Two users cannot edit the same record at the same time - that's a basic fact, and you cannot change it. You can allow a user to open a record explicitly by using the Open Record/Request[] script step. That will "reserve" the record for this user, same as if he/she began modifying a field.

if user1 closes the file after opening a record, the record still looks locked to everyone else.

What do you mean by "still looks locked"? It is NOT locked, if the user who had it open closed the file (or just the window used for opening the record).

[jstaphse]

I don't want 2 users to be able to edit at the same time. I want to indicate to the second user that a user has the record in question open and that they cannot edit it, but before they get to this record.

The open record step doesn't work if the user clicks outside a field area unless "save record changes automatically" is deselected. If I do this, the dialog will bug the pants off of you if you click anywhere outside a field.

[fabriceN]

I think you are trying to work around a FileMaker behaviour where probably your data structure could be the key.

How come users are so often modifying the same record simultaneously (or would like to) ?

My guess is that your structure is not relational enough

Imagine a basic inventory, with ins and outs, and a product database.

If the available quantity is updated on the product record on every sale, and that you sell a lot, probability will increase that you want to modify your product description... and block a sale transaction (or vice versa).

Now add another table, with a 1 to 1 relationship to a product. You can update the available quantity there on every sale without the need to block a record in the product table.

Am I clear (sorry, but typing in proper English with a broken arm is just a little above my skill)

[mr_vodka]

I want to indicate to the second user that a user has the record in question open and that they cannot edit it, but before they get to this record.

What you are trying to do will not work.

See this part again.

You can use the Get(RecordOpenState) function to indicate the state of the record. But that will only give you the state at the moment when the function is evaluated. So if two users are viewing the same record, and one of them begins editing it, the other user will not know about it until he/she tries to edit it too - or at least refreshes the screen.

[fabriceN]

Note that plug-ins exist to trigger a script on another client on the network, but I would certainly not advise this method for what you're trying to do.

Edited April 5, 2008 by Guest

[comment]

Well, you could do something like this:

Make the default layout non-editable, with a "Edit" button. Clicking the button opens the record in another layout, perhaps in a new window. This layout is NOT set to commit automatically, so another user cannot "steal" the record until this one has finished (to prevent the annoying message, place a web viewer in the layout's background).

[jstaphse]

I have attached my sample file just to clarify what I may use as an alternative.

With this technique, I should be able to avoid the web viewer (thanks comment).

Thanks for the help.

test_locking.zip

[comment]

That might work, I suppose - though there's still risk of user delaying the return to the main menu, thus keeping the record locked. This could be minimized somewhat by keeping the script paused during editing.