on 2009 Jun 04 2:25 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
105 | |
8 | |
6 | |
6 | |
5 | |
5 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.