Hello. I would like to make an invoice system with running number document starting YEAR, MONTH, and then 000 (three digit from 001-999)
and then i want the digit to start all over again on the new month.
Is there any possible way to do it?
I’m making up some column names here, and you’ll have to enter your own, but assuming you have a table Invoices with columns Date (the date of the invoice), DateCreated (automatically generated with a Created On formula), and InvoiceNumber
You can use this formula for your invoice number:
concatenate(thisRow.Date.Year().Right(2), thisRow.Date.Month().ToText().LeftPad(2,"0"),
totext(thisTable.filter(CurrentValue.Date.year()=thisRow.Date.Year() AND CurrentValue.Date.Month()=thisRow.Date.Month() AND CurrentValue.DateCreated<thisRow.DateCreated AND CurrentValue!=thisRow).Count() + 1).LeftPad(3,"0"))
However! This will get screwy if you delete an invoice for any reason. Because this formula generates live,if you remove a previous entry, the invoice number would change which is obviously no good.
For that reason, I would use a button to generate the invoice number on demand, so it only changes when you ask it to.
HI Korn,
The example below stores an invoice number in a separate table. It eliminates the problem with deleting/archiving invoices from the sales table.
It currently makes use of a single sequence. You would need to do a check in the button that creates the new number to see whether the year and/or month has changed, and then start a new sequence.
And these pages explain how to rank() rows in a table so that you can work with the previous row:
Regards
Rambling Pete
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.