6 Replies Latest reply on Jun 2, 2017 10:52 AM by gbenna

    pair entries of two databases and share information.

    gbenna

      I have two databases with users names, addresses, email in each entry.  I want to match up the the entries (1 to 1, 2 to 2, etc.) of each database and share the info between them.  There are fields for match information in each database entry.  I am complete novice, any ideas?

       

      I am including a picture of the database layout

       

       

      Screen Shot 2017-05-31 at 10.23.24 AM.png

        • 1. Re: pair entries of two databases and share information.
          Jaymo

          Trying to automate the merging of the two databases is going to be difficult using an import or a script. It really requires a human to make judgements about what information is different and the same. I would import the data into one database and then use the duplicate search feature to find duplicates. The exclamation point finds duplicates but can only be used on one field. You'll need to create a calculation field for searching. I usually use the following:

           

          name_first & " " & name_last & " " & LeftWords(address1; 1)

           

          The LeftWords function grabs just the number from the address to account for any spelling differences (e.g. Street, St, St.).

           

          Once you have found duplicates, you can sort the database and the duplicates will all be next to each other. Someone at that point can go through the list and decide how to merge them. Make a backup before going through the duplicates, just in case.

          1 of 1 people found this helpful
          • 2. Re: pair entries of two databases and share information.
            beverly

            Plus one on the 'difficult'! I've seen comparisons where the names are slightly different: 'Bev' not 'Beverly' and of course these are not going to match. Using Jaymo's advice, I'd still use these results as "best guess" and put the human factor in there to verify all matches, too.

             

            Beverly

            • 3. Re: pair entries of two databases and share information.
              Malcolm

              Firstly, work with copies of the databases.

               

              I usually create relationships based on different parts of the data. For instance, relate first name table A to first name in Table B and last name in Table A to last name in Table B. Using that relationship, use a script to loop through the records evaluating other pieces of data. As the script loops over each record it leaves a trail, e.g.,

               

              All Fields Match

               

              or

               

              phone number is different

              email is different

               

              You can immediately remove all the records where all fields match and begin inspecting the remainder. That is round one.

               

              As Beverly has pointed out, names could be different, so the name relationship may not work. That will leave a number of records that didn't have a match at all. Try again, using a different field for the relationship. Phone numbers and email addresses make good matches because they are usually unique to a person.

               

              Repeat the process using different methods until you are satisfied. When you are ready, you can import your good data into your database.

               

               

              Malcolm

              • 4. Re: pair entries of two databases and share information.
                gbenna

                Ok I think I was not clear

                lets say I have

                0225,kathy,jones,kathy@jones.com

                0227,john,kelly,john@kelly.com

                etc in one data base  and

                 

                0354,patty,hit,patty@hit.com

                0367,gary,name,gary@name.com

                etc in the other data base

                 

                I want to pair up the entries and send them each other info so

                kathy would get patty's info and patty would get kathy's info

                john would get gary's info and gary would get john's info

                and so on and so forth

                so the first entries would exchange info

                the second the second

                the third the third

                This is what I was trying to get at

                • 5. Re: pair entries of two databases and share information.
                  Jaymo

                  I would place a serial number field in each database and then use the Replace Field Contents to reserialize the databases so each record has a corresponding record in the other file. You can then create relationship between the two files via the serial number field and do a simple looping script through each file to send the related contact information to each person.

                  • 6. Re: pair entries of two databases and share information.
                    gbenna

                    So what I did was do a find for 2016 domestic individuals and saved it with the first name, last name, email address, matched first name, matched last name, and matched email address fields. I then updated the matched fields to be the same as the regular fields. Then I saved 2 copies of that FMP database.

                    I then did a find for 2016 international individuals and did the same as above to it.

                    Finally I imported the matched fields from the domestic database #1 into the international database #2 and from the international database #1 into the domestic database #2.  worked ok. Is there any way to join two fields with an @. In other works can I join a Gary field with a me.com field with a @ in between So I get gary@me.com in a updated field?  Thanks everyone for your help