Merging Relationships?

[LaRetta]

Hi everyone,

We work very hard to keep duplicate customers from getting into our system. But even our best efforts occasionally fail and we end up with two customer records for the same customer. Before FM it happened a lot. I'm attempting to clean them up and would like to establish a process to MERGE two customer records. Sounds simple. But by the time we identify these duplicates, it isn't just the customer record any longer - this CustomerID has now created records in Addresses, Numbers, Invoices, Commissions, Billing, Notes, Activities, and History. These related tables usually contain many related records in each table. I envisioned a script to go to each related table and rewrite the CustomerID. It sounds complex, prone to error and time-consuming. At the very least, it is NOT elegant.

It struck me that I might be able to cheat by simply taking the 'extra' CustomerID and inserting it as a multi-line with the 'real' CustomerID in Customers. I wouldn't need to do anything with these related records then. The duplicate Customer record can then be deleted and all related data would group with the correct customer record. Might there be hidden problems with this idea that I can't see? How would you all pull something like this off? I want to establish a standard routine to 'merge duplicate customers' and all their related data. Our CustomerIDs write EVERYWHERE. Ideas very much appreciated. BTW, I have the standard structure using CustomerID (yada yada).

LaRetta

[Ocean West]

I would recommend abandoning the two Original customer IDs and create a new one from the system you have built. Then move the two or more duplicate ones data and children to this new one. Through a scripted process compare the data from each prior record(s) and provide a conflict report to determine which of the duplicates you wish to keep. So that the NEW official record may be a collaboration of data merged from two or more duplicates.

Customer ID's being everywhere i assume you mean everywhere but not in view of the user as typical keys are never displayed to the user.

[LaRetta]

You are correct, Stephen. The CustomerIDs are hidden for internal use only. Okay, I will script it. The more I thought about turning the CustomerID into multiline, the more concerned I became. So much of my process expects to find only ONE CustomerID and all it would take is to miss one and I could have problems.

Thank you!

LaRetta :wink2: