NOTE: THIS IS OBSOLETE IN SOME VERSION OF 8.82. THERE IS AN AUTOMATIC CUSTOMER, VENDOR NUMBERING MECHANISM UNDER THE DOCUMENT NUMBERING FORM
Hello,
I have been honing this code for a while now and wanted to share it. Â I ended up using it again today and I impressed myself.
This will automatically sequence up BP codes splitting vendor and customers (+leads). Â It also has an optional prefix.
Save it as a query, then assign it to the Code field on the Business Partner Master Data, set it to auto refresh on Group Code. Â I found it works the best. Â Simply type the name of the BP you want to add and it will magically update.
Code__________________________________
--MJT/Forgestik - Auto sequential numbering for BPs.
--Refresh on Group Code... trust me. Remember to set PREFIXES (C/V/L etc.)
DECLARE @CardType as NVARCHAR(1)
SET @CardType = $[OCRD.CardType.0]
IF (@CardType = 'C' OR @CardType = 'L')
BEGIN
SELECT
'C' +
RIGHT('0000' + CONVERT(VARCHAR(4)
,(SELECT TOP 1 CAST(RIGHT(T0.[CardCode],4) as INT) +'1'
FROM
OCRD T0
WHERE
(T0.[CardType] = 'C' OR T0.[CardType] = 'L')
AND ISNUMERIC(CAST(RIGHT(T0.[CardCode],4) as INT)) = 1
AND LEN(T0.[CardCode]) = '5' ORDER BY T0.[CardCode] DESC)),4)
END
ELSE IF (@CardType = 'S')
BEGIN
SELECT
'V' +
RIGHT('0000' + CONVERT(VARCHAR(4)
,(SELECT TOP 1 CAST(RIGHT(T0.[CardCode],4) as INT) +'1'
FROM
OCRD T0
WHERE
T0.[CardType] = 'S'
AND ISNUMERIC(CAST(RIGHT(T0.[CardCode],4) as INT)) = 1
AND LEN(T0.[CardCode]) = '5' ORDER BY T0.[CardCode] DESC)),4)
END
____________________________________________
I won’t walk you through all of the steps here, but this will create an output something like C0010 and V0010. But you can easily adjust it for almost any scenario.
You need to manually create the first customer/vendor or it won’t work! (Or you can import through DTW first, but there needs to be at least one existing record for either cardtype).
Hope that helps!
Mike