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

    Max (InvoiceNumber) has stopped working


      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.



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



          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

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

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

              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

                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

                  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

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

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

                      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

                        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


                             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


                             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

                          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.