Adding leading zeroes to batch numbers so they display in order

The unique numbering in all forms e.g. sales orders, bank reconciliations, creditor purchases is a alpha numeric field and display in the search screen in that order e.g. in an alpha numeric sort, 10, 11, 11001, 11121200 etc. displays before records starting with 2.

Open

Step-by-step guide

For the list of records to appear in a logical order (i.e. date of entry) leading zeroes need to be added to the batch or form unique number to facilitate that.  The SY_Numbers table must also be updated to reflect the new number.  Below is an example of updating the numbering for bank reconciliations.  Compare the following screen with the one above to see the change after the script was run.

^{UPDATE BR_Main SET BankRecNo = RIGHT('0000000000' + BankRecNo, 10)}

^{UPDATE SY_Numbers SET LastInvoiceNo = RIGHT('0000000000' + LastInvoiceNo, 10) WHERE Description = 'GLReconciliation'}

Open

Here is an example for Service Manager Jobs changing the numbering to 6 characters.

UPDATE SM_Jobs SET JobNo = RIGHT('0000000000' + JobNo, 6)
UPDATE SY_Numbers SET LastInvoiceNo = RIGHT('0000000000' + LastInvoiceNo, 6) WHERE Description = 'ServiceManagerJobNo'

Example changing Work Orders numbers to 8 characters

UPDATE BM_WorkOrder SET WorkOrderNo = RIGHT('0000000000' + WorkOrderNo, 8)
UPDATE SY_Numbers SET LastInvoiceNo = RIGHT('0000000000' + LastInvoiceNo, 8) WHERE Description = 'WorkOrderNo'

Related articles