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?
Auto Generated Customer Account Number
Apr 14, 2006
1 Replies
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 )
Join the Discussion
Have something to add? Share your thoughts — no account required.
Didn't find your answer?
Ask the community — no account required