Jump to content

Merge two dBs

Recommended Posts

I am trying to merge two databases together. Because fields are in portal, no idea how to accomplish. Adding to Table LineItems layout is no problem. Both dBs have same fields, of which the below are relevant. (These are samples. Actual dB1 goes from year 2000 to 2021 with several thousand records and over 200 for dB2 monthly records for 2022 only -- actual dB originated as something else so some fields I didn't use but didn't want to delete to prevent breaking anything)

2001 dB sample.1 dB
Table Hours detail layout> one record per month 2001 (one record per month per year = 12 records; one per month for one year)
Table LineItems > one record per event per month per year (12 records, one record per event, per month, per year cumulative)
Table Charities > one record per charity

2002 dB sample 2

same format as above for year 202   

2001 dB sample.fmp12 2002 dB sample.fmp12

Link to comment
Share on other sites

If the two files have the same structure, then you should be able to import the newer records into the old file (or, perhaps preferably, import both into a new file).

However, this can work only if the parent records have unique primary keys. Glancing at the provided samples, that is NOT the case here: in both files, the Hours Detail::kp_ID_Billing numbers go from 261 to 272. If that represents your real situation, then you will have to renumber both the parent records and their children in (at least) one of the files, before attempting to merge the data.


Link to comment
Share on other sites

Opened the 2002 dB and see the 261-272 for the kp_ID_Billing field on both Volunteer Hours and LineItems.

I opened the 2002 dB and added the field kp_ID_Billing to both layouts so I could see it. On the Volunteer Hours layout, I changed to 361-372. The entries in portal remained. However, on the LineItems layout when I tried to match new range (manually, the Replace All sequential number didn't work), the data deleted from portal in Volunteer Hours layout. 

I then looked at my actual 2000-2021 dB and see range for kp_ID_Billing on Volunteer Hours layout is 2-249. The range for 2022  Volunteer Hours layout is 175-187 so they do overlap. 

Not sure how to correct my sample files in order to replicate on actual files.

Link to comment
Share on other sites

You cannot replace the primary key values in a parent table without replacing the foreign key values in the child table too. Otherwise you will break the relationship and the child records will become orphans - or even worse, will be "adopted" by other parents.

I am afraid your files are too complex for me to give you specific advice - that's why I am speaking in general terms. The general procedure is this:

  1. Define a new field in the parent table (let's call it NewPK);
  2. Define  a new field in the child table (let's call it NewFK);
  3. Populate the NewPK field with new values;
  4. Using the existing relationship, populate the NewFK field in each child records with the value from the NewPK field in the parent record;
  5. Now you can switch the relationship to use NewPK and NewFK as the match fields. Or, if you prefer, copy the new values in both parent and child records into the old PK and FK fields.

And of course:

    0.  Make a backup in case you make mistake and need to start over.


Edited by comment
Link to comment
Share on other sites

IT's not that my task is complex, it's more my not understanding the details required to accomplish my task. While I've used Fiemaker for many years, never enough to understand all it requires for proper running dB. I usually start with one of the solution samples and modify from there. I am weak at the relationship requirements so not always correct. (One is from 2000-2021 and the other is 2022.) Will manually add the 200 records so far for 2022 to the 2000-2021 and continue from there.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.