9 Replies Latest reply on Jul 31, 2016 10:39 AM by philmodjunk

    Increment Number by 1




      I have the following problem with my invouce app.



      This is the scenario




      Text-Field Invoice-Number

      Datefield Invoice_Date


      Invoice_Date is filled with the current date

      Get (CurrentDate)


      Invoice_Number should be filled with the text

      "Invoice[space][current year]-[space]"

      then there should be a number added with the following requirements

      1. If a new year begins, the first in voice in that year should start with "1"

      2. During the current year, the number should increase by 1 with every new invoice


      I was using the following solution which did not work properly



      & " "

      & Year ( Get ( CurrentDate ) )

      & "- "

      & If ( Year ( Get (CurrentDate)) > Year ( Max (Invoice_Date)); "1"; SerialIncrement ( Invoice_Number; 1 ))


      Unfortunately this does not work.

      Any ideas ?



        • 1. Re: Increment Number by 1

          If you want to use the Max( ) function you need it to look at a range of records. As you have it now it is only looking at the current record, so of course the Max will be whatever the value is in that record.

          To get it looking at a range, you will need to create a relationship to a new TO, via the year. I think you'd need to make an invoiceYear field that parses out the year from the invoice date—make it an auto enter field that uses Year ( invoiceDateField ) as its formula. Then you would create a self-join relationship (i.e. to that same table) matching that invoiceYear. Then in your calculation set the Max function to look at the date through that relationship filter and it should work.

          • 2. Re: Increment Number by 1

            It's not really clear what you are trying to do. As @keywords said, the MAX function doesn't really work as you would expect without some extra work.


            I tried to create a simple application, but wasn't exactly sure what you expected as output. This isn't difficult, but don't code before you can solve something (at least a simple case) on the back of, say, an envelope. Going to the computer too early will only cause frustration. (The computer, programming, should be the easy part with proper preparation).


            Try to create some test data how the code should work first.


            If you have FMPA, step through your code in the debugger.



            Consider forgetting about the MAX function and just use SQL instead:


            ExecuteSQL ( "SELECT MAX(YEAR(INVOICE_DATE)) FROM <TABLE_NAME>" ; "" ; "" )

            • 3. Re: Increment Number by 1
              Markus Schneider

              serialincrement is a custom function (?)


              Why not just define a field as 'serial number', incremented by 1 and use that fir the string? SN can be scripted for a new year..


              the above is nonsense, just found an identical posting in the german section of the community

              • 4. Re: Increment Number by 1

                Such systems of invoice numbers are rods to beat yoursef with.


                Build a table to hold InvoiceYears, where each record is a year. The record has a field that contains the invoice number. Increment this number being sure to trap for record lock errors.


                The reason for keeping previous year's invoice number in a table is because just after 1/1 there will be a need to enter both new year invoices and previous year invoices. Trust me on this.

                • 6. Re: Increment Number by 1
                  Markus Schneider

                  ouch - language problem, no english version here )-:

                  • 7. Re: Increment Number by 1


                    FortlNrInTextÄndern = SerialIncrement




                    p.s. see the /15/fmp/... in the above link? change it to 'it', 'fr', 'se', etc. So far, I've found the help to be well done for other languages, just by changing the country-code.

                    • 8. Re: Increment Number by 1
                      Markus Schneider

                      first thought that it's a cf - I fully misunderstood the question, sorry


                      (I got the manuals in en and de on my iPad, but did not check. No FM here at the moment)

                      • 9. Re: Increment Number by 1

                        If you aren't going to use FileMaker's build in auto-entered serial numbers, care must be taken if this is.a hosted solution with multiple clients creating invoices. It's quite possible that two clients will produce invoice numbers that are identical if they both create invoices at the same wrong instant. The more users you have, the more likely this becomes.


                        Setting up a field as an auto-entered serial number that is either manually or via script reset at the start of each year would thus be far safer than using either the Max or a related table of max values to produce this increment.


                        There is also a technique that uses an auto-entered serial number to produce a second value "on command" that might be used, but it seemus over complex for the purpose described here.