I usually just use a second field with an auto-increment serial number as my invoice number. Other people will calculate specific invoice number styles,
I've seen date-serial, such as 04082016-0001; or initials-serial like FMI-0001 (where each company has a unique set of initials); or just an incremental number to the company itself instead of all invoices.
It's really whatever your client wants. The auto-increment serial is the easiest, the rest we can tell you how to calc. There is absolutely nothing wrong with having a separate primary key apart from invoice number. In most cases it's preferred.
This may be overkill for your purposes, but let me describe what I do, at least at a high level, and you can tell me if it's something you want to explore.
I hate using auto-enter serial. Data recoveries, merging, etc... They kind of work against what makes UUID so awesome.
Where I do need a sequence (invoices, etc.) I have a NUM table. It basically has two fields: table name and last number used.
In the table that needs a number (let's say INV) I have a calculated field equal to the table name. There's a relationship from INV to NUM, and it allows creation.
On commit, or in my new record script (whichever's appropriate) I have a script that sets the INV_NUM last number field to itself, and checks for an error. We'd get an error if someone else was setting a number. We don't want two users pulling the same number. Loop until you can lock that record without error, or until a specified "give up" time is reached. Once you've locked the record, set last number + 1 into the number field in INV, and increment the last number field in NUM..
An important factor is that you need to do this quickly and then release the num record so another user can get a number. For a more transactional approach, you may want to do this in another window, on a layout based on NUM, so that you don't need to commit INV to commit NUM.
This and other details crop up, and I'm sure I haven't hit all of them, but this should give you the idea.
The beauty of this is that if you're recovering data from a backup, the NUM record has been modified and that data gets recovered like any other. Also, you can give an administrator a dashboard where they see all NUM records in a portal, showing the table names and also the last number used for each.
Like I said, this may be overkill for you. If not, and you have any questions about the details of setting it up, let me know.
I agree with Chris' approach, auto enter serials can become quite a headache and the user has no control over it. I like his method of creating unique numbers. For creating of unique invoice numbers I use a similar but slightly simpler system; I store the last used number for each type of document in one record in a separate table 'Settings'.
Here you can also keep your company logo, address details, vat percentages, all the stuff you need the end user to be able to change but is system wide the same. So it is a good place to keep order numbers, invoice numbers, etc.
By making a cartesian (X) relationship between the settings table and your other tables you can always access the data in the Settings table. You just need to make sure that the user cannot delete or add a record, but that is easily done in Scripting and or Security. (and to be 100% sure I always perform a check on startup if there is only one Settings record)
If you use the serial increment function for generating new numbers the user can also have leading zero's or text in the invoice number. (like INV2016001 or 00001)
Making sure they are unique can be done in various ways as explained by Chris.
Hope this helps, Daniel
Thanks everyone for the responses. Just an amazing amount of help from the community!
. For a more transactional approach, you may want to do this in another window, on a layout based on NUM, so that you don't need to commit INV to commit NUM.
Can look like this:
We offer several numbering options at once. The user can edit them with variables, add new ones (Neu +), delete unused numberings (Entfernen -) and call numbers (Verwenden), which are automatically counted up. The window is closed then.