Build your invoice number with a calculation field combining your customer ID and an auto serial number.
Thanks. Which field is going to be the auto serial number: the invoice number? Do I need to have 3 fields: a Customer ID field, another field generating auto serial numbers, then a third field called Invoice number that combines both?
Second thing is I don't know how to tell it to auto generate serial numbers when each customer will be starting from #1, since the auto serial number generation is incremented for each invoice instead of each customer. I hope I'm being clear.
Field Customer_ID = Text string ----- Sample: "JHR"
Field Serial_Number = Auto Serial Number ----- Sample: "1234"
Field Invoice_Number = Calculation: Customer_ID & "-" & Serial_Number ----- Result: "JHR-1234"
If the serial numbers have to be sequential starting and start from 1 for each customer, you probably should have individual related tables for each customer.
Thank you! That works.
Now is a good time to finally ask a question I've had about the auto serial numbers: when you delete a record, the numbers keep incrementing anyway, and I've always had to go back to the manage database and re-do the field. Is there a way for it to know that, so it doesn't skip to the next number?
I'll have to leave that question to someone else. I don't know either. I haven't had a situation were that was required.
There's a script step called Set Next Serial Value and a function called GetNextSerialValue.
You can use these items to set the next serial value for a field to a different value, but you'll need to script with caution as you would only want to reset the serial value if the current next serial value more than one larger than than the record with the largest serial value in this field.
You'd have to handle situations were a record in the middle of the serial number series was deleted and if you are hosting the database so that numerous users might be creating and/or deleting records simultaneously you likely won't be able to do this at all.
Usually, it's not worth the trouble...