Auto Increment of BP code

[Former Member]

Hi,

Is it possible to get the next code while creating new BP master. If yes can anyone help me in this scenario.

Code which we follow are in this manner Customer: C000001.... and Vendor: V000001

Regards

swapnil

[Former Member · Accepted solution]

Hi SWAPNIL,

Save the following query

IF $[$40.1.0]='' (
   SELECT 'Select BP Type') 
ELSE (
   SELECT T0.CardType + RIGHT('00000' + LTRIM(RIGHT(MAX(T0.CardCode), 5) +1), 5) 
FROM OCRD T0 
WHERE T0.CardType = $[$40.1.0] GROUP BY T0.CardType)

And add it as a Formatted Search in the BP Code field.

Regards,

Vítor Vieira

[Former Member · Accepted solution]

Hi.

thanks for reply.

1. Query is working fine. But in this for supplier increment starts with 'S' instead i want the auto incrment to start with V00... how shall i do the same.

2. Also when i select lead in the BP master no number is reflected in BP code. Pls guide how to do the same for both the cases.

swapnil

[Former Member]

Try this one:

DECLARE @MAXC as integer

SET @MAXC=isnull((SELECT CAST(MAX(RIGHT(OCRD.CardCode,5))+1 AS INTEGER)

FROM OCRD

SELECT CASE

WHEN $[OCRD.CardType\]='C' THEN (SELECT DISTINCT CASE

WHEN LEN(@MAXC)=5 THEN (SELECT 'C' + CAST(@MAXC AS varchar) )

WHEN LEN(@MAXC)=4 THEN (SELECT 'C' + '0' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=3 THEN (SELECT 'C' + '00' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=2 THEN (SELECT 'C' + '000' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=1 THEN (SELECT 'C' + '0000' + CAST(@MAXC AS varchar))

END FROM OCRD )

WHEN $[OCRD.CardType\]='L' THEN (SELECT DISTINCT CASE

WHEN LEN(@MAXC)=5 THEN (SELECT 'L' + CAST(@MAXC AS varchar) )

WHEN LEN(@MAXC)=4 THEN (SELECT 'L' + '0' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=3 THEN (SELECT 'L' + '00' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=2 THEN (SELECT 'L' + '000' + CAST(@MAXC AS varchar))

WHEN LEN(@MAXC)=1 THEN (SELECT 'L' + '0000' + CAST(@MAXC AS varchar))

END FROM OCRD )

WHEN $[OCRD.CardType\]='S' THEN (SELECT DISTINCT CASE

WHEN LEN(@MAXC)=5 THEN (SELECT 'V' + CAST(@MAXS AS varchar) )

WHEN LEN(@MAXC)=4 THEN (SELECT 'V' + '0' + CAST(@MAXS AS varchar))

WHEN LEN(@MAXC)=3 THEN (SELECT 'V' + '00' + CAST(@MAXS AS varchar))

WHEN LEN(@MAXC)=2 THEN (SELECT 'V' + '000' + CAST(@MAXS AS varchar))

WHEN LEN(@MAXC)=1 THEN (SELECT 'V' + '0000' + CAST(@MAXS AS varchar))

END FROM OCRD)

END

[Former Member]

You may check these threads:

Thanks,

Gordon