Upper ( Left ( Customers::company ; 3 ) ) & Right ( "000" & Customers::Serial ; 3 )
Don't forget that every 1000 serial numbers you are guaranteed to duplicate that serial number and Murphies Law suggest that the probabilities of duplicating that serial number on every invoice are 100%...
Anyone care to crunch the possibility for how often or how soon a duplicate will appear?
Also the more active a customer is the more likely the duplicate.
Not to mention the catastrophe that occurs when a meteor hits your server and wipes out... Then you have to rebuild from backups and you forget and the serial number starts repeating as you enter new records which if not entered in the same order....
Will this still happen if I have the first three letters of the customer's surname or company name (if a company) and the last three numbers of their actuall customer id? We create around 400 of these IDs a year so heopfully it wont create an issue. IS there a way of adding to the calc to be a unique number? OR if it exists to go to the next serial? OR do you have any other suggestions? Thank you
The best, simplest option for generating unique ID's is not to use this method, but to use a simple auto-entered serial number instead.
If you do use calculeted Identifiers with leading zeroes, protect your self by making it an auto-entered calculation on a text field and specify a unque values validation--so at least the duplication is caught at the time it happens so you can then take corrective action.
I Agree- however, the existing Sage Pastel program to which the old database used to export customer info to, already used this alphanumerical code...
So I think I am stuck..
I think the key phrase is "used to". if you no longer need to do this, you no longer need to limit your design by using such a method.