Multi-user dilemma: replacing fields in many records while person is editing a record

[cpbatwes]

This seems like it would be a classic filemaker problem:

I have version 5.5. I have a complex contacts database used by 7 people. They often need to perform complex finds, then mark the records, and then assign a category to this new group. The category is assigned by entering a category number in a multi-key field. It is scripted to enter this number for all records in the found set at once.

MY DILEMMA: What if someone is editing "John Smith's" record while another person is adding a category number to a whole swath of records, one of them being John Smith. The add-category script will of course miss john smith b/c the record will lock, right? What are the workarounds here? They HAVE to use the database at the same time.

-Should they be sending messages to each other that say "hey, click out for a second, I'm about to do a global replace?"

WHAT DO THE EXPERTS DO IN THIS CASE?

I'm willing to institute some "database use rules" for the users, but I don't want to limit them too much.

Your help is much appreciated!!!

[BobWeaver]

You are absolutely right! It is a classic problem. But I have never heard even the slightest mention of a solution, or even an acknowledgment that it's a serious problem for that matter. If you read any Filemaker books that even get into the discussion of concurrency in multi-user implementations, their advice is always something like "Be careful." That's a lot of help, isn't it! I think it really boils down to restricting mult-user implementations to browse only. Sorry if i'm not much help, but it's a bit of a sore point that I've had with Filemaker for a long time.

Basically, you should not use the Replace command in a multi-user database. Use a looping script with a Set Field command. And, check for a locked record with Status(CurrentError)=301. Then, you have to have some way of bailing out gracefully when you find a locked record part way through your loop. How you do that will depend on what you are trying to do.

[Vaughan]

I don't think it's just a FMP thing -- all good RDBMS have to have record locking (they call it row locking).

One way could be to check how many users are sharing the database with Status(CurrentUserCount) if there is only one user then there cannot be any problem with locking.

Not much, but it's all I can think of at the moment.

Another thought: would a locked record be included in a Find?

[BobWeaver]

Yes, I believe a find will return locked records.

My main complaint is that while Filemaker will lock individual records--row locking--as you say, it doesn't have any of the more advanced locking features that other RDBMS's have, such as the ability to request and lock an entire found set. If you could do that, you could create a script that would request/lock a found set, perform whatever operations are necessary, and then release the lock. That way, if the lock on the entire set is not granted, you just pause and then try again. You don't get into the situation of modifying half a set of records before hitting a roadblock that might be very difficult to recover from.

[cpbatwes]

Thanks for your comments -- here's a couple more questions and thoughts:

QUESTION: IF A USER IS EDITING/LOCKING A RECORD, CAN I AT LEAST COPY DATA FROM THAT RECORD?

Question: my users will have filemaker open most of the day. will status current user count find how many users have it open, or how many users are actually locking records. THE DIFFERENCE is that if they're just sitting at their desks but not editing records, all should be OK. how do I check for that beside asking users to yell next door?

Comment: on marking records in a found set, I'm working on a solution that uses copy all records to copy the unique id's to a global field unique to each user. then the user can save this list and later call up those records again through a go-to-relationship step.

THANKS.

[BruceJ]

Using the global field is probably the best way to save a found set.

Even better is to have another file you call "Saved_.fp5" and then write a script that puts the recordIDs into a single field in the "Saved_.fp5" file. Of course, you need to write it so that there are carriage returns between the RecordIDs so that you can then create a relationship between the field of all the RecordIDs in the Saved_ file and the other data file.

Since you can't Copy and Paste when a record is in use by another user, a script would have to do someting like "Set" a Global field="Status(RecordID)".

Your loop then would have to go through each found record, "set" the global field, then perform a sub script that added the contents of the global field into the appropriate record in the Saved_ file. the loop woudl have to go back and forth and do this for each record.

I say "loop" but not using the Loop command, it's done with alternating scripts instead of the tradional simple loop.

Maybe something like this:

(MAIN.fp5)ScriptA

Error Capture - ON

User Abort - OFF

Enter Browse Mode

GoTo Record Request - First

Perform External: ScriptC

Perform Script: ScriptB

(MAIN.FP5)ScriptB

SetError Capture - ON

Set Field(Global ID Holder) = Status(CurrentRecordID)

GoTo Layout: LayoutContainsAllFields

Copy: Global ID Holder

GoTo Record -Next

If

-- Status(CurentError) = ?? ***If there is no Next Record***

--GoTo Record Request - First

--GoToLayout: UserLayout

--Exit Script

Else

End If

Perform External Script: ScriptD

("Saved_.fp5")ScriptC

RecordRequest: New

("Saved_.fp5") ScriptD

GoTo Layout: A Layout

Paste(tempHolder)-selectAll **Have a CalcField= ("P" & tempHolder)

Copy(CalcField)

Paste(SavedSet)-do NOT selectAll

Perform External Script: ScriptB

[ December 20, 2001: Message edited by: BruceJ ]