Skip to Content
author's profile photo Former Member
Former Member

Autogenerated BP code with increment of 1

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Aug 11, 2009 at 07:43 AM
    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 11, 2009 at 08:30 AM

    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 !

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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 !

      auto-increament-of-itemcode

      auto-generation-of-customer-code

      auto-increment-of-bp-code

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

  • author's profile photo Former Member
    Former Member
    Posted on Aug 11, 2009 at 11:54 AM

    Hi Malhotra,

    Check the following thread

    creating-new-bp---next-code--not-automatic-what-ab

    how-to-auto-increment-business-partner-code-or-fin

    Auto Increment of BP code

    *Close the thread if issue solved.

    Regards

    Jambulingam.P

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 13, 2009 at 08:35 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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.

  • author's profile photo Former Member
    Former Member
    Posted on Aug 13, 2009 at 03:59 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 14, 2009 at 05:13 AM

    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]

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.