How to Merge customers Vendors, GL Accounts

ara3nara3n Member Posts: 9,255
edited 2015-09-22 in NAV Tips & Tricks
Hello Here is an example on how to merge customers. This can be used and changed to merge vendors, GL accounts.
The basic logic is this.

CustA.get(A);

CustA.Delete;

CustB.get(B);

CustB.rename(A);
OBJECT Report 50044 Combine Customers
{
  OBJECT-PROPERTIES
  {
    Date=05/10/06;
    Time=[ 4:05:48 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    ProcessingOnly=Yes;
    OnPreReport=BEGIN
                  IF NOT CONFIRM(STRSUBSTNO(TecText,CustomerNo1,CustomerNo2)) THEN
                     ERROR('');

                  Customer.GET(CustomerNo2);
                  Customer.DELETE;


                  CommentLine.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                  CommentLine.SETRANGE("No.",CustomerNo1);
                  IF CommentLine.FIND('-') THEN REPEAT
                     CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                     CommentLine2.SETRANGE("No.",CustomerNo2);
                     IF CommentLine2.FIND('+') THEN;
                     CommentLine3 := CommentLine;
                     CommentLine3."No." := CustomerNo2;
                     CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                     CommentLine3.INSERT;
                     CommentLine.DELETE;
                  UNTIL CommentLine.NEXT = 0;

                  CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                  CommentLine2.SETRANGE("No.",CustomerNo2);
                  IF CommentLine2.FIND('+') THEN;
                  CommentLine3.INIT;
                  CommentLine3.Code := 'MERGE';
                  CommentLine3."Table Name" := CommentLine3."Table Name"::Customer;
                  CommentLine3."No." := CustomerNo2;
                  CommentLine3.Date := TODAY;

                  CommentLine3.Comment := 'Customer ' +CustomerNo1+ ' was merged';
                  CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                  CommentLine3.INSERT;

                  DefaultDim1.SETRANGE("Table ID",DATABASE::Customer);
                  DefaultDim1.SETRANGE("No.",CustomerNo1);
                  DefaultDim1.DELETEALL;

                  Customer.GET(CustomerNo1);
                  Customer2 := Customer;
                  Customer.RENAME(CustomerNo2);


                  Customer2.INSERT;
                  Customer2.DELETE(TRUE);
                END;

  }
  DATAITEMS
  {
  }
  REQUESTFORM
  {
    PROPERTIES
    {
      Width=8910;
      Height=3960;
    }
    CONTROLS
    {
      { 1000000000;TextBox;3960 ;550  ;4620 ;440  ;SourceExpr=CustomerNo1;
                                                   TableRelation=Customer }
      { 1000000001;Label  ;550  ;550  ;2860 ;440  ;ParentControl=1000000000;
                                                   CaptionML=ENU=Delete Customer }
      { 1000000002;TextBox;3960 ;1210 ;4620 ;440  ;SourceExpr=CustomerNo2;
                                                   TableRelation=Customer }
      { 1000000003;Label  ;550  ;1210 ;2970 ;440  ;ParentControl=1000000002;
                                                   CaptionML=ENU=Merge into Customer }
    }
  }
  CODE
  {
    VAR
      CustomerNo1@1000000000 : Code[20];
      CustomerNo2@1000000001 : Code[20];
      Customer@1000000002 : Record 18;
      TecText@1000000004 : TextConst 'ENU=Are you sure you want Merge Customer %1 into Customer %2';
      Customer2@1000000007 : Record 18;
      CommentLine@1000000003 : Record 97;
      CommentLine2@1000000005 : Record 97;
      CommentLine3@1000000006 : Record 97;
      DefaultDim1@1000000008 : Record 352;

    BEGIN
    END.
  }
}



Ahmed Rashed Amini
Independent Consultant/Developer


blog: https://dynamicsuser.net/nav/b/ara3n
«1

Comments

  • couberpucouberpu Member Posts: 317
    What Would happen in table18
    OnDelete() 
    MoveEntries.MoveCustEntries(Rec);
    

    Do we need to put new Customer.No. into Customer No. field in Cust. Ledger Entry table?

    This is such a cool idea and it might help me with s lot of small things at work! \:D/

    Best,
    CouberPu
  • ara3nara3n Member Posts: 9,255
    My routine does not run that ondelete trigger. So the code is not run.
    Do we need to put new Customer.No. into Customer No. field in Cust. Ledger Entry table?
    No you don't. Navision will automatically do it for you.


    Yep it's a great idea, :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • henrikohmhenrikohm Member Posts: 30
    well, what about customer ledger entries etc.???
    Best regards
    Henrik Ohm
  • Alex_ChowAlex_Chow Member Posts: 5,063
    henrikohm wrote:
    well, what about customer ledger entries etc.???

    It will be automatically changed when Navision does the rename.
  • navexnavex Member Posts: 16
  • ara3nara3n Member Posts: 9,255
    navex wrote:
    Thanks ara3n
    :)

    It's nice to see random "thank you" in your old posts. :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SogSog Member Posts: 1,023
    Thank you ara3n
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • ara3nara3n Member Posts: 9,255
    you are welcome.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • djswimdjswim Member Posts: 277
    Haven't tried it yet, but I'm going to be doing testing later. Consider this a preemptive thank you, I'll only come back if I have trouble with something :D
    "OMG ALL MY DATA IS GONE"
    "Show All..."
    "Oh..."
  • ara3nara3n Member Posts: 9,255
    Consider this a preemptive you are welcome. :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • remarkremark Member Posts: 122
    Hi Rashed!

    I have tried your code and it worked so great. Thank you very much. :D

    BTW, before finding your post I unsuccessfully tried to merge customers using standard NAV functionality: Customer Card > Customer > Combine Customers. Got too many difficulties...

    Best Regards
  • ara3nara3n Member Posts: 9,255
    you are welcome.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • SLF25SLF25 Member Posts: 37
    This is an example of thinking outside the box, nice.
  • ara3nara3n Member Posts: 9,255
    Thanks :)
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • gunavsgunavs Member Posts: 32
    Thank You ara3n! Going to try it out right now!
  • ssinglassingla Member Posts: 2,973
    My turn to say thank you =D> Rashed.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • ara3nara3n Member Posts: 9,255
    You are welcome. :D
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • EwanEwan Member Posts: 54
    edited 2013-01-10
    Hi

    I need to merge a number of vendor accounts and have a number of questions

    1) Will it work in Nav 2009?
    2) Do I need to replace Customer with Vendor in the Code?

    Many thanks for you help
  • ara3nara3n Member Posts: 9,255
    Hello.

    Yes to all your questions.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • EwanEwan Member Posts: 54
    Hi

    Noticed that it leaves data behind in the following tables:

    [dbo].[TEST$Comment Line].[Comment]
    [dbo].[TEST$Purch_ Inv_ Header].[Invoice Disc_ Code]
    [dbo].[TEST$Vendor Bank Account].[Code]
    [dbo].[TEST$Vendor].[BACS Account No_]
    [dbo].[TEST$Vendor].[Invoice Disc_ Code]

    In fact the merged to account ends up with the vendor Bank And Inv Disc code belonging to the deleted vendor


    Many Thanks
  • ara3nara3n Member Posts: 9,255
    Those tables are not linked to the pk table.
    You'll need to add them to your routine.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • EwanEwan Member Posts: 54
    Hi has anyone tried deleting the leftover vendor bank account details?

    I do not seem to be able to retrieve the bank account details

    VendorBank.GET(VendorNo1); fails

    as does

    VendorBank.GET(VendorNo1; VendorNo1);

    Where VendorBank is a record linked to Vendor Bank Account table (288)
  • jglathejglathe Member Posts: 639
    Hi Ewan,

    Try VendorBank.get(Vendor.Code,BankCode)

    with best regards

    Jens
  • EwanEwan Member Posts: 54
    Thanks - got it to delete the bank accounts left behind

    Does it matter that there will be orphaned Purchase Invoices / credit memos?

    Clearly the rename function ingnores them

    Regards
  • EwanEwan Member Posts: 54
    For completeness I have attached a version for Vendors that
    1) Retains the Merged To account details
    2) Removes the Merged From Vendor Bank account record
    OBJECT Report 50045 Combine Vendors
    {
      OBJECT-PROPERTIES
      {
        Date=11/01/13;
        Time=13:49:44;
        Modified=Yes;
        Version List=ESW;
      }
      PROPERTIES
      {
        ProcessingOnly=Yes;
        OnPreReport=BEGIN
                      IF NOT CONFIRM(STRSUBSTNO(TecText,VendorNo1,VendorNo2)) THEN
                         ERROR('');
    
                      Vendor.GET(VendorNo2);
                      Vendor.DELETE;
    
    
                      CommentLine.SETRANGE("Table Name",CommentLine."Table Name"::Vendor);
                      CommentLine.SETRANGE("No.",VendorNo1);
                      IF CommentLine.FIND('-') THEN REPEAT
                         CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Vendor);
                         CommentLine2.SETRANGE("No.",VendorNo2);
                         IF CommentLine2.FIND('+') THEN;
                         CommentLine3 := CommentLine;
                         CommentLine3."No." := VendorNo2;
                         CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                         CommentLine3.INSERT;
                         CommentLine.DELETE;
                      UNTIL CommentLine.NEXT = 0;
    
                      CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Vendor);
                      CommentLine2.SETRANGE("No.",VendorNo2);
                      IF CommentLine2.FIND('+') THEN;
                      CommentLine3.INIT;
                      CommentLine3.Code := 'MERGE';
                      CommentLine3."Table Name" := CommentLine3."Table Name"::Vendor;
                      CommentLine3."No." := VendorNo2;
                      CommentLine3.Date := TODAY;
    
                      CommentLine3.Comment := 'Vendor ' +VendorNo1+ ' was merged';
                      CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                      CommentLine3.INSERT;
    
                      DefaultDim1.SETRANGE("Table ID",DATABASE::Vendor);
                      DefaultDim1.SETRANGE("No.",VendorNo1);
                      DefaultDim1.DELETEALL;
    
                      Vendor2.GET(VendorNo1);
                      Vendor3 :=Vendor2;
                      Vendor2.RENAME(VendorNo2);
    
                      VendorBank.SETRANGE("Vendor No.", Vendor."No.");
                      VendorBank.SETRANGE(Code, VendorNo1);
    
                      Vendor2.DELETE;
    
                      Vendor.INSERT;
    
                      Vendor3.INSERT;
                      Vendor3.DELETE(TRUE);
    
                      IF VendorBank.FINDFIRST THEN BEGIN
                        VendorBank.DELETE;
                      END;
                      MESSAGE(STRSUBSTNO(FinText,VendorNo1,VendorNo2));
                    END;
    
      }
      DATAITEMS
      {
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=8910;
          Height=3960;
        }
        CONTROLS
        {
          { 1000000000;TextBox;3960 ;550  ;4620 ;440  ;InPage=-1;
                                                       SourceExpr=VendorNo1;
                                                       TableRelation=Vendor }
          { 1000000001;Label  ;550  ;550  ;2860 ;440  ;ParentControl=1000000000;
                                                       CaptionML=ENU=Delete Vendor }
          { 1000000002;TextBox;3960 ;1210 ;4620 ;440  ;InPage=-1;
                                                       SourceExpr=VendorNo2;
                                                       TableRelation=Vendor }
          { 1000000003;Label  ;550  ;1210 ;2970 ;440  ;ParentControl=1000000002;
                                                       CaptionML=ENU=Merge into Vendor }
        }
      }
      REQUESTPAGE
      {
        PROPERTIES
        {
        }
        CONTROLS
        {
        }
      }
      CODE
      {
        VAR
          VendorNo1@1000000000 : Code[20];
          VendorNo2@1000000001 : Code[20];
          Vendor@1000000002 : Record 23;
          TecText@1000000004 : TextConst 'ENU=Are you sure you want Merge Vendor %1 into Vendor %2';
          Vendor2@1000000007 : Record 23;
          Vendor3@1190000000 : Record 23;
          CommentLine@1000000003 : Record 97;
          CommentLine2@1000000005 : Record 97;
          CommentLine3@1000000006 : Record 97;
          DefaultDim1@1000000008 : Record 352;
          VendorBank@1190000001 : Record 288;
          FinText@1000000009 : TextConst 'ENU=Vendor %1 has been successfully Merged into Vendor %2';
    
        BEGIN
        END.
      }
      RDLDATA
      {
      }
    }
    
    
  • prateekarya2011prateekarya2011 Member Posts: 40
    Hiii All experts :D
    I have a small query

    While renaming customer system is generating error

    //*********************//
    The Customer Bank Account already exists.

    Identification fields and values:

    Customer No.='20000',Code='ECA'

    //***********************//

    What system is doing is correct but as the code stops at

    Customer.GET(CustomerNo1);
    Customer2 := Customer;
    Customer.RENAME(CustomerNo2);


    ** CustomerNo1 = Customer to be deleted
    ** CustomerNo2 is the customer into which records are to be mergerd.

    Should i write the code to modify customer ledger entry & other tables or some modification is required in above written code?

    Please help..

    Thanks in advance
    Prateek
  • prateekarya2011prateekarya2011 Member Posts: 40
    Issue solved experts..;-)
    It was my mistake actually the error was for Customer bank accounts..
    I am just wondering what is the use of these bank accounts..... presently i deleted the customer bank account entry and it worked out.
  • apertierraapertierra Member Posts: 61
    Ewan wrote:
    Hi

    Noticed that it leaves data behind in the following tables:

    [dbo].[TEST$Comment Line].[Comment]
    [dbo].[TEST$Purch_ Inv_ Header].[Invoice Disc_ Code]
    [dbo].[TEST$Vendor Bank Account].[Code]
    [dbo].[TEST$Vendor].[BACS Account No_]
    [dbo].[TEST$Vendor].[Invoice Disc_ Code]

    In fact the merged to account ends up with the vendor Bank And Inv Disc code belonging to the deleted vendor


    Many Thanks

    That's why you need to check all the tables that might be related to the table you are merging the records (customer/vendor/gl account/bank account, etc.) and ensure that the field that links to the table where you are merging the records has the "tablerelation" property set OR in the code modify/rename those records.
    There are problems also when you have records in related tables (like comments, prices,etc) where the records might, after being renamed, have the same key as the existing records for the record you merge into, so you might want to review those and manually rename those records first (or the automatic renaming will give you an error).
  • aadamsaadams Member Posts: 19
    I know this is a very old thread, but we have a situation where we've ended up with several duplicate customer cards that were created by mistake and that have ledger entries against. I've read there's a Combine Customer/Vendor feature, but I cannot seem to find it in my install (NAV 2015). The only MS related documentation I find seems to point to it being a Russian local feature, so perhaps the feature is not available on my Netherlands install?

    Would this report work for NAV 2015 and does it bring all of the customer 1 history to customer 2? Does the customer 2 retain it's customer number?

    If this proc report does work in NAV 2015, can someone export this as a fob so that I could import and use? (as an end user (IT, but no developer license) I cannot import txt files)

    I can try to get our VAR to install it for us, but would rather just import a fob if one is available.

    Thanks in advance.
  • besfortazizibesfortazizi Member Posts: 2
    ara3n wrote: »
    Hello Here is an example on how to merge customers. This can be used and changed to merge vendors, GL accounts.
    The basic logic is this.

    CustA.get(A);

    CustA.Delete;

    CustB.get(B);

    CustB.rename(A);
    OBJECT Report 50044 Combine Customers
    {
      OBJECT-PROPERTIES
      {
        Date=05/10/06;
        Time=[ 4:05:48 PM];
        Modified=Yes;
        Version List=;
      }
      PROPERTIES
      {
        ProcessingOnly=Yes;
        OnPreReport=BEGIN
                      IF NOT CONFIRM(STRSUBSTNO(TecText,CustomerNo1,CustomerNo2)) THEN
                         ERROR('');
    
                      Customer.GET(CustomerNo2);
                      Customer.DELETE;
    
    
                      CommentLine.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                      CommentLine.SETRANGE("No.",CustomerNo1);
                      IF CommentLine.FIND('-') THEN REPEAT
                         CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                         CommentLine2.SETRANGE("No.",CustomerNo2);
                         IF CommentLine2.FIND('+') THEN;
                         CommentLine3 := CommentLine;
                         CommentLine3."No." := CustomerNo2;
                         CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                         CommentLine3.INSERT;
                         CommentLine.DELETE;
                      UNTIL CommentLine.NEXT = 0;
    
                      CommentLine2.SETRANGE("Table Name",CommentLine."Table Name"::Customer);
                      CommentLine2.SETRANGE("No.",CustomerNo2);
                      IF CommentLine2.FIND('+') THEN;
                      CommentLine3.INIT;
                      CommentLine3.Code := 'MERGE';
                      CommentLine3."Table Name" := CommentLine3."Table Name"::Customer;
                      CommentLine3."No." := CustomerNo2;
                      CommentLine3.Date := TODAY;
    
                      CommentLine3.Comment := 'Customer ' +CustomerNo1+ ' was merged';
                      CommentLine3."Line No." := CommentLine2."Line No." + 10000;
                      CommentLine3.INSERT;
    
                      DefaultDim1.SETRANGE("Table ID",DATABASE::Customer);
                      DefaultDim1.SETRANGE("No.",CustomerNo1);
                      DefaultDim1.DELETEALL;
    
                      Customer.GET(CustomerNo1);
                      Customer2 := Customer;
                      Customer.RENAME(CustomerNo2);
    
    
                      Customer2.INSERT;
                      Customer2.DELETE(TRUE);
                    END;
    
      }
      DATAITEMS
      {
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=8910;
          Height=3960;
        }
        CONTROLS
        {
          { 1000000000;TextBox;3960 ;550  ;4620 ;440  ;SourceExpr=CustomerNo1;
                                                       TableRelation=Customer }
          { 1000000001;Label  ;550  ;550  ;2860 ;440  ;ParentControl=1000000000;
                                                       CaptionML=ENU=Delete Customer }
          { 1000000002;TextBox;3960 ;1210 ;4620 ;440  ;SourceExpr=CustomerNo2;
                                                       TableRelation=Customer }
          { 1000000003;Label  ;550  ;1210 ;2970 ;440  ;ParentControl=1000000002;
                                                       CaptionML=ENU=Merge into Customer }
        }
      }
      CODE
      {
        VAR
          CustomerNo1@1000000000 : Code[20];
          CustomerNo2@1000000001 : Code[20];
          Customer@1000000002 : Record 18;
          TecText@1000000004 : TextConst 'ENU=Are you sure you want Merge Customer %1 into Customer %2';
          Customer2@1000000007 : Record 18;
          CommentLine@1000000003 : Record 97;
          CommentLine2@1000000005 : Record 97;
          CommentLine3@1000000006 : Record 97;
          DefaultDim1@1000000008 : Record 352;
    
        BEGIN
        END.
      }
    }
    
    
    
    

    Hi Great Example... Do you have the same report for NAV 2013 r2. It would really be helpful.
    Thanks :smile:
Sign In or Register to comment.