1 Reply Latest reply on Jul 29, 2011 11:11 AM by philmodjunk

    Need to Merge two databases without overwriting all fields



      Need to Merge two databases without overwriting all fields


      I have two databases of contacts. Some of the contacts are present in both databases, but not all.  Each of the two databases has important information present only in that one database. I can't overwrite them, I need to merge the two together such that blank fields in one database are populated by the information in matching fields in the other, and vice versa. I have created a unique identifier for each record (lastname_firstname_city) to set as the index key (is that the term?).  I'm okay with letting one database overwrite existing information in the other when that information is present in both databases. But it's the fact that each has important information in them that is lacking in the other database. I have over 2000 records, so doing this 'merging' of data one-by-one is a daunting task.

      I'm pretty new to Filemaker, and a moderately good Excel user. I'm tempted to hire this out but don't know even how to do that!

      Thanks in advance.

      S Quirk

        • 1. Re: Need to Merge two databases without overwriting all fields

          Since the data may be missing for a given contact in Either file, you'll need to do something a bit more sophisticated than just an import reocrds with the update option specified.

          First make back up copies just in case a mistake screws up your data.

          You can use your key (sure it's really unique and don't have two contacts such as "john smith" with the same city?) to link the tables in your two files in a relationship. (If you don't know how to link two tables from separate files, open Manage | Database | Relationships in one file, use the button at bottom far left to add a new table occurrence and use the data source drop down to add the other file's table as a FileMaker data source.)

          Then you can show All records and use a series of Replace Field Contents with calculations to copy data from the matching field in the related table to only if the current file's field is empty. You'll do this for each such field in the table either by hand or with a script.

          Once you've done that, you may still have records in the other file that don't have a matching record in your current file. You can go to the layout to the new table occurrence you just added and place the key field from your current file's table on this layout. Enter find mode, enter an * in this field and select the Omit option before performing the find. This should bring up all the records in the other file that don't have a matching record in this file. Now you can return to your original layout and use Import records to import records from this table occurrence (not directly from the other file) to import just the records that don't match. (Select the current file instead of the other file, then specify the table occurrence to the external file or you wont' import the found set you just produced. )