3 Replies Latest reply on May 17, 2011 1:50 PM by philmodjunk

    How to reconcile two lists?



      How to reconcile two lists?


      FileMaker Pro Advanced 10

      Have two lists:

      1) 1,300 records.  Basic mail list - Name, Address, C/S/Z, etc.

      2) 58,000 records.  Exported from voter registration database.  Has similar fields to above plus several others which I don't need.

      Imported both lists into FM database as two tables.  ("Main List" & "Voter List")

      Want to reconcile "Main List" with "Voter List" such that a name on the "Main List" can be verified against "Voter List."

      If name/address, etc. on "Main List" matches name on "Voter List" then I want to:

      1)  Mark name on "Main List" as "Verified."

      2)  Set field "Main List:Political Party" to be same as similar field in "Voter List"  (e.g.  Democrat, Republican, Independant, etc.)

      How can I do this?  I assume it's going to be with scripting.  Otherwise, I am stumped.


      Randy S.

        • 1. Re: How to reconcile two lists?

          The following process is pretty simple to do by hand, but can also be scripted.

          Keep in mind that the quality of your data could really cause problems for you with alternate name spellings, minor variations in address fields, etc. preventing a record in Main List from matching to the voter list.

          Define a relationship between the two tables that matches by all the fields you want to match in order to mark a Main List record as verified. You'll have something similar to this:

          Main List::FirstName = Voter List::FirstName AND
          Main List::LastName = Voter List::LastName AND
          Main List::City = Voter List::City AND
          Main List::StreetAddress = Voter List ::StreetAddress

          You may need to construct calculation fields in one table or the other to get fields you can use in this relationship that properly "match" like you need here.

          Once you have your relationship setup. You can use Replace Field Contents to update the verified field with a calculation such as:

          If ( Not IsEmpty ( VoterList::LastName ) ; "Verified" )

          In like manner, you can use Replace Field Contents to copy the value from the political party field in voter list to the political party list in Main List.

          • 2. Re: How to reconcile two lists?

            Very cool!  Thank you!

            I think I will create a calculation field in each table such that the restult is something like FirstName+LastName and maybe another one like StreetAddress+ZipCode.  (No need to use City/State/Zip because the ZIP code is unique to a city and state.)

            Each record in the "Main List" table has a unique ItemID number, serially assigned upon creation of the record.  I suppose I should create a similar field in the "Voter List" table too.  That way, I can copy the ItemID number from the Main table to the Voter table as well.  Then I can create a relationship between those two tables which will "lock" the records in the two databases together in case I ever want to refer to them again.

            In other words, it would be a way to save my work for other uses.  Right?

            • 3. Re: How to reconcile two lists?

              The serial number based relationship sounds like a very good idea. The calculation fields will work, but you don't actually have to use them in the relationship unless that's simply a way to get the data on one side of the relationship into a format that matches a field on the other.

              And keep in mind that you aren't limited to using a single relationship to link records by name, address between your two files. There are any number of different relationships you may want to work with that match only by last name or only by full name as a way to look for matches between the two tables. You may even want to use a filtered portal where the text you type into a search field drives a portal and a scriptTrigger performed script so that each key stroke in the search field reduces the list of matching entries shown in the portal.

              Check out the two portals at the bottom of this Demo file for ways you might use this when searching these tables for a specific person: