Are you sure you only want three digits for this? We invoice nearly a 1000 invoices in a day on busy days, so I'm wondering if 999 invoices are sufficient for a year's business.
I wouldn't use this for a primary key to link invoice records to other tables. I'd keep a separate serial number field for that purpose.
You can set up a serial number field for your Invoice number as an auto-entered serial number that you reset the next serial value at the end of each year. This can be scripted to make the reset automatic.
Then a calculation field like this can return your invoice number:
Right ( Year ( InvoiceDateField ) ; 2 ) & "-" & Right ( "00" & SerialNumberField ; 3 )
Thanks for your help.
Yes, 999 should be more than enough. I work as freelance web developer and last year I had about 30 invoices in total :-)
The calculation you suggest seems reasonable. But how would I reset the counter at the end of each year?
I hope I don't have to do this manually?
I haven't done much scripting yet, so I'd be happy if you can provide a template or point me to a tutorial.
Thanks a lot!
It's actually pretty easy to reset manually. At close of business at the end of a year, you just find the field in Manage | Database | Fields and double click it. You'll get a dialog where you can change the next serial value back to 1.
In a script, you can do this:
Go To Layout [ Invoices ]
Show All Records
Sort [no dialog ; restore ] // specify an order that puts the most recent invoice first
If [ Year ( get ( CurrentDate ) > Year ( InvoiceDate FIeld ) ]
Set Next Serial Value [ YourTable::SerialNumberField ; 1 ]
You can use field options to specify that this script be run every time you open the file.
I'm having a similar issue when generating a new Administrative record.
I'm trying to custom modify a unique ID for the month when a button is clicked.
part of my script creates a new record and sets the field by calculation:
"ADMIN 02" & "-" & Right ( Year ( aPROJECTS::CURRENT YEAR ) ; 2 )]
as a result....i get ADMIN 02-6
I need it to be ADMIN 02-13 as in 2013.
Not sure why it's turnig out as a -6 for year.