I obviously know how to renumber a field in a particular database using Replace Field Contents.
However, I need to "renumber" a 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 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.
You are updating a key field and this is difficult but not impossible to do without changing the relationships.
1. Generate NEW fields to hold temp keys in the exisitng files.
2. Generate a new key set of fields in the 'Company' file.
3. Use the OLD relationship and the OLD key to find matching records in a secondary file.
4. Update the NEW key field in the secondary databse.
5. Continue for all relevant files.
6. NOW duplicate the values in the NEW key fields into the OLD key fields for ALL files.
The tricky part is making sure you have completed the duplication BEFORE you wipe out the old entry.
There are other ways to accomplish this, but not many without recreating the relationships or doing massive exports and reimports.
Depending on the complexity of the project, choose your poison.
Thank you. With assistance from many posts in another forum, 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.