6 Replies Latest reply on Aug 20, 2015 2:11 PM by MariusPirv

    Assigning invoice numbers

    x4der

      Title

      Assigning invoice numbers

      Post

      I have work orders which are numbered sequantially when entered. All invoice numbers are initially set to 0 and then assigned an invoice # on the day they are shipped.

      How do I grab the last invoice # of previous shipments so that I can run a script with a loop sequence and number the invoices from the last one?

        • 1. Re: Assigning invoice numbers
          philmodjunk

          Is there ever more than one user doing this at the same time?

          If so, extra precautions are needed to keep both users from getting the same next invoice number.

          If you will always have just one user doing this at a time, You can define a summary field such as sMaxInvoiceNumber in the invoice table to return the Maximum of your invoice number. Then

          Show All Records
          Set Field ( YourTable::InvoiceNumber; YourTable::sMaxInvoiceNumber + 1]

          will assign your next invoice number.

          If you'll have multiple users doing this let me know and also let me know if your Invoices are work orders with an Invoice Number assigned or if they are a separate table linked to work orders. I'll then post a method that will safely do this when you have multiple users performing this operation.

          • 2. Re: Assigning invoice numbers
            x4der

            Thanks Phil. Like so many other things, the answer is obvious once it is shown to you. I will use a loop command because we will post multiple invoices in one day. This will entail finding the records to be invoiced. So should the sMaxInvoiceNumber be assigned to $InvNo before finding the records?

            We will have two users on this database. Work_Order_Number and Invoice_Number are fields in the same table.

            • 3. Re: Assigning invoice numbers
              philmodjunk

              Capturing the max value in a variable is definitely something you'd want to do before performing the find as the summary field computes from all the records in your found set and a found set of unnumbered records would give this field an empty value!

              If you are using an "end of the day" batch process to do this, you might be able to set up a script that uses Replace Field contents with the serial number option to update them all in one go. Whether you loop use replace field contents, such a batch update process is best run when there is no possibility that another user might be editing and thus locking a record that's part of the batch at the same time the script is running.

              • 4. Re: Assigning invoice numbers
                x4der

                Here is what I ended up with:

                Go to Layout [ “Invoice Input” (Invoices) ]
                Show All Records
                Set Variable [ $Inv_Num_Last; Value: Invoices::sInv_Num_Last ]
                Perform Find [ Specified Find Requests: Find Records; Criteria: Invoices::Invoice_Number: “<1” AND Invoices::Ship_Date: “//” ] [ Restore ]

                Loop

                Insert Calculated Result [ Invoices::Invoice_Number; $Inv_Num_Last + 1 ]

                Set Variable [ $Inv_Num_Last; Value:$Inv_Num_Last + 1 ]

                Go to Record/Request/Page[ Next; Exit after last ]
                End Loop

                This script can be executed as many times a day as needed.

                Thanks again!

                • 5. Re: Assigning invoice numbers
                  philmodjunk

                  I wouldn't use Insert Calculated Result for this. Set Field will do the same job and is slightly more robust. In particular, the "Insert" script steps only work if you have the target field physically present on the layout. If you later edit the layout to remove it from the layout, the "insert" step will silently fail to work where set field will not be affected.

                  • 6. Re: Assigning invoice numbers
                    MariusPirv

                    Hello Phil,

                    Can you please post the method that you are referring to here

                    "If you'll have multiple users doing this let me know and also let me know if your Invoices are work orders with an Invoice Number assigned or if they are a separate table linked to work orders. I'll then post a method that will safely do this when you have multiple users performing this operation."

                    I believe this method might help with my invoice problem.

                    Thank you,

                    Marius