Page Comparison

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 screen in that order e.g. in an alpha numeric sort, 10, 11, 11001, 11121200 etc. displays before records starting with 2.

Image Added

Step-by-step guide

...

...

Warning
title SQL skills are required
Sound SQL skills are required to complete this task

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'

 

Image Added

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 BRSY_Main Numbers SET BankRecNo LastInvoiceNo = RIGHT('0000000000' + BankRecNoLastInvoiceNo, 10)8) WHERE Description = 'WorkOrderNo'

Related articles

...