4 Replies Latest reply on Feb 5, 2013 3:58 PM by blewvelvet

    Invoice number based on Year



      Invoice number based on Year



      I just created my first database with FM (an invoicing solution) but got stuck.

      Each of my invoices should contain the year (2-digits) in which it was issued followed by a 3-digit serial number, i.e. the first invoice in 2011 would be: 11-001.

      How can I get the current year and then add the automatic increment? After one year has passed, the increment should start from zero again, of course.

      If anyone could help, that would be great.

        • 1. Re: Invoice number based on Year

          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 )


          • 2. Re: Invoice number based on Year

            Hi Phil,

            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!

            • 3. Re: Invoice number based on Year

              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 ]
              End If

              You can use field options to specify that this script be run every time you open the file.

              • 4. Re: Invoice number based on Year

                     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.