9 Replies Latest reply on Apr 6, 2013 9:05 AM by MICHAELAE-V

    Formula Help for Invoice Number

    Hybridnath

      Title

      Formula Help for Invoice Number

      Post

      Hi

       

      I am in the process of moving over to FileMaker Pro 10, which is great.  Previously on my Invoice numbers I follow the Year and Month then number the invoice.

       

      E.g.  The first 2 invoices in April 2010 would be 100401 then 100402.  May then becomes 100501, 100502, 100503 and so on.

       

      I have tweaked the invoice from the template, and this is one of the last areas I am struggling with.  Can anyone suggest how to do this please.

       

      Many thanks in advance.

       

      Nathan.

        • 1. Re: Formula Help for Invoice Number
          philmodjunk

          Can you be more specific. From what version are you moving?

           

          Do you only have one invoice per day? (I would think not) How do you distinguish between different invoices printed on the same day?

           

          Regardless of which version of filemaker you use, the best solution may be to not number your invoices this way and stick to a simple series of incrementing numbers. You can have a date field that records the date and a simple auto-entered serial number is much safer to use as the primary key linking your invoices to related records in other tables.

          • 2. Re: Formula Help for Invoice Number
            Hybridnath

            Hi Phil.

             

            The last 2 digits are number of invoice that month.  (Not usually over 99 in my case).  I think maybe your correct, change methods.  In the early days I used to create invoices using Word/Excel but since finding FMP, I am setting this up as we speak.  I got the number method from a basic business setup meeting and used to input this manually.  Since seeing how good FMP is for my company, I am making the switch as it is perfect.  I just wanted to keep the same invoice numbering system that I was taught.  

             

            I thought maybe you could take the YearMonth from a date field and have the last 2 digits change sequentially.

             

            Sorry I am a bit brief, been an Excel Junky for a while and trying to learn new methods.

             

            Many Thanks

             

            Nathan.

            • 3. Re: Formula Help for Invoice Number
              philmodjunk

              You can do exactly what you describe, it's just more trouble than it's worth. Here's a compromise that may work for you.

               

              Define a date field that auto-enters the date each time you create an invoice. (In some business operations, you may want to script something that records the date the invoice is printed rather than the creation date.)

              Define a number field in filemaker that functions as an auto-entered serial number.

               

              Define a calculation field as Right ( Year ( DateField ) ; 2 ) & Right ( "0" & Month( Datefield ) ; 2 ) & SerialNumberFIeld.

               

              Use this field on your layouts for printing and display purposes, but use just the serial number  field for your relationship linking an invoice record to other tables such as a line items table.

               

              This does everything but restart the count with each month and is fairly easy to set up.

              • 4. Re: Formula Help for Invoice Number
                raybaudi

                Hi Phil

                 

                "and a simple auto-entered serial number is much safer to use as the primary key"

                 

                where he said that that number is used as a primary key ?

                • 5. Re: Formula Help for Invoice Number
                  philmodjunk

                  He didn't, of course, but we need to make sure that that fact is clear--both for our OP and for any other new user that might read this thread.

                  • 6. Re: Formula Help for Invoice Number
                    Hybridnath

                    Thats Great.  Many Thanks for your help. I will put that into practice.

                     

                    Thanks for all your support.

                     

                    Nathan.

                    • 7. Re: Formula Help for Invoice Number
                      mootles22

                      Hi there,

                      Our invoices are very similiar to this in that we use MonthYearInvoiceNo.

                      ie. invoice 1234 created May 2008 would be 05081234 but our invoice number doesn't reset every month so each invoice still has it's own unique number in the last four digits but if someone rings up, we have the first four digits and immediately know where to look in terms of month/year.

                      I'm assuming PhilModJunk saying this is exactly what we want? Just swap month and year around?:

                      "You can do exactly what you describe, it's just more trouble than it's worth. Here's a compromise that may work for you.

                       

                      Define a date field that auto-enters the date each time you create an invoice. (In some business operations, you may want to script something that records the date the invoice is printed rather than the creation date.)

                      Define a number field in filemaker that functions as an auto-entered serial number.

                       

                      Define a calculation field as Right ( Year ( DateField ) ; 2 ) & Right ( "0" & Month( Datefield ) ; 2 ) & SerialNumberFIeld.

                       

                      Use this field on your layouts for printing and display purposes, but use just the serial number  field for your relationship linking an invoice record to other tables such as a line items table.

                       

                      This does everything but restart the count with each month and is fairly easy to set up."

                       

                      Cheers,

                      SK

                      • 8. Re: Formula Help for Invoice Number
                        FranzLang

                        Hi,

                        I am new to FileMaker and have a similar problem.

                        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: 11001.

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

                        I tried to figure out some of the calculations above, but I don't get what "Right" does and I can't find any documentation on it.

                        Thanks a lot for your help.

                        • 9. Re: Formula Help for Invoice Number
                          MICHAELAE-V

                               HOW DO I ADD  INVOICE NUMBERS TO MY INVOICES I WANT MY FIRST INVOICE TO START 121

                               I am using Filmaker pro 11.

                               Also How do I change my currency from € to £