Autonumbers

[flap14]

Hi
I have an Orders database for which I set the Order_ID field to autonumber. The I screwed up playing around and now Purchase Order 0001 has Order_ID number 2.
Is there an equation/expression I can put in the Validation rule that will make my PO number = Order_ID minus 1?

Thanks

 

[CJ_London]

autonumbers should never be used as anything meaningful other than a way of identifying a particular record. In principle they start from 1 and increment by 1 but this is not guaranteed -they are only guaranteed to be unique (and you will find threads on here where due to one issue or another, even this is not the case).

I would abandon the idea of using autonumber in this way and create your own. Again, there are plenty of suggestions on here - search for 'dmax+1' or 'dmax()+1'- there are other considerations such as number of concurrent users

 

[gemma-the-husky]

yes. In general terms we often end up with two references

1. an autonumber to relate the table throughout the system
2. The "true" ordernumber which is held only in the orders table

there are lots of threads discussing the merits of having effectively two keys - the "real world" ordernumber key, and the "system" autonumber key (also called a surrogate key)

for many of us, the ease of using autonumber keys is worth having. However, you just cannot assume anything it about with regard ot maintaining an intact sequence.

 

[RainLover]

It looks like we are all in agreement here.

Use Auto Number as your Primary Key. This will identify records with a unique number. Then for sequential Numbers like Invoice number use the function DMax plus one.

You should be able to find this in the archives or do a Google Search.

I have a copy if you run into problems.