Duplicated order IDs

[AndrewBruno]

My order processing system ( FM Server, FM 6.0, Windows 2000, 10 users ) uses the following method of order ID allocation. All record IDs are held in a single record 'parameters' file.

When a new order is raised, Cusomer ID and basic order details are recorded in global fields in the order file. When ready to create the order, a script creates a new record in the orders file, turns global values into record values, sets the Order ID from a master relationship to the parameters file and immediately increments the Order ID in the parameters file by 1.

This method is tried and tested in another system that I wrote, but in this particular system, I have had duplicated order IDs. Two users have raised orders for different customers, with the same ID.They have the same auto-entry order creation time. This has happened twice in over 3000 orders.

The logical reason is that the scripts are running at the same time, the first order ID is set and the second scipt reads the same Order ID before the next line in the first script line increments it. Is this possible ?

I do it this way to have all the serial IDs in 1 place and reduce the risk of wrong numbers when importing data into amended system files. Am I better using the auto-entry serial number within the file ?

AS ALWAYS, THANKS IN ATICIPATION.

Andy Bruno

[Jim McKee]

Hi Andrew ...

Yes, it is possible for two users to simultaneously grab the current OrderID number in your Parameters file, which results in both Orders having the same ID.

You can avoid this by performing a record lock in the Parameters file. This would be a simple subscript in Parameters that performs a "GoToField" step while user#1 is accessing the OrderID and an "Exit Record" step after the OrderID is incremented. I'm assuming that your Parameters file has only 1 record that you access via a constant::constant relationship (1::1).

If user#2 runs the "Assign OrderID" script which attempts to access the record (via the "GoToField" step) before the OrderID has been incremented, a "301" error is returned. You can trap this error in your script and create a loop which will keep trying to access the record in Parameters until user#1 is out of the record.

Since the process of grabbing the current OrderID number is scripted and is very fast (i.e., user#1 isn't "hanging out" in the record in Parameters), user#2 won't even notice the delay -- it will be near instantaneous.

See attachment for one way to implement the record lock test in your own script.

Good luck!

Record_Locking.zip