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?
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 )
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.