6 Replies Latest reply on Aug 9, 2013 8:35 AM by philmodjunk

    Match Field

    EricKnustrom

      Title

      Match Field

      Post

           I am brand new to FMP so please be patient with me.  I'm trying to import data from fields in one database to a second using lookup to find related entries in both. (I'm trying to get the address from db1 to db2, both have first name and last name that I will compare to figure out which to transfer)  I'm trying to create a "match field" based upon a support article I found [http://help.filemaker.com/app/answers/detail/a_id/5244/kw/lookup%20values] using the calculated value outlined (FullName (calculation, text result)=FirstName & "^" & LastName).  But after defining the field nothing happens.  Do I need to do something to populate the field?

           Thanks in advance for any help you can provide

        • 1. Re: Match Field
          philmodjunk

               You no longer need such a calculation field in order to match records in Filemaker. You can set up a relationship by dragging from the first name field in db1 to the first name field in db2 in Manage | Database | Relationships. Then drag from the Last Name field in one table to the last name field in the other so that you are using both match fields.

               But matching records by name is very problematic. Names are not unique. People change their names. Names take more than one form (some people call me Phil, others call me Phillip) and are vulnerable to data entry errors.

          • 2. Re: Match Field
            EricKnustrom

                 Thanks for the quick reply.  I did create the relationships.  I completely agree about the challenges with name but i have very few fields to work from.  I have the zip code in both databases so I can use that to double check varriations and I deduped before I imported the data. 

                 My only experience with Lookup is with spreadsheets.  So after the relationships are established do I just need to add a "Lookup" function for the fields I'd like to pull from db1 into db2?

            • 3. Re: Match Field
              philmodjunk

                   First you need to decide if you even need to do the look up. With the tables related and with matching values in the match fields, you can add fields from one table to the layout based on the other and the data is automatically displayed. You only need to copy over the data with a looked up value field option if you need to capture data from the first table so that it is preserved if the original data should change.

                   And example of this is when an invoice is set to look up (copy) contact data so that the data that was current at the time the invoice was created is preserved. (Sorry you didn't get your order Mr. Smith, let me call up that invoice and see what shipping address was used to send it to you...)

              • 4. Re: Match Field
                EricKnustrom

                     Ok, so since db1 has the full address information I'm trying to get into db2 (does not have full address, just the zip), I'm guessing there is a way to layout the info from both tables once the relationship is esablished????

                     Again, sorry for being dense. 

                • 5. Re: Match Field
                  EricKnustrom

                       for context db1 has over 13 million records and I'm trying to match up with 13k in db2

                  • 6. Re: Match Field
                    philmodjunk

                         Assuming this relationship:

                         db1::FirstName = db2::FirstName and
                         db1::LastName = db2::LastName

                         You can create a layout where layout setup | Show Records From lists db2.

                         WHen you  use the field tool to add fields to the layout or click Modify to add fields to a table view, you can then select the fields you want from both db1 and db2.