5 Replies Latest reply on Nov 6, 2009 8:40 AM by david_lalonde@d-cogit.ca

    Comparing and Merging Records from 2 Databases

    jackibar

      Title

      Comparing and Merging Records from 2 Databases

      Post

      Hi,

       

      Is there any way to do this?  I have 2 FMP databases and need to verify that everyone in the smaller one is included in the bigger one - the email address is the field I need to compare.

       

      I'm using FMP 10, Mac OS 10.5.8 and have been using FMP for about 10 years now...

       

      Thanks so much for any help!

       

      - Jacki

        • 1. Re: Comparing and Merging Records from 2 Databases
          david_lalonde@d-cogit.ca
            

          There certainly is a way to do this. And an easy one at that!

           

          In the smaller database, make a relationship to the larger one based on the email address.

           

          Always in the smaller database, create a new field that will contain a marker. Create a script that will loop through all the records (replace comes to mind) that sets the marker field if there is a corresponding record in the larger database. Run the script (actually, you could perform the replace yourself). Perform a search of all the records that contain the marker. You now have two sets of records.

           

          The found set contains records already in your larger database. You may want to perform some sort of synchronization.

           

          The ommited set contains records not already in your larger database. You will want to switch the found set so that you can import the missing records in the larger database.

           

          That's it! 

          • 2. Re: Comparing and Merging Records from 2 Databases
            jackibar
              

            Thanks so much for your quick response, David!  I really appreciate it...  Okay - I'm new to working with Scripts within FMP...!  Could you give me an example of what steps I need to tell the script to take to do the looping through the records?  I'm assuming there will need to be an If statement to where if the email is found in the 2nd database it then sets my marker to Yes or something like that...?  But I'm clueless as to how to set this up.  I've been playing with it for a while now and haven't gotten anywhere!

             

            Thanks again,

             

            Jacki

            • 3. Re: Comparing and Merging Records from 2 Databases
              david_lalonde@d-cogit.ca
                

              Here is another approach...

               

              Once you have created your relationship between the two files, define a new calculation field in the smaller of the two files. It should contain a calculation that looks like this:

               

              email field of smaller file = email field of larger file

               

              Make sure it returns a number.

               

              Once done, perform a find on that field. If it contains a 1, the record of the smaller file is contained in the larger file. It it contains a 0, the record of the smaller file does not exist in the larger file.

              • 4. Re: Comparing and Merging Records from 2 Databases
                jackibar
                  

                Thanks SO much, David - the 2nd option worked great and I figured it out quickly...!  I'd still like to learn more about the scripting in FMP - is there a place that gives example scripts or tutorials I could look at?

                 

                Thanks again for your help!

                 

                - Jacki

                • 5. Re: Comparing and Merging Records from 2 Databases
                  david_lalonde@d-cogit.ca
                    

                  You will find basic scripting instructions in FileMaker Pro's help.

                   

                  If you search the WEB, you will find a number of sites that show examples and techniques. You may also find some good books, one of which is available on FileMaker's WEB site. A trainer or coach may help you get on your feet fast. D-Cogit offers coaching and training. A class is another solution. You can find companies that offer classes on FileMaker's WEB site.