Locked records

[Paolo]

How should I design my scripts to behave correctly with locked records in multi-user solutions ?

Thanks in advance for any suggestion.

Paolo

[jscooper]

Set Error Capture [On] will let you trap errors. After each attempt to change a value, go into a field, whatever, check status(currenterror). 301 is the error code for Record in Use. You can then throw up a message for the user or whatever. Example:

Set Error Capture [On]

Set Field ["LastName",""Doe""]

If ["status(currenterror) = 301"]

Show Message ["Sorry, this record is busy."]

Else

Show Message ["Record Updated."]

End If

[Paolo]

Hmmm, looks like a lot of work, to add error trapping to every script

If don't set error capture on what happens ?

does it hangs until the record is free or display an error message to the user?

is it possible to know who's in the record?

THANKS

[jscooper]

Yup, it can get tedious, I'm afraid.

If a script to change a busy record, the user'll get an error message. BUT, once the user clicks OK, the script continues anyway. So if you're expecting the new value to be there later in the script, you'll have a problem.

As far as I know off hand, there's no way to trap who's in a record.

Jeff

[Vaughan]

Usually it's not necessary because FMP will wars users if they try to modify a record (click in a field).

It's only really necessary for scripts that bulk process records, especially looping scripts and scripts that use the replace step. Often the script will continue OK, the problem is that the *process* will not have been completed. Some remedies involve recording the recordIDs of the records that were locked and prompting the user to update them later.

[Lee Smith]

Hi Vaughn,

Who said FM was a weak product?

Vaughan said:

Usually it's not necessary because FMP will wars users if they try to modify a record (click in a field).

Does it take them prisoner or just kill them?

LOL

Lee

[Paolo]

Does "Replace" too returns error 301 if a record is locked?

[BobWeaver]

Yes.

[bruceR]

Paolo said:

Does "Replace" too returns error 301 if a record is locked?

Yes. The general consensus in other Filemaker lists is that replace is not really an acceptable operation in multi-user environments. You'll get much better speed out of a looping script and you can log the ID of locked records and then operate on them later or whatever you choose.

[LaRetta]

I think it's important to clarify here...

The Replace function (when used within a script calculation with Set Field or whatever) produces an Error 301 but Replace Contents does not.

And to protect your scripts, place your cursor in any field with Go To Field [uniqueID or whatever] first, then run a Set Field and test for an error. Once you know you have no error, you've claimed the field until you perform some action that removes the cursor again. In this way, you don't have to continually test because you own the record.

LaRetta

[vanderark1]

Could someone give a simple example of how to script the activity of re-doing the loop script after I run it and have captured the id's of the locked records?

I understand everything about error 301 above, and I have successfully captured the Record ID's of the locked records in a seperate file. Where I am stuck is how to script the activity to AUTOMATICALLY go back and run the looping script on just the captured ID's. Thanks in advance.

[-Queue-]

Set a global in the main file with the carriage return-separated locked ids. Then use a relationship from this global to the id field itself. Then

Set Field [gfailsafe, 0]

Loop

[color:"white"]__Exit Loop If [not self::constant = 1 or gfailsafe = 50]

[color:"white"]__Go to Related Record [show, self]

[color:"white"]__Go to Next Field

[color:"white"]__If [not Status(CurrentError)]

[color:"white"]____Perform Script [sub-scripts, "Set record's fields"]

