Merge two clients

Business Central/NAV Developers Forum
1

Hello,

I’d like to merge two customers, so I have to modify all tables where this customer is used, for that I tried to use RENAME function but I had a problem with some keys.

For example :

No. Bank account

Customer1 B1

Customer2 B1

Customer2 B2

I want to put Customer1 in Customer2, so I did :

Cust1.GET('Customer1 ');

Cust2.GET('Customer1 ');

Cust2.DELETE(FALSE);

Cust2.GET(‘Customer2’);

Cust2.RENAME(‘Customer1’);

Cust1.MODIFY;

There is two problems here :

1- when it tries to modify the bank account it shows an error two records with the same key !

2- It doesn’t insert B2 for the customer1

I didn’t use my own search and modify / insert because If the user add a new relation table, I have to modify the code !

Is there a methode or an idea to do this modification dynamically ?

Thank’s for your help.

2

Why You want to Merge two customer ??

Ideally it is not correct to modify the ledge tables ?

If you want to start transaction with customer 2 then block the customer 1 once customer 1 balance is reconciled and then transfer the closing balance to customer 2 and start transactions on customer 2.

3

Hi Raf,

Your code is basically fine and would work with most other tables in NAV.

But there are a lot of things you also need to take care of before it will work. And that is all the tables where Customer No. is part of the primary key. The Bank Account table is just one of them. Others include Customer Comments, Sales Prices, Discounts, Dimensions, Ship-To Addresses etc. You need handle all those tables in your code before you can run the RENAME customer code. This means that you will have to RENAME these tables before you can rename the customer.

In the case of your bank accounts, then when you run the RENAME it will only rename the Customer No., so you will have to rename the B1 for Customer2 to something else first. So before running the customer rename you have to do something like:

BankAccount.SETRANGE("Customer No.",Cust2."No."); IF BankAccount.FINDSET(TRUE,TRUE) THEN REPEAT I := 0; IF BankAccount2.GET(Cust1."No.",BankAccount.Code) THEN BEGIN REPEAT I += 1; NewCode := BankAccount.Code + '-' + FORMAT(I); UNTIL BankAccount.RENAME(Cust2."No.",NewCode); UNTIL BankAccount.NEXT = 0;

This is assuming that the Cust1’s B1 not 100% sure is the same as Cust2’s B1. If they are then you can just replace the second REPEAT loop with a BankAccount.DELETE;

And then you need to create similar loops for the other tables.

4

Hello,

Thank you for your responses, so I have to do this treatment for each table (What happen if I forget one table) ?! when I add a new table in database which contents a customer No. I have to add it in the treatment also in this case !?

I had another idea, but it doesn’t seem working :frowning:

I did a report which scans all table which has a relation with a customer using the virtual table "Field’, so i did :

//I take only the records that content relationTable = 18

Field - OnAfterGetRecord()

RecRef.OPEN(TableNo);

FldRef := RecRef.FIELD(“No.”);

FldRef.SETRANGE(FromCustNo);

IF RecRef.FINDSET THEN BEGIN

FldRef.VALUE(ToCustNo);

FldRef.VALIDATE;

IF NOT RecRef.MODIFY THEN //the case of customer no. in primary key

IF NOT (TableNo IN [17,21,32,96,160,179,203,253, 254,265,271,272,337,338,355,379,380,454,472,6507,5802]) THEN //Normally it should not remove the entries, but for safety I added this condition.

RecRef.DELETE;

I have problem with modify ! For example for bank, it shows an error : bank account does not exist for customer xxx I didn’t understand why it tests if it exist or not !!! I did just a simple modify !

Do you any idea why it doesn’t work ?

5

Hi Raf,

You ask what happens? The answer is nothing. Except your customer rename will fail. So as long as you do all the rename commands in the same transaction and not use the commit command, then your safe. But I still highly recommend that you make sure you have a usable backup and I also suggest that you test it in a copy of your database first!

6

Hi Raf,

There’s also a problem with your approach to identifying foreign key fields that relate to the Customer primary key. Looking at the Field table is a good idea, very cleaver; and in the cases you’ve described, the logic is correct. However, this approach will fail to identify conditional foreign key fields. You recall that a conditional table relationship is one where the table to which the foreign key field relates is dependent upon the value of another field in the same table. For a perfect example, take a look at t_81 Gen. Journal Line, fields Account Type and Account No. The values for Account Type include G/L Account, Customer, Vendor, etc., and the values in Account No. field will be related to whichever table is specified in the Account Type value. And you won’t be able to detect this type of table relationship in the Field record. So, while the approach you’re using may identify many of the related fields, it won’t identify all of them. And, as you’ve observed, the approach will also not accommodate future code changes that involve new uses of foreign keys related to Customer.

(That you mentioned the need to anticipate new tables or fields that might be related to Customer is also a bit disconcerting, since it suggests that you see this consolidation tool as part of an on-going process, which really makes me wonder what it is that you’re doing that necessitates this kind of solution.)

I think the bigger issue that everyone is hinting at is that, even though you may find a technically feasible method for consolidating Customer records, you’ll have a hard time convincing any of us that doing so is a good idea, or that it is the best solution to your business need. Amol opened the door by asking about modifying ledger entry records in this way, and I believe that if you mention the idea of altering posted ledger entries to your company’s CPA’s, their hair might actually spark on fire as they run screaming from the building. And if you’re a publicly traded company, the consequences of this approach could become quite dire. All that to say that there’s much more here to consider than just the question of how you might accomplish such a task.

Just food for thought …