Merging duplicates with child records

[Matt.C]

I'm trying to figure out the best way to structure my data, so that I could "merge" duplicate parent records and combine all of the child records.

 

For example:

"ABC Enterprises" has a number of child records and linked records (contact names, work orders, asset records, etc.) - meanwhile there are second and third records "ABC Enterprises Ltd." and "ABC Enterprise" that has similar child and linked records. 

I've attached a file with some sample records. This example is overly simple to illustrate what I'm trying to do. In real-world, each company would have many other pieces of information (address & contact info, warranty documents, etc.) across a number of different tables. I'm guessing the process would look something like delegating a "master" company record.

If it's feasible, I would like to use the same process to combine other records, such as duplicate phone numbers, contacts, etc. 

I found a few articles online, this process is used sometimes for medical records and is apparently referred to as a "Master Patient Record" or "Master Name Index" from what I can see. 

What is the best way to accomplish this?

Thank you!

ABC sample.fmp12

Edited May 18, 2020 by Matt Cudmore