Problem with auto generation

[tau zi]

I have my database set up to automatically generate a serial number upon creation of a record. The problem i am having though is that if a coworker or myself makes a mistake and inserts and then deletes the record, the next inserted record skips the deleted number and goes to the next. How do i set it up that it does not skip to the next number? I would like to be able to add a new record that then follows the previous record without any conflict with a deleted record.

Please Help.

[Reid]

Sounds like you would then have a field called record_id that would be the autonumber. This number would act the same way as you are describing. If the sequence is absolutely necessary then you can have another field that is a calculation of 1 more than the previous field.

From what I've seen the autonumber acts that way to make it impossible to have duplicates. There's no real way around it except for the way I described above.

I'm sure someone will correct me if I'm wrong. :-)

[comment]

Be careful what you wish for: suppose user A created record 301, then deleted it. Meanwhile, user B created record 302. What should be the next record's number? If you say 301, you will have a discrepancy between serial numbers and creation order.

The best way to deal with this, IMHO, is to assign the serial number on commit, and prevent users committing new records casually.

See also:

http://fmforums.com/forum/showtopic.php?tid/216331/post/363809/#363809

[tau zi]

As long as i can find record 301 it wont matter. How do i set it up so that the next record does says 301 instead of 303. Im pretty new to the software could you walk me through it?

[Vaughan]

I would like to be able to add a new record that then follows the previous record without any conflict with a deleted record.

Take note of what Comment has asked:

A user makes record 301.

Another user makes record 302.

First user deletes record 301.

Another user creates a new record: what should it be?

If you say 301 then the records will be in this order:

...

299

300

302

301

303

...

Are you happy for record 301 to be out of order?

[LaRetta]

As long as i can find record 301 it wont matter.

What is record 301? If you ask a co-worker what 301 represents, what would they say? They wouldn't have a clue what the number means. What matters is that you can find a record according to what humans track - name, phone, product, etc.

Since these are auto-generated, they aren't even part numbers that Users might know. And the worst thing to do is require that your people keep lists with, for instance, client name and number next to it and then have to search for number.

Leave the auto-generated numbers alone. They mean nothing so what difference does it make if they have holes? :wink2:

[comment]

Well, if record 301 can be out of sequence, then so can record 50 - if the original happens to be deleted - so you could end up with:

...

299

300

302

301

303

50

304

One cannot help wondering why is it important for the series to be contiguous, but not sequential.

In any case, I don't know of a way to do this reliably other than by pre-generating the serial numbers in another table, then assigning them on a "first-come, first-served" basis. This too would have to be scripted - and scripted VERY carefully to prevent assigning the same number twice, when two users are creating a new record at roughly the same time.