[color:"white"]____Set Field [gids, Substitute( "

[-Queue-]

Also, there is a way to trap who is in the locked record, but it's tedious. You would have to set a text field with the current user before any entry or modifications were made. Then a script can test that the value in the field matches that of the current user when any batch scripting is performed. If the test fails, store the id for the future.

[BobWeaver]

I have been working on a method that will lock an entire found set of records before any operations are done. It's a continuing project; I have had some success but I'm not totally happy with it yet. It uses record level access to lock other users out of the found set. The idea is that you run a "Lock Found Set" script which will return a boolean value of true if successful, or false if unsuccessful. So, rather than going through the records and then having to do a messy cleanup on the locked records later, you have an all or nothing situation. You either get a complete set of locked records or none. After performing your main script, you then run an "Unlock" script to free up the records for the other users.

[-Queue-]

It seems like that process could slow you down quite a bit. But if you're in a situation where "all or nothing" is vital, it sounds like a good way to do it. For the average batch process, I would think a loop similar to the above would be most efficient, especially if no locked records are encountered a majority of the time, in which case it will exit immediately.

[LaRetta]

Locking a found set is powerful when you need to preserve summaries from the data to write to GL, etc. I loop and lock the found set until clean before any major process that must be preserved as a group. Access Privileges are set to not allowed edit (but can view) this locked set. After I'm done 'doing my thing' the set is then released. I think it's quite valuable.

LaRetta

[BobWeaver]

Simply looping and processing is definitely faster if you have some graceful way to roll back the operation if you get stuck in the middle.

I have a system where a daily report has to be printed and submitted to a government agency. A record must be reported once and only once. So, I get a found set of all unreported records, lock them, print them, set the reported field to the current date, and then unlock the records.

In this kind of situation, the print command is going to work on all records whether another user is accessing them or not, but you won't necessarily be able to mark each record as reported after printing is finished. So, it can create a dilemma without found set locking.

Found set locking is a feature that is built into other database programs (including MS Access), but Filemaker has never done it. I think this is one of its biggest shortcomings.

[Paolo]

How do you lock a found set???

[Ugo DI LUCA]

Paolo,

You can lock records in many different ways.

According to your needs, you'd either use a mark, a calculation or a global field.

All the following methods rely on Access Privileges Settings.

You may use a n_locker set to 1 or 0, a c_locker calculated (InvoiceSent- Boolean result) or a t_locker (Multiline field holding Text Ids) in a Locking.fp5 file (with one record only).

Or all 3 together.

Then, in Access Privilege, limit modification to records with a calculation along the lines of (for all 3 together)

n_locker or c_locker or Patterncount(::Locking:t_locker,ID)

For Foundset, rather than a loop setting the n_lock, you could capture all IDs with a Copy all records, paste them in g_NewLock and script a SetField[t_locker, t_locker&"

[Paolo]

>All the following methods rely on Access Privileges Settings.

ok, I got the idea!

[PiedPiper]

replace contents in script doesnt return an error? Only one error or any error? Good grief. I've been merrily script set error capture on and testing for something that does no good? How can filemaker like this happen? All scripts should produce an error according to the list, shouldn't it? I'll have 22 scripts to redo. This major sucks - I searched tech info and can't find a thing about it. Nor help.

Or is it only in certain circumstances? please clarify anyone. I'm a bit bummed by this.

P

[BobWeaver]

I was pretty sure that a replace operation would produce an error 301 if another user was accessing one of the records. However, it doesn't give you an error message; you have to have error capture turned on and specifically check the status(CurrentError) immediately after the replace. I'll have to go back and test this just to make sure though.

[LaRetta]

Hi Bob! Hi Pete!

I don't know from personal experience, as I haven't networked FileMaker yet. I was going by two powerful posts (one from Andy Gaunt and one from CobaltSky) that I read (and kept in my Networking folder) because I think it's pretty important. And I pretty-much took it as fact.

Speed of Changing All Records - Andy Gaunt

Insert Text in All Fields - CobaltSky

I hope this helps clarify my statement and my expressed concerns about Replace Contents.

LaRetta

[PiedPiper]

Well now i don't know what to do. I gueess it's best to assume it won't catch errors and rewrite the scripts. Has anyone tested this or do they know for sure its true because the first doesn't really say it won't work. Any Cobalt Sky says that. He's helped me before out of jams so I think he's right but I would like to know for sure? Now I wonder what my text really looks like. I guess I'll call FileMaker. We've been networked for a month running these scripts. And I thought Set Error Capture On and If Status Current Error = 301 would tell me.

Any more surprises out there about field locking that File Maker hasn't told us? Anything else that won't produce errors that should?

Pete

[huscat]

We have a script that runs every few hours to update our ebay auctions, and it pulls the results into a single user database, and then pushes the results into a multiuser Auctions database one at a time. Problem is, if someone is in one of the records in Auctions, the script just stops and says "Can't update current record." and then the whole script is hung until the record unlocks.....

Is this the procedure for getting around that? Also, who is a good company to hire to do FM programming. We have a very extensive setup for our ebay business, but want to do a couple of things, that frankly, I don't have the desire to write myself. Thanks!

www.dealexpress.com