Replace (Related) Field Contents assistance

[mecchaeric]

I obviously know how to renumber a field in a particular database using Replace Field Contents.

However, I need to "renumber" a related field called CompanyID in the Companies database, but have it change its related CompanyID in the Employees database to be equal, so relationship is not lost. In fact, there are several databases where related CompanyID resides, and I need all related databases w/ CompanyID to update as well.

How do I do this?

Any and all assistance would be greatly appreciated.

I wan't sure where to post this so forgive me if it wasnt best to post it here.

[Fenton]

I think you need to say more clearly what you're trying to do. "Renumbering" has little to do relationships or their keys. It shouldn't anyway. I can think of almost no reason to ever "renumber" or change a relational key, if your structure is sound; unless a company "splits" or some other drastic change. What is the "renumbering" for?

[comment]

I'd think twice about this, too. And make sure to have a decent backup before attempting a serious operation like that.

That said, you need a ADD a NewCompanyID field to the Companies table - this is where you'll do the new numbering, leaving the old CompanyID intact. Then go to each related table, show all records, and replace CompanyID with Companies::NewCompanyID.

Finally, either change the relationships to use NewCompanyID, or replace the contents of CompanyID in the Companies table with the new numbers from NewCompanyID.

[mecchaeric]

Basically, when I came on board there were 3 Companies databases and 3 Employees databases and 2 Call Log databases. I've decided to perform merge, so there is one Company database, one Employees database and one Call Log database. They are all centered around CompanyID and Employee ID. However, before I merge the databases, I need to resolve the fact that there are overlapping CompanyID's and Employee ID's, which will result in dupes in the main Companies database (the one I'm merging others into). I hope this is clear.

Is there a better way?

Again, thanx to all in advance.

Edited July 21, 2006 by Guest

[mecchaeric]

Thanx for your response. Please read my previous post for background. Trust me, I've thought about this many times and for many months. Is there a better way? I believe I understand your idea though, and It sounds great to me. I'll def be trying it (on offline Db's, of course, which is what I do for all MAJOR development). As for backups, oh yeah, no worries there either. My databases are backed up every 1/2 hr to separate hard drive, every night to separate hard drive, to my Mac weekly, an external hard drive weekly, burned to CD weekly, brought offsite weekly, etc. Thank you again.

Edited July 21, 2006 by Guest

[comment]

I don't know of a better way. You need a "foot on the ground", so to speak, so the related records can look at the correct (old) parent Company, and take the new CompanyID from there.

You may need to make adjustments for your particular situation, perhaps add a specifix prefix to each of the 3 old files, before merging them together (so eventually you will be doing this twice - once on 3 separate sets, just to keep them from mixing together in the merged file, then a final run with the actual new numbers). But the basic principle is to use the old relationship/s to fetch the new numbers.

[Fenton]

I thought there may be something like this. It is always possible to merge data and fix duplicates. The question is always how to it in the most efficient manner; that is, the way that gets accurate results with the less tedium.

Since each of the database has its own IDs, they are not going to be of great use. As comment says, you choose one of them as the "real" file, with its ID (auto-entered serial hopefully). Let's say you choose Company1 file. First, Company1 itself must be cleansed of duplicates. No duplicate can be deleted until its related records have been updated with the legitimate parent ID. This can all be done with small relational scripts; Go To Related Records, Loop through updating the ID.

To see whether you have duplicates between the files you must fall back on names. If you get solid matches, then you need to hunt down any related records of those, and update their IDs to the legitimate parent in Company1. Then the dupes can be deleted. Watch out for "cascading deletes", ie., "allow deletion of related records" in the children. Until you update their ID, deleting the parent can inadvertantly delete the children (if you're using a separate field for the "new" ID, leaving the old in place). Yeah, it's confusing to talk about. But if you think it over logically you'll be OK.

Non-matches between names in similar files, such as between multiple "company" files, is problematic. Because it could just be minor misspellings. You can try and find and fix these, for consistent naming, so they'll match. At some point however you're probably going to have to sort both files by name, and just scroll down, comparing.

You can also try other fields, such as addresses (just use the 1st part of the address; the last part is always a problem; "Rd", "Rd.", "Road", nothing, etc.). Then look at the results in a small portal (just a few rows). You'd be surprised how easy it is to find misspelled name matches if you do an address match instead. Make a button to set the ID if you see a match in the portal.

The process requires patience. You have to keep in mind that it is kind of big mistake to delete a customer -] There is a reason why database designers are so insistent about good relational design. It's because we've all spent hours trying to put together jigsaw puzzle data.

[mecchaeric]

Thank you. I ended up creating newCompanyID field (in database whose records i'm importing), and renumbered it. Then, I created newID fields in all other related files and based on current relationship(s), renumbered them. I did offline, as a test, and it seemed to go very well. Once I test it a few more times, check it many times, etc. I'll then import these records w/ renumbered ID's into THE main databases, using newID fields when importing. Again thank you all.