Auto Generated Customer Account Number

Posted by Chris on April 14, 2006, 10:30 am

Please log in for more thread options

I appreciate that RMS is optomistically perset so that we can will someday
need 7 digit account numbers, but for now five digits would do. I have tried
in configuration to change the next auto generated account number to a five
digit number but when I leave the field or click OK two leading 00's are
added to it. Am I missing something?

Posted by Ken K on April 15, 2006, 11:26 am

Please log in for more thread options

Hi Chris,

The first SQL script below will drop the leading two zeroes from existing
account numbers. Make a backup before you execute it.

The second will create a trigger which drops the two zeroes for new
customers. Run them one at a time.

Ken Kosnik
Computer Analytics Corporation

UPDATE customer
SET accountNumber = RIGHT(RTRIM(accountNumber),5)
WHERE LEN(RTRIM(accountNumber)) = 7
AND LEFT(accountNumber,2) = '00'

CREATE TRIGGER [tr_Force_5digit_accountNumber] ON [dbo].[Customer]
FOR INSERT AS
UPDATE customer
SET accountNumber = RIGHT(RTRIM(accountNumber),5)
WHERE LEN(RTRIM(accountNumber)) = 7
AND LEFT(accountNumber,2) = '00'
AND id IN (SELECT id FROM inserted )

>I appreciate that RMS is optomistically perset so that we can will someday

>need 7 digit account numbers, but for now five digits would do. I have

>tried in configuration to change the next auto generated account number to

>a five digit number but when I leave the field or click OK two leading 00's

>are added to it. Am I missing something?