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.
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.
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.
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 ]
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 ]
This script can be executed as many times a day as needed.
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.
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.