Autogenerated BP code with increment of 1

[Former Member]

Hi

As my client wants when he click add button the customer code should increase by 1.

Means if last Customer code is c0003 then on selecting customer from drop down button and clicking add the code should be self generated to c0004.

Likewise the case of vendor also.

How should i do this?

Thanks

[Former Member]

Or try this code in FMS:

SELECT  $[OCRD.CardType]+isnull(str(max(substring(T0.CardCode, 2, 4))+1, 4, 0),'1000') 
FROM OCRD T0
WHERE T0.CardType = $[OCRD.CardType]

[Former Member]

Try this one:

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)

Thanks,

Gordon

[msundararaja_perumal]

Hi,

If your issue is still not resolved try this

In order to create consecutive alphanumeric business partner codes, such as C1000, C1001 etc, please follow the procedure outlined below:

1. Manually create the first customer, supplier and lead type business partners, e.g. C1000, S5000, L8000.

2. Go to Reports -> Query Generator -> Click on 'Execute' without making any selection.

3. In the 'Queries' window click on the pencil icon in the upper left area to activate the query structure area. The area appears grey when blocked, white when ready and yellow when active.

4. Copy and paste this query

select max(cardtype) + cast(substring( max(cardcode), 2, len(max(cardcode))) + 1 as nvarchar(10)) from ocrd  where cardtype = $[OCRD.CARDTYPE] 

5. Click on the button 'Save',then, in the 'Save Query' window, enter an appropriate name for the query and select the appropriate category. Then click on 'Save'.

6. Click on 'Cancel' in the window 'Queries' and 'Close' in the window 'Query Generator'.

7. Go to Business Partners -> Business Partner Master Data, change to 'Add' mode.

8. Map a formatted search to this field by using CtrlAltF2 in the active field.

9. In the 'Define Formatted Search' window, click on the button for 'Search by Saved Query'

10. Bring the cursor to the bar that will pop up just below 'Search by Saved Query' until it changes from an arrow to a pointing hand, then double-click.

11. The 'Query Manager' window will pop up, select the category where the query above was saved under, then click on the query name to select it, click on 'OK'.

12. Place a tick in the box for 'Auto Refresh When Field Changes' and select 'BP Name' from the drop down menu.

13. Click on 'Cancel' in the Business Partner Master Data window and open it up again.

14. Click on 'Refresh Regularly' and click on 'Save'.

15. Change to 'Add' mode, select the 'Type' (Customer, Supplier, Lead) and enter a name for the new business partner. Press the 'Tab' key and the next available BP code will automatically be inserted into the BP code field.

[Former Member]

I have Imported BPs through DTW upto

C00127

V00029

But now i want that that when user select the BP type and click add then next customer should be c00128 and vendor v00030 and so on it should increase with increment of 1.

Is this possible?How?

Thanks

[Former Member]

Hi!

Did u tried this FMS !

declare @temp as char(20) 
IF $[OCRD.Cardtype] = 'C'
BEGIN 
set @temp=(select isnull(max(right(cardcode,4)),0) + 1 from OCRD where (Cardtype='C') and (len(cardcode)=6)) 
set @temp='C'+isnull(replicate(0,4-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END
ELSE IF $[OCRD.Cardtype] = 'S'
BEGIN 
set @temp=(select isnull(max(right(cardcode,4)),0) + 1 from OCRD where (Cardtype='S') and (len(cardcode)=6)) 
set @temp='V'+isnull(replicate(0,4-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END

[Former Member]

declare @temp as char(20) 
IF $[OCRD.Cardtype] = 'C'
BEGIN 
set @temp=(select isnull(max(right(cardcode,5)),0) + 1 from OCRD where (Cardtype='C') and (len(cardcode)=7)) 
set @temp='C'+isnull(replicate(0,5-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END
ELSE IF $[OCRD.Cardtype] = 'S'
BEGIN 
set @temp=(select isnull(max(right(cardcode,5)),0) + 1 from OCRD where (Cardtype='S') and (len(cardcode)=7)) 
set @temp='V'+isnull(replicate(0,5-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END

There is only one change, I changed isnull(max(right(cardcode,4)),0) to isnull(max(right(cardcode,5)),0)

This should work for you.

[Former Member]

Hi Malhotra,

Check the following thread

*Close the thread if issue solved.

Regards

Jambulingam.P

[Former Member]

Hi!

Assingn this FMS on CustomerCode in BP Master and Make it Refresh on BP Name ! and set Display Saved Values !

Select
(case B0.CardType
when 'C' then (select isnull(max(B1.Cardcode)+1,'C0001') from OCRD B1)
When 'S' then (select isnull(max(B1.Cardcode)+1,'V0001') from OCRD B1)
End)
From OCRD B0

Try this !

[Former Member]

Hi

On running this query in SQL i receive foll error:

*Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the nvarchar value 'V0128' to data type int.*

Where v0128 is my last vendor code in system.

[Former Member]

Hi!

Try this then

Select
(case B0.CardType
when 'C' then (select max(right(B1.Cardcode,4))+1from OCRD B1)
When 'S' then (select max(right(B1.Cardcode,4))+1 from OCRD B1)
End)
From OCRD B0

Try this in Test System !

Add Max 10 Customer and check it thoroughly !

[Former Member]

iTS JUST GIVING VALUE 129,

AND IT IS 188TIMES.

188 IS THE NO OF BPs IN THE SYSTEM.

[Former Member]

Hi!

Sorry, thtz my mistake.

Gv me some time i will check it fully and post a new one !

[Former Member]

Hi!

Try this !

declare @temp as char(20) 
IF $[OCRD.Cardtype] = 'C'
BEGIN 
set @temp=(select isnull(max(right(cardcode,4)),0) + 1 from OCRD where (Cardtype='C') and (len(cardcode)=5)) 
set @temp='C'+isnull(replicate(0,4-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END
ELSE IF $[OCRD.Cardtype] = 'S'
BEGIN 
set @temp=(select isnull(max(right(cardcode,4)),0) + 1 from OCRD where (Cardtype='S') and (len(cardcode)=5)) 
set @temp='V'+isnull(replicate(0,4-len(@temp)),'')+@temp 
select cast(@temp as char(20)) 
END

Also check this Thraed !

Edited by: Suraj V on Aug 11, 2009 2:49 PM

[msundararaja_perumal]

Hi,

Refer this note:[571681|https://websmp130.sap-ag.de/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/smb_searchnotes/display.htm?note_langu=E&note_numm=0000571681] for generating BP code automatically.