Automatic BP Numbering in SAP Business One

by

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

Leave a Comment