11 Replies Latest reply on Jan 19, 2014 8:28 PM by philmodjunk

    How to find next invoice number?

    fabiuz

      Title

      How to find next invoice number?

      Post

           Hi,

           I am lost in a stupid thing: I have an invoice table, each invoice have an ID a date and a number.

           I want to know wich will be the next invoice number when I will create a new one.

           So the first thing I have to know is which is the date of the last invoice, may be the max date in table.

           In this way I should be able to find max invoice number too and be sure that it is relate to the current year.

           I have created a selfjoin table using invoiceID and a calculation field to find the max date. Then I have created one more TO of Inoivce table related by maxDate field and date field. This way I should be able to see all invoices that have the same date when the date is the most recent one.

           Next I have created another calculation field to get the maxInvoiceNumber of the last invoice TO.

           The problem is that such field does not return any value but an empty field :-(

           My suppositions are broken somewhere and I am unable to find where... can anybody give me some suggestions to go out of here?

           thankss!!

      invoice_table.gif

        • 1. Re: How to find next invoice number?
          philmodjunk

               Why can't your invoice number field be an auto-entered serial number so that it automatically enters the next number just like InvoiceID? If this value needs to with 1 each new year (I can see that you aren't using it as the primary key), You can either manually or via script reset the next serial value setting back to 1.

          • 2. Re: How to find next invoice number?
            fabiuz

                 thanks for the reply,

                 yes I could do this way too but I have the same problem at the beginning of each year and I do not want to manually change next serial number value.

                 I can script something but I have to check max date and current date to calculate if I have to reset counter. Could be easier if you automatically run the script for each new invoice.

                 My way should work too I think, I have got some different result, seems correct, by changing the calculation: I have unchecked the option "Do not evaluate if all referenced fields are empty". In this way for some reason the max number field works.

            • 3. Re: How to find next invoice number?
              philmodjunk
                   

                        I can script something but I have to check max date and current date to calculate if I have to reset counter.

                   That's actually a pretty simple script.

              • 4. Re: How to find next invoice number?
                fabiuz

                     Yes, I suppose there is an action to reset or to set the next serial number value, I didn't use it never till now.

                     About my solution, I am not sure if the global storage is correct for my calculation fields and I don't understand why by uncheking the "Do not evaluate if all referenced fields are empty" option in calculation things seems to work.

                     Calculation should return always current data results and in my relations there is always data to compare. Why does it make difference?

                • 5. Re: How to find next invoice number?
                  fabiuz

                       ..anyway my solution still doesn't work.

                       So I am trying to apply yours but I have problems too :-(

                       1. If I create a new Invoice and I delete it, the next serial number will be wrong because it goes to the next while the previous one was not used.

                       2. I cannot fin any layout script trigger that could check the invoice number when I start to inser a new Invoice.

                       3. I suppose the action to use is "Set next serial value" but I have to find a script trigger to call even when I delete a new invoice that I have started and not completed.

                        

                       Suggestions?

                  • 6. Re: How to find next invoice number?
                    fabiuz

                         ..so I have created two buttons and two scripts to manage such situations. But again I don't feel comfortable with this solutions:

                         - If I need to know which was the last invoice number of previous year I still need an approach similar to mine;

                         - I cannot set the invoice number to be unique so it could happen that for a mistake two invoices of the same year have the same number;

                         - I have to add buttons that call scripts, but if I use the status bar default buttons all my work is not useful because scripts will not work.

                         If I can make work my solution, I should not have all these possibile mistakes and I do not have to script anything.

                         What do you think about?

                    • 7. Re: How to find next invoice number?
                      philmodjunk

                           Global calculations evaluate according to really funky logic. They update to compute a value from the most recently modified field for any field that they reference so this value can be very misleading. A Global storage option should not be needed for this.

                           Your method needs a relationship that matches Invoices to invoices by year rather than invoice number:

                           Invoices::cYear = Invoices|SameYear::cYear

                           Where cYear is defined as Year ( date ) and is defined to return a number data type.

                           Then Max ( Invoices|SameYear::invoiceNumber ) + 1

                           can compute the next number in the series. Be sure to clear both "do not evaluate if all referenced fields are empty" and "Do not replace existing value" check boxes.

                           But here's a script that can run automatically to reset the next serial value:

                           Define a summary field, sMaxInvoiceDate, in Invoices to compute the maximum of Date to use with this script.

                           Go to Layout ["Invoices" ( INvoices ) ]
                           Show All Records
                           If [ Year ( Get ( CurrentDate ) ) > sMaxInvoiceDate // time to reset the serial number ]
                              Set Next Serial Value [ Invoices::InvoiceNumber ; 1 ]
                           End IF

                           Use File options to set this script to run "onFirstWindowOpen". It will run every time the file is opened, but will reset the serial number only at the start of the new year. If you host this file with FileMaker Server, you can also use a server schedule to run this script once a day.

                      • 8. Re: How to find next invoice number?
                        fabiuz

                             many thanks for the reply, it is really frustrating for me to be still here searchin for a solution of this little thing!

                              

                             I will try your suggestion to make work my solution, about yours I am using FM 11 Pro Adv, no server at all and may be I do not have the onFirstWindowOpen event, may be I could eventually try with the file start up script, but it still seems a more complicated way for me.

                        • 9. Re: How to find next invoice number?
                          philmodjunk

                               The option is still there in FileOptions for FileMaker 11, but it's not identified as a script trigger. You use the option for performing a script when the file is opened--which is the same thing with a different name.

                          • 10. Re: How to find next invoice number?
                            fabiuz

                                 Ok, thanks.

                                 At the end your fix to my solution does the job and seems easier to me.

                                 File start up script is a problem for me too if I use it on mobile device with FM GO: for some reason it will uncheck such start up script when I copy back the database to my mac.

                            • 11. Re: How to find next invoice number?
                              philmodjunk

                                   if I use it on mobile device with FM GO: for some reason it will uncheck such start up script when I copy back the database to my mac.

                                   That is odd. I've not seen that behavior with FM GO 12 and 13. The Known Bugs List database the I created and maintain uses such a script to select different layouts depending on whether FM Go or FM Pro opened the file.