On 23 Dec 2011, at 05:51, Peter Buchanan wrote:

> Steve, I understand your solution involving a destination port table with
> one record per port. But it is a multi-user, server environment, and I have
> had problems before where two users grabbed the same serial number using
> this type of "add one to existing record value". In this solution, duplicate
> document numbers would cause huge problems. How to avoid that? Is it
> possible for user1 to lock that port record, add one to serial number, set
> to a variable, commit and exit?

Peter

It is possible to lock a record while grabbing and updating data (such as a serial number).

The reason you got duplicate serial numbers is that Filemaker does not automatically lock records while running a script nor does it run scripts called by multiple users serially. Say you have a script that does something like the following:

Step 1 Set Variable $mySerialNumber = NextSerialNumber
Step 2 Set Field NextSerialNumber = NextSerialNumber + 1

Then if two users run this script almost concurrently, it is possible for Step 1 to run for both of them before Step 2 runs. Not good if you need to ensure unique values!

The basic way to avoid this is to lock the record containing the NextSerialNumber you want to grab (in your case, it would be the destination port record) by actually placing the cursor in a field. This can be any field in the table. You do this with a simple Go To Field step. This step will return an error (can't remember the error number) if another user's script is already there. So an outline of the process is something like:

Error Capture ON
Go To Layout XXX
//This layout must include the field AnyField and it must be enterable//
Loop
 Go To Field AnyField
 If No Error
  //No other user is running the script//
  Set Variable  $mySerialNumber = NextSerialNumber
  Set Field NextSerialNumber =  $mySerialNumber = NextSerialNumber + 1
  Exit Field
  Exit Loop
 Else
 //Collision with another user//
 // So repeat the loop//
End Loop

Obviously, you need some refinements to this, such as to prevent the loop continuing for ever. But that's the basic principle.

Back in the day, I and others tested this with scripts that created thousands of serial-numbered records running concurrently on multiple machines. I never saw a duplicate. Eric Scheid, who hosts the Filemaker Experts mailing list, concurred that this method was reliable. In my book, approval by Eric means it must be pretty bomb-proof....

Does that help? Maybe you can invert those cups again?

Steve