4 Replies Latest reply on Dec 9, 2008 8:25 AM by TSGal

    Combining Databases



      Combining Databases


      I hope come can help me...


      I am wondering if there is a way to combine two FileMaker Pro databases. For instance, I want compare a list of names on database with those in another by creating an entirely new database. Is this possible? I would like two be able to "compare " the information (thousands of names) in an efficient way without having to go through one by one. 


      Thanks so much!! 

        • 1. Re: Combining Databases

          I did a custom version of this for a customer. The customer had a filemaker database and an excel spreadsheet that was created originally from an old version of the database. They had updated the excel spreadsheet with new names and some corrections to the data associated with the original names. Of course, just to make it complicated, they had also updated in the filemaker database. What they wanted was a filemaker database containing one record per person with the "correct" data for each one. I built them a combination "automatic+human" solution that did most of the work automatically, but allowed a human to choose the "correct" info when the solution couldn't.


          The solution was specifically done for that customer, so I was able to cut many corners to get them what they needed at minimum cost to them. I think it ended up costing them a few hundred dollars. 


           If you'd like a quote, please contact me via the direct message feature on this forum. It's against forum rules for me to post my contact info directly.

          • 2. Re: Combining Databases

            The problems are

            Are the name spellings correct (initial vs name for example)

            Are the addresses the same (date of change?)

            Is the phone the same (date of change)?

            Is the email the same (date of change)?

            Is there a modified date in both databases?


            If you import both into a new database, you can set up calculation field that will concantenate the lastname, firstname, firstfourinaddress OR lastfourinphonenumber OR some other easily compared dataset.

            SmithJohn2254 for example.

            This would ease the pain of eliminating duplicates with a script.



            • 3. Re: Combining Databases

              That would be exactly what I'm looking for! The first name, last name, etc. fields (of most files) would be identical. Basically we want to compare who is in one database with who is in the other. There would be a lot of overlap, but we are interested in a quick way of finding out who is not in BOTH databases. 


              How would I import the records into a new database like you mentioned?


              Thanks so much!!!! 

              • 4. Re: Combining Databases



                Thank you for your posts.


                When you import from another database/table, your "Import Field Mapping" has an option for "Updating matching records in found set".  Below that, there is a checkbox for "Add remaining data as new records".  That is, you can match the records from the other table to the current table, and for those records that don't match, we can add them to the file.  As far as the matching fields, you would select both First Name and Last Name fields.  However, this will not tell you which records are not in BOTH databases.


                Off the top of my head, I would create some temporary fields in the current table:


                Constant (Number)

                ImportConstant (Number)

                ImportCalc (Calculation: Number) = Constant + ImportConstant


                Then, I would Show All Records, put my cursor in "Constant", enter 1, and replace across all records.  That is, every record has the value "1" in Constant. 


                In the imported table, I would create one field:


                ImportConstant (Number)


                Like above, I would replace the value with 1 in "ImportConstant" for all records.


                Now, when I import, if there is a match of First Name and Last Name, the value from ImportConstant in the external table is imported into the ImportConstant in the local table.


                All records that do not match are added to the end of the current table.


                For all matching records, the value in "ImportCalc" is 2 (1 + 1).  For all records that don't match, the value is 1.  That is, if it exists in the current file but not in the external file, the value in Constant is 1, and the value in ImportConstant is 0.  The reverse is true for the extra records added to the end of the file.  That is, Constant is 0, and ImportConstant is 1.


                I'm sure others will pipe in with their take on it, but this is an easy way to understand it, even if it does require extra fields.



                FileMaker, Inc.