Assignment of reference numbers

[Cliff Wiernik]

In our application, we have a variety of next reference numbers and next numbers assigned to customer accounts/transactions. They are contained in basically 2 files. With these files, to process a transactions, we get a lock on the file, retrieve the variety of numbers and increment the numbers. We may need to increment some numbers several times during the transactions. We will release the lock on the file, as it is used by everyone, as quickly as possible. The numbers may be next customer account numbers, next transaction numbers, next credit bureau numbers, next application numbers and a variety of other next ....... numbers.

This has worked with upwards of 200+ people working on the system at the same time. Sometimes, we run into situations where volume is high and some individuals timeout attempting to access and lock the file to get the next numbers. In the past, we had to break out some of the numbers for one of the departments into its own separate file as automated processes like the posting of cash receipts received via a lockbox would tie up the file for extended periods of time. It would lock/unlock very quickly but because it was automated, it would also relock very quickly.

I am wondering on thoughts and suggestions from others who may have encountered similar situations and have come up with a better solution. We generally need to account for the integrity of all the numbers so that the entire sequence is accounted for.

Cliff

[rdonnay]

I have a customer who uses an .ADT file to handle incremental numbers.

The .ADT file will have only one field that is an auto-increment field. When a new record is added, the number is automatically incremented in the new record. This prevents duplicates. You can add an .ADT file to your data-dictionary. You just need to give it a start number.

Code: Select all

FUNCTION GetNextNumber()
LOCAL nNext
USE COUNTER
append blank
dbunlock()
nNext := COUNTER->nc_no
RETURN nNext

[Cliff Wiernik]

So is that file constantly adding records and are they deleted at some time?

[rdonnay]

Yes. Occasionally he deletes all but the last record.

[Auge_Ohr]

what about using UuidCreate() as primary Key ?

[Cliff Wiernik]

That might work for some items, but then they are not sequential. However, would not work for customer's account number. An 9 digiit number (first 8 significant with a mod 10 check digit as last).

Thanks for the info.