9 Replies Latest reply on Jul 3, 2017 10:02 AM by philmodjunk

    Max (InvoiceNumber) has stopped working

    pietergorisio

      Hi fellow FM'er,

       

       

      I HAD a smooth running invoicing numbering which I messed up somehow.

      I don't know why but my Max (InvoiceNumber) stays empty. And I don't see the difference compared to backup previous versions.

       

      My field InvoiceNumber is set to 1

      When I create a new invoice it runs this script:

       

      Screen Shot 2017-07-01 at 23.35.17.png

       

      This is the relation between the two table occurrences:

       

      Screen Shot 2017-07-01 at 23.36.55.png

       

      Somehow the s_MaxInvoiceNumber always stays empty.

      Anyone any idea? Might be something silly but I just don't see it.

       

      All help is much appreciated.

      Regards,

      Pieter

        • 1. Re: Max (InvoiceNumber) has stopped working
          planteg

          Hi,

           

          we don't see how the s_MaxInvoiceNumber field is defined. Could the issue lies in there ? We are missing information here to help you.

          • 2. Re: Max (InvoiceNumber) has stopped working
            bigtom

            What is your actual Max number? Is it an unstored calculation?

            • 3. Re: Max (InvoiceNumber) has stopped working
              pietergorisio

              Sorry, this should give you the requested information correct?Screen Shot 2017-07-01 at 23.52.35.png

              • 4. Re: Max (InvoiceNumber) has stopped working
                BruceRobertson

                For one thing, summary fields deal with the found set.

                What is the found set when you are doing this operation?

                Summary field performance gets worse as the found set gets larger.

                The way you are using the summary function requires you to have a found set of all records.

                (Or; a found set of highest-number records)

                What are you really doing here?

                1 of 1 people found this helpful
                • 5. Re: Max (InvoiceNumber) has stopped working
                  pietergorisio

                  Hi Bruce,

                   

                   

                  Actually I followed a complete online course to get to this invoicing solution. I made changes but the core is build around the course I followed.

                  So what I need the solution to do is the following:

                   

                   

                  When I select "Invoice" from dropdown field "Invoices::Type" I need the "InvoiceNumber" to increment by 1.

                  When I select "Quote" from dropdown field "Invoices::Type" I need the "QuoteNumber" to increment by 1.

                  When the record is made I need it to be a quote standard. Which can be transformed into an invoice without having to make the entire thing from scratch.

                   

                  I can post my complete solution here if you needed.

                  • 6. Re: Max (InvoiceNumber) has stopped working
                    bigtom

                    You may try an unstored calc field with Max(InvoiceNumber) and see if that helps.

                    • 7. Re: Max (InvoiceNumber) has stopped working
                      philmodjunk

                      Looks like your self join may have problems. Check to see what matcing records you are getting and what data is in that invoice number field.

                       

                      Contrary to what others have posted here, your summary field is returning a max value based on a set of related records if I am correct that your layout is based on invoices as you are then referring to a summary field  from a related table occurrence.

                      • 8. Re: Max (InvoiceNumber) has stopped working
                        pietergorisio

                        Would it make sense to keep a Invoices::MaxNumberInvoice (kinda like a counter field) and base the Invoices::InvoiceNumber on that +1 if Invoices::Type="Invoice".

                         

                        In Belgium (my home country) all Invoices must be numbered serial. So if you delete record 24/24 the next invoice that you make should be 24. Unless it already exists and has the number 25.

                        I don't think auto serial works in that case?

                        Also a quote number should be easily transformed into an invoice so it automatically gets the last available number as InvoiceNumber.

                         

                        I would make a script that

                         

                        If Invoices::Type="Invoice"

                             Set field Invoices::InvoiceDate = Get (CurrentDate)

                             Set field Invoices::InvoiceNumber = Invoices:MaxNumberInvoice + 1

                        Else

                             Set field Invoices::InvoiceDate=""

                             Set field Invoices::InvoiceNumber = Invoices::MaxNumberInvoice - 1

                        End If

                        If Invoices::Type="Quote"

                             Set field Invoices::QuoteDate = Get (CurrentDate)

                             Set field Invoices::QuoteNumber = Invoices:MaxNumberQuote + 1

                        Else

                             Set field Invoices::QuoteDate=""

                             Set field Invoices::QuoteNumber = Invoices::MaxNumberQuote - 1

                        End If

                         

                         

                        Like to hear your opinion on this.

                        Much appreciated.

                        • 9. Re: Max (InvoiceNumber) has stopped working
                          philmodjunk

                          The main issue is what happens if more than one user creates invoices at the same time. With both a "counter field" and the current method, it's possible for you to get two invoices with the same number simply because they got either the same "max value" or the same counter field value to which the script then adds 1. Make sure to at least include a unique values validation on that field.

                           

                          But I'd recommend that you not allow an invoice to be deleted. Mark it "void" instead and design your solution to work with that accordingly. Then you can sequentially number your invoices and still account for those where a mistake would otherwise have caused you to delete the invoice.