9 Replies Latest reply on Feb 7, 2017 1:25 AM by philmodjunk

    AutoGeneration of Invoice Number

    sam0723

      Hello everyone, i am new to filemaker. Recently, i am working on the invoice database.

      The invoice number is in the following format, T1234-AA01-I01

       

      where T1234 is the combination of T and C1234(client's number).

      AA is the type of invoice, there are 4 of them AA, BB, CC, DD and the 2 digits are the type number.

      The final symbol is Invoice number "I" and the 2 digits are the invoice number.

       

      The combination of all the segments are the final invoice number we would like to generated.

       

      i think it is not possible to auto-generate the number as the type really depends on the user selection.

       

      For the first part, T1234. It could be generated by "T"& filter(client's number ; "0123456789")

      For the second part, it is selected by the user.

       

      My question is for the third part, how could it be automatically generated with respect to the same client.

       

      For example, client A with client's number C1234 could have several invoice number, T1234-AA01-I01, T1234-AA01-I02, T1234-AA02-I01,

      T1234-BB01-I01.

       

      i am using filemaker 15.

        • 1. Re: AutoGeneration of Invoice Number
          greatgrey

          Take a look at the "SerialIncrement"  function. Once the first number is made it will make the next number.

          • 2. Re: AutoGeneration of Invoice Number
            philmodjunk

            I strongly recommend that you NOT use this value as your primary key to link to other records in relationships. Use just an auto-entered UUID or serial number as your primary key.

            Either do not use this method at all or only use it as a "label" field in the invoice table.

            And note that you will have to take extra precautions in order to avoid getting duplicates if it is ever even remotely possible that two users might create invoices for the same customer at the same time.

            • 3. Re: AutoGeneration of Invoice Number
              sam0723

              Thank you. i have considered this function. For the whole number, i should use a text field/calculation field, right?

              do i need to create a script to check the duplication of the invoice number?

              • 4. Re: AutoGeneration of Invoice Number
                sam0723

                thank you for your advice. i will use another text field to get the UUID as the primary key.

                • 5. Re: AutoGeneration of Invoice Number
                  sam0723

                  Let ([

                  a = "N" & Filter ( client's number; "0123456789") &  "-" & AA & "-" ;

                  b = "I0";

                  c = 0

                  ]

                  ; SerialIncrement ( a & b & c ; 1 ))

                   

                  i propose to use this calculation, but it seems not working as it allows duplicate invoice number

                  • 6. Re: AutoGeneration of Invoice Number
                    philmodjunk

                    You have to get the most recently used serial number or the max of the numeric part of the serial number taken over all the records for a given customer.

                     

                    And this does not prevent duplicates if two users try to create a new invoice for the same customer at the same time. You'll need to set up a unique values validation on the field.

                     

                    I would use use a number field for just the numeric part of the string so that either a self join by customer ID or ExecuteSQL can be used to get the maximum value. A script step or field calculation can then compute the max plus 1 value and put it in this field. Your invoice number field can then be a text field that has unique values specified and that concatenates values to produce your invoice number.

                     

                    For those who might read this discussion in search of an answer, there are really only a few reasons to use such an "encoded data" serial number:

                     

                    To support a legacy system that relies on it.

                     

                    To produce data in a compact label that humans need to read and decode without looking up an ID or scanning a bar code.

                     

                    The client/boss insists on it despite all efforts to talk them out of it.

                    • 7. Re: AutoGeneration of Invoice Number
                      sam0723

                      Thank you. i would like to ask a couple question. how to i get the most recently used serial number? using a script to find the last record with same client's number?

                       

                      For the second method, the maximum number, it should be the maximum of a given customer? right

                      If there are customer with N1234-BB-I07 and they would like to create a record with N1234-AA-I06, is it possible to autogenerate?

                       

                      Otherwise, it may be much easy to select the last part by user.

                      • 8. Re: AutoGeneration of Invoice Number
                        keywords

                        If you look at the field through a self-join relationship that looks at all records in the table via a cartesian join, you can use the Max ( ) function.

                        • 9. Re: AutoGeneration of Invoice Number
                          philmodjunk

                          Don't use a Cartesian self join use a relationship that links to all invoices for the same customer and then either th last or max function can get the value to which you add 1.

                           

                          ExecuteSQL(. )

                          Could also be used.