Mainaining integrity of ID numbers

[Inky Phil]

I have a table called system that contains one record with all my next id numbers etc

If I want the next invoice number for example my script would be along the lines of

set field invno to system::nextinvno.

set system::nextinvno; system::nextinvno+1

In a multi-user environment with the solution run from FM Server should 2 users attempt these script steps at exactly the same time would it sort itself out and form a nice orderly queue or is there something I should be doing to error trap for this ?

I know this is highly unlikely but I just thought I better be on the safe side

TIA

Phil

[Reed]

why not just use auto-enter serial numbers instead of stored field values? If you need to use the values in a calc or script, just use the GetNextSerialValue() function, and use the Set Next Serial Value [] script step to change them if needed (with data imports for example.

[Inky Phil]

Thank you Reed

Phil

[comment]

I agree with Reed. You're not showing us the whole picture here (when are records created and committed, etc.), but just from the snippet: if one user is modifying the system record, it is locked for another user. So the second user might not be able to raise the number at that time, leaving an opportunity for the next user to create a duplicate.

Highly unlikely * time = bound to happen.

[Inky Phil]

Yeah, sorry reed and comment.

On reflection my question, out of context, is a little vague.

Unfortunately, I am pondering my structure and I am not too sure of the context myself at the moment :. Once I have sorted my thoughts out I will come back again. Thanks for taking the trouble to reply. I will be back as soon as my brain stops hurting

Phil

[Inky Phil]

Back again,

Here is another stab at explaining what I would appreciate advice on. I will use the invoice routine as an example.

I am preparing an invoice. The invoice per se is not a record, it is a layout that will consist of a number of line items which are records.

Prepare the line items to be inc. in invoice

Allocate the 'next available' invoice number

Preview the invoice,

Print the invoice,

Record the invoice number in each line item record

File those records

Move on..

I used the invoice routine as an example because it raises a number of issues:

What is the best practice for storing these 'next available' numbers as I have a few different ones ie next job no, next description no etc and the last used numbers might be stored in a record that is anywhere in the system ?

Secondly ( and this takes me back to my original post), in a multi user system how does one GUARANTEE that the same number does not get picked up by two people ?

Lastly, and I realise that this is possibly a request too far but, if you don'task...

Is it possible that should you allocate a number and then that number is not used (eg invoice abandoned)that the number could be re-allocated to the 'next available' list

Ignore the last one if it is too involved but I would appreciate the benefit of anyones experience with the first two points. I am rewriting my solution because of my naiivety when I wrote it the first time and whilst I am no longer a beginner I am still an amateur but I would like to get it as near to right as possible this time :

TIA and Kind Regards

Phil

[Ender]

Hey Phil,

I'd strongly recommend using an Invoice table in addition to your Line Items table. Not only is it important for creating a link that groups the Line Items (and allows them to be easily linked to other entities,) but it's also the best place to put data that applies to all Line Items, like the Order Date, Ship Date, and Total.

The best way to avoid problems with sequential ID numbering is to use the auto-enter serial numbers. In this case the Invoice table would have an Invoice Number with an auto-entered serial number. While other methods for guaranteeing unique sequential IDs might be thought up, the ones I've seen risk record locking problems in multi-user environments.

I would also avoid any ID reallocation scheme, as this also introduces complexity, risk of duplication, and record locking. Instead, if an invoice is abandoned, mark the invoice as "Void" and keep it in there.

[Inky Phil]

Ender,

Thank you for your advice.

The addition of the invoice table would also address a few other issues I have. It is obviously the best solution now that someone has pointed out its merits.

As a non professional I never cease to be amazed at some of the practices are offered in here. Many is the time that I have thought 'Blimey, that would have taken me 4 globals and 3 scripts to achieve the same as that 1 line calc'. With this in mind I was trying to do this with one less table but on this occasion that is obviously not the way to go

Thanks again for your contribution

Regards

Phil

[Ender]

No problem. Remember: tables are your friend. :wink2: