Just keep in mind if you do incremental counts that if you permanently delete a record, you may inadvertently create duplicate invoice records.

For this purpose, itâ€™s good to have some ongoing ledger. Or to have a mechanism to mark invoices as user deleted, but not actually deleted from the database.

Also, you want to format it as a text (numbers only). If you save it as a number, leading zeros will be omitted.

You need not one, but two fields, the first of type number, the second of type text.

You do the calculations and incrementations on the number field only.

Once you have the right n-digit number for your invoice, you transform it into a n-letter text element.

You take the n-letter text element and superpose it on the last n zeros of the generic text element â€ś0000000â€ť, for instance â€śxyzâ€ť and â€ś0000000â€ť gives â€ś0000xyzâ€ť

You store the text â€ś0000xyzâ€ť in the text field.

Example: extract the invoice number 67 from the number field => transform into text element â€ś67â€ť => superpose the two texts â€ś0000000â€ť and â€ś67â€ť right-aligned to give â€ś0000067â€ť => store the latter in the text field.

Note that numbers and texts are different formats of variables, you cannot mix them, but have to transform one into the other before doing operations on the pair. The text â€ś0000000â€ť transformed into a number gives 0, but the number 0 transformed back into a text gives â€ś0â€ť.

Yes, what youâ€™re saying is correct. So long as you are referencing a discrete field on the last invoice, you shouldnâ€™t encounter unintended duplicates. @ones comment clarified the point.

My initial comment was based on the logic of a :count, function.

I had the same problem. I just store the invoice number in the database as a number which makes it easy to increment and use in searches. I always keep the last used invoice number in a database as well. Then when I need to display the number, like in a text field, I do zero padding as follows:

First, take the invoice number and add a number thatâ€™s one larger than the maximum possible invoice number. Example: Add 10000000 to invoice number to represent an invoice number from 1 to 9999999.

Then format the invoice number as a number with 0 decimal places. This turns the number into a string.

BTW: The Regex pattern .{7}$ will extract the 7 least significate digits. Just change the number to match the number of digits to extract from the number string.