7 Replies Latest reply on Nov 20, 2009 9:32 AM by FentonJones

    Update matching records



      Update matching records




      I am a new user to Filemaker Pro. I have Version 10 and am using Windows XP, Service Pack 3. 


      I am trying to start up our database and I must say that Filemaker has been pretty user-firendly so far to someone who isn't very technology-oriented, like myself.  


      My question is: How can I import records to my existing database and have Filemaker Pro eliminate the duplicates? 


      Sounds simple enough, but I've run into a road block. 

      - So I have my current database

      - I clicked on "Import Records (File...)" from the File menu up above

      - I chose the Excel spreadhseet of additional records that I would like to add 

      - The next prompt was "Specify Excel Data" and I chose "Display Worksheets" as opposed to "Display named ranges," and hit "Continue..." Not sure why I did this, but it seemed like a better option.

      -  The next prompt was "Import Field Mapping." I clicked on "Import matching records in found set" and hit "Import..." 

      - This is where it tells me "At least one pair of matching fields must be specified for the 'Update Matching Records' Option" 

      - I don't know HOW to specify one pair of the matching fields. 


      Can anyone help with this?


      Thanks so much. 

        • 1. Re: Update matching records

          Are you updating existing records in Filemaker or importing records that do not yet exist in your database file?


          To use this option you need a column in your spread sheet with a value that matches with a value stored in a field in your database table. You have to drag the right hand fields (columns in your spreadsheet) up and down until you have it aligned with the matching field in your table. Click the line between the two fields to change it to an = symbol and you've specified a pair of matching fields. Data from your spreadsheet will overwrite the data in the matching record of your database table according to how you align the fields in your table with the columns in your spreadsheet.


          Note: given the many options in Import Records and the possible mixups that can happen when you pick the wrong import options: 

          1. Read up on this tool in the Help system.
          2. Make a backup copy of your file before trying to import the records.
          • 2. Re: Update matching records

            I am both updating existing records in Filemaker AND importing records that do not yet exist in my database file. What is happening is I have uploaded MY list of clients (897). I want to import my Partner's list of clients (1044) to my database and eliminate the matching contacts we have who have the "exact" same information for each field/column. We want to KEEP our matching contacts that have DIFFERENT information because we are going to have to go through those together one by one to determine whose contact info if more accurate.  Does that make sense? is there any easy way to do this?


            I tried doing what you suggested but either I am not doing it correctly, or it simply isn't working. 

            • 3. Re: Update matching records

              I think it'll be much easier to simply import all your records and then process the results post import. The matching records option will overwrite data in your existing records if the selected fields contain matching data and this won't give you the option of keeping or deleting the record based on "who has the most accurate data".


              Before I spell out a method for this, a few question about your table will help:


              Do you have a field in your table with an auto-entered serial number setting?

              Is this field used to link your records to a related table?

              Are there any other fields set with auto-enter field options? If so, what are their setttings?


              There's a way to eliminate identical records during import, but the above details have to be handled carefully to avoid creating new problems.

              • 4. Re: Update matching records

                Yes, I figured that I would probably just have to import both records, and clean it up thereafter. I was trying to avoid that because that would mean we would have to go through close to 2000 contacts one-by-one.


                To answer your questions: 

                Do you have a field in your table with an auto-entered serial number setting? NO

                Is this field used to link your records to a related table? NO

                Are there any other fields set with auto-enter field options? If so, what are their setttings? NO


                I honestly don't even know what an auto-entered serial number setting is, or how to create it. 

                • 5. Re: Update matching records

                  Auto-entered serial numbers are the main work horse for setting up relationships between tables in Filemaker. This is a number field with the serial number option selected in the field's field options dialog. I suggest you learn about this feature as you'll probably need it sooner or later.


                  In this case the lack of such a field actually makes life simpler for you.


                  Let's say you want to consider a pair of records identical if the following three fields, contain identical data: Firstname, Lastname, Address.


                  Define a text field UniqueField, while still in Manage | Database | Fields, click the options button to bring up the field options dialog for this field.

                  Click the Auto-Enter tab.

                  Click the Calculated value check box and enter the following expression:


                  Firstname & " " & Lastname & " " & Address


                  Click OK and clear the "Do not replace existing value..." check box.

                  Click the Validation tab

                  Click the Validate data in this field Always radio button.

                  Click the Unique checkbox.


                  Click OK until you've left Manage | Database.

                  Now, when you perform your import, Select the "perform auto enter operations while importing" check box.


                  if the first three records imported show

                  John Smith 1333 Main St.

                  John Smith 1333 Main St.

                  John Smith 1333 Main St.


                  The first record will be imported and the following two will not be imported, thus filtering out the duplicates. In your situation, you'll need to look over your fields and decide which ones to include in your auto-enter calculation.

                  • 6. Re: Update matching records
                       Seems a little daunting considering my lack of IT knowledge but I will certainly try it and am grateful for the info. I will let you know the outcome or if i encounter issues along the way. Thanks so much!
                    • 7. Re: Update matching records

                      As PhilModJunk says, there is a way to stop multiple exact duplicates during Import. But I would not do that, unless I could be sure that it was 100% effective. Basically I don't use the "import then clean up later" method. I use, "compare and clean up now, then import."


                      You can link 2 databases and compare them, in many ways. You can identify and delete exact duplicates from (a copy of) the database to be imported. You can also drag-drop data between them, to update individual fields.


                      There has been, in every case I've seen, no way to accurately compare all the data of all the records of two databases which have people's information using a totally automated method. What you are trying to do is a process of elimination. Start by eliminating the easy to match records, then work your way down the line, using other less likely but possible matches.


                      The main piece of data about people is their names. Now, I don't know about your data, but it is very common for people's names to be entered slightly differently. It is also common that more than one person can have exactly the same name. If you rely entirely on the name you may get both false positives (but very few) and false negatives (more, misspellings, different entry).


                      Other pieces of data can also be used. Addresses (but people move), phone numbers (cleaned to numbers only). Addresses can be problematic, mostly because of variations (North, N., N, Drive, Dr., Road, Rd., etc.). What I do is only use the first word, and the first part of the 2nd word. Yes, I get false positives. But at that point I am trying to find "possible matches that were missed by the names match."


                      One of the best methods to look for possible matches is to create a multi-line field.


                      Name & ¶

                      List (phone numbers, numbers only) & ¶

                      Addresss (cleaned calculation)


                      Match these and you'll pick up a few more.