Auto Code Generation

[Former Member]

Experts,

BP - V00000000000001 - V00000000001315 - Last No. Vendor

BP - C00000000000001 - C00000000039861 - Last No. Customer

Item - 1000000001 - 1000016632 - Last No.

For generating auto code for this

SELECT

CASE $[$40.0]

When 'C' then 'C

'When 'L' then 'L'

When 'S' then 'V'

ENDIsnull(Substring(Str(100000Max(Right(T0.CardCode,5))+1,6),2,5),'00001')

FROM OCRD T0

WHERE T0.CardCode like '%[0-9][0-9][0-9][0-9][0-9]'AND T0.CardType = $[$40.0]

I am using this, am i going right? it is not giving any value...

[Former Member · Accepted solution]

Hi Rupa,

The following should give you what you need.

DECLARE @NextNumber INT

SELECT @NextNumber = ISNULL(MAX(CAST(RIGHT(T0.CardCode, LEN(T0.CardCode) - 1) AS INT)), 0) + 1
FROM OCRD T0
WHERE ISNUMERIC(RIGHT(T0.CardCode, LEN(T0.CardCode) - 1)) = 1
	AND T0.CardType = $[$40.0.0]

SELECT 
	CASE $[$40.0.0] 
		WHEN 'S' THEN 'V'
		ELSE $[$40.0.0]
	END + REPLICATE('0', 14-LEN(CAST(@NextNumber AS NVARCHAR))) + CAST(@NextNumber AS NVARCHAR)

This will return a 15 character code of the appropriate prefix and number. It also dynamically adjusts to the length of the next number rather than hard coding the zeros into the formula.

Hope this helps.

Cheers,

Sean

[Former Member · Accepted solution]

For Customer it gives

C00000000100000

For Vendor it gives

V00000000070001

For Lead it gives

L00000000010003

[Former Member · Accepted solution]

I want

BP - V00000000000001 - V00000000001315 - Last No. Vendor

BP - C00000000000001 - C00000000039861 - Last No. Customer

[Former Member · Accepted solution]

Hi Rupa,

Check your OCRD table, you must have some text only codes in Customer Code for the last 5 digits. That makes the MAX function return 0 for customer.

Thanks,

Gordon

[former_member204969]

Try this:

SELECT
CASE $[$40.0]
 When 'C' then 'C'
 When 'L' then 'L'
 When 'S' then 'V'
END+'00000000'+
Isnull(Substring(Str(100000+Max(Right(T0.CardCode,5))+1,6),2,5),'00001')
FROM OCRD T0
WHERE T0.CardCode like '%[0-9][0-9][0-9][0-9][0-9]'
  AND T0.CardType = $[$40.0]

[Former Member]

For Customer it gives

C0000000000000

but for vendor

it gives

V0000000070001

Edited by: Rupa86 on Jul 14, 2011 6:28 PM

[Former Member]

Hi Rupa.......

Check this thread and do modify as per your requirement........

Regards,

Rahul