Serial number nightmare!

[MarcBauwens]

Hi everyone,

I have an interesting problem with a multi-user database I've written to make prodution sheets.

There are 20 user using this database at the same time. When a user makes a new record a serial is automatically generated by the serial field. This is coupled to a script like the one below.

Allow User Abort [off]

Set Error Capture [On]

Go to layout ["Num_Jobs" (Num_Jobs)]

Insert Calculated Result( GetNextSerialValue (Num_Jobs" ; "Number Job Numeris")]

Set Next Serial Value [Num_Jobs::Number Job Numeris; Num_Jobs::Number Job Numeris + 1]

New Record/request

When a user creates a record and another user creates a record lets say 30 sec to a minute later they get the same serial assigned!

I scratched my head what could be the cause, but running out of ideas. Is there an issue with the script or the field validation?

Thanks for advice, because I'm out of ideas and pressure is building.

[AudioFreak]

You could try adding Commit Record request at the end of the script. What I'm guessing is happeing is the serial number isn't considered entered until the record is commited.

[mr_vodka]

... and this is perciously why this kind of method can not be trusted as serial numbers. You should use FM auto-generated serial numbers only for true unique serial numbers.

[AudioFreak]

Agreed. I used a similiar method for creation of an Invoice Number. But the difference was it was a single user and the user wanted to be sure there were no gaps in the Invoice Numbers.

[MarcBauwens]

Yes, I agree.

The serial is generated by the field, but how do you reset the serial number when a user deletes a record. You can only do that through a script..

Any suggestions?

[comment]

how do you reset the serial number when a user deletes a record

You don't. How could you - when you have 20 users? Suppose User A creates record #5. Immediately afterwards, User B creates record #6. Now User A wants to delete their record (#5). What would you reset the serial number to?

[Vaughan]

"the user wanted to be sure there were no gaps in the Invoice Numbers"

Simple: disable delete. Cancel invoices instead.

I've done similar "centralised serial number" systems before, and none can prevent gaps, as Comment and others have said.

I've stored the serial number in a dedicated, single record "preferences" table set up just for the purpose. Scripts that increment the serial number have to trap for record locking, and be sure to open and close the record when they are done -- do everything quickly to minimise clashes.

You've also go to think about what should happen if the serial number cannot be incremented for, say, 30 seconds: should the process be aborted or should the user be kept waiting while the process continues trying. (To be honest, 30 seconds is way too long, 5 seconds would be more reasonable. A 30 wait would mean that sonething is relly wrong.) If the process is aborted then the new invoice (or whatever) needs to be rolled-back or deleted.

If that all sounds complicated, it's because it is. But it's needed for a robust and reliable system mission-critical system.

Which is why it's best to convince the system owners that invoice or serial numbers with no inherent meaning are the way to go.

[comment]

I don't think it needs to be complicated - provided you either forbid deleting, or allow gaps.

Even re-using deleted numbers should be possible, if a bank of numbers is kept in a pre-generated table (could be script-incremented with each posted record). However, an invoice dated later than the "next" invoice may be considered evidence of accounting fraud.