Take a look at the "SerialIncrement" function. Once the first number is made it will make the next number.
I strongly recommend that you NOT use this value as your primary key to link to other records in relationships. Use just an auto-entered UUID or serial number as your primary key.
Either do not use this method at all or only use it as a "label" field in the invoice table.
And note that you will have to take extra precautions in order to avoid getting duplicates if it is ever even remotely possible that two users might create invoices for the same customer at the same time.
Thank you. i have considered this function. For the whole number, i should use a text field/calculation field, right?
do i need to create a script to check the duplication of the invoice number?
thank you for your advice. i will use another text field to get the UUID as the primary key.
a = "N" & Filter ( client's number; "0123456789") & "-" & AA & "-" ;
b = "I0";
c = 0
; SerialIncrement ( a & b & c ; 1 ))
i propose to use this calculation, but it seems not working as it allows duplicate invoice number
You have to get the most recently used serial number or the max of the numeric part of the serial number taken over all the records for a given customer.
And this does not prevent duplicates if two users try to create a new invoice for the same customer at the same time. You'll need to set up a unique values validation on the field.
I would use use a number field for just the numeric part of the string so that either a self join by customer ID or ExecuteSQL can be used to get the maximum value. A script step or field calculation can then compute the max plus 1 value and put it in this field. Your invoice number field can then be a text field that has unique values specified and that concatenates values to produce your invoice number.
For those who might read this discussion in search of an answer, there are really only a few reasons to use such an "encoded data" serial number:
To support a legacy system that relies on it.
To produce data in a compact label that humans need to read and decode without looking up an ID or scanning a bar code.
The client/boss insists on it despite all efforts to talk them out of it.
Thank you. i would like to ask a couple question. how to i get the most recently used serial number? using a script to find the last record with same client's number?
For the second method, the maximum number, it should be the maximum of a given customer? right
If there are customer with N1234-BB-I07 and they would like to create a record with N1234-AA-I06, is it possible to autogenerate?
Otherwise, it may be much easy to select the last part by user.
If you look at the field through a self-join relationship that looks at all records in the table via a cartesian join, you can use the Max ( ) function.
Don't use a Cartesian self join use a relationship that links to all invoices for the same customer and then either th last or max function can get the value to which you add 1.
Could also be used.