How to Merge customers Vendors, GL Accounts

ara3n · 2006-12-20

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.
  }
}

couberpu · 2007-01-21

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

ara3n · 2007-01-21

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,

henrikohm · 2007-09-25

well, what about customer ledger entries etc.???

Alex_Chow · 2007-09-25

henrikohm wrote:

well, what about customer ledger entries etc.???

It will be automatically changed when Navision does the rename.

navex · 2008-02-18

Thanks ara3n

ara3n · 2008-02-18

navex wrote:

Thanks ara3n

It's nice to see random "thank you" in your old posts.

Sog · 2009-03-16

Thank you ara3n

ara3n · 2009-03-16

you are welcome.

djswim · 2009-04-22

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

ara3n · 2009-04-22

Consider this a preemptive you are welcome.

remark · 2010-05-10

Hi Rashed!

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

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

ara3n · 2010-05-10

you are welcome.

SLF25 · 2010-05-12

This is an example of thinking outside the box, nice.

ara3n · 2010-05-12

Thanks

gunavs · 2010-08-03

Thank You ara3n! Going to try it out right now!

ssingla · 2011-11-07

My turn to say thank you =D> Rashed.

ara3n · 2011-11-07

You are welcome.

Ewan · 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

ara3n · 2013-01-10

Hello.

Yes to all your questions.

Ewan · 2013-01-10

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

ara3n · 2013-01-10

Those tables are not linked to the pk table.
You'll need to add them to your routine.

Ewan · 2013-01-11

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)

jglathe · 2013-01-11

Hi Ewan,

Try VendorBank.get(Vendor.Code,BankCode)

with best regards

Jens

Ewan · 2013-01-11

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

Ewan · 2013-01-11

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
  {
  }
}

prateekarya2011 · 2014-06-06

Hiii All experts

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

prateekarya2011 · 2014-06-06

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.

apertierra · 2015-09-03

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).

aadams · 2015-09-22

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.

besfortazizi · 2015-11-26

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

Howdy, Stranger!

Quick Links

Categories

How to Merge customers Vendors, GL Accounts

Comments