3 Replies Latest reply on Aug 10, 2010 12:24 PM by philmodjunk

    Matching on Entity Name Before Import

    vhobbs

      Title

      Matching on Entity Name Before Import

      Post

      I periodically merge thousands of new records into an existing FM DB.  I need to maintain the data by entity (person, company, or committee), so that over time each person will have a variable number of records associated with him/her. My problem is that I don't know an easy way to assign matching or new serial entityID #s to new records without doing a vlookup in EXCEL first to find the entity names matches and associated EntityID#, then manually assign new serial ID#s for new entities, then re-merge the two sets of new records and import into FM.  There must be an easier way. Can anyone help?

        • 1. Re: Matching on Entity Name Before Import
          philmodjunk

          On problem here is that names are not unique. If you set filemaker or other software up to do the matching, you may inadvertantly match data that shouldn't be matched.

          You appear to have two tables:

          Entities------<EntityData

          where Entities::EntityID# = EntityData::EntityID#

          You could import the data into filemaker first and then use filemaker scripts to match values--assigning the EntityID# to EntityData records that match existing records in Entities. EntityData that doesn't match--their EntityID# fields will still be empty can then be pulled up in a found set so that Import records can copy the needed name data from EntityData to Entities and then the same matching logic can be repeated to assign the matching EntityID# values.

          You can set unique values | validate always settings on one or more fields in Entities to filter out duplicates when importing this data from EnitityData into Entities. If "unique values" means a combination of fields (such as name and address) are unique, you may need to define an auto-entered text field that combines this data in a single field and set the validation rules on it.

          Note that the more typographical errors you have in the data you are using to match entities, the more messy this process becomes and the more likely you will need human oversite to fix errors.

          • 2. Re: Matching on Entity Name Before Import
            vhobbs

            I can live with potential of non-unique matches. The much larger problem is dealing with the lack of any fuzzy match capabilities when the same entity may be listed in multiple ways. As I presume there is no way around that problem, my current issue surrounds the matching script you mention. Can you clue me in how to proceed in writing the script?

            • 3. Re: Matching on Entity Name Before Import
              philmodjunk

              Using just a typical name matching scenario, let's say we want to match records if Name and Address both match.

              1. Define a relationship to a second table occurence of Enitities:

                EntityData::Name = EntitiesByName::Name AND
                EntityData::Address = EntitiesByName

              2. Immediately after importing the new data, your imported data comprises the found set for EntityData.
              3. The next step in the script can thus be:
              4. Replace Field Contents [No Dialog ; EntityData::EntityID# ; EntitiesByName::EntityID# ]
              5. That will copy the EntityID# of a matching record in Entities while leaving the field blank for records that do not match on these two fields.

               

              Now to add the new records...

              1. Define a new text field in Entities, NameAddress and define an auto-entry calculation for it as: Name & Address. Clear the "Do not replace existing value..." option so editing the name will update this field.
              2. On the Validation tab of field options, specify Unique Values and Validate Always for this field.
              3. Now add these script steps to your process:

                Enter Find Mode[]
                Set Field [ EntityData::EntityID# ; "="]
                Set Error Capture [on]
                Perform find []

              4. This will find all imported records that don't have an EntityID#
              5. Now you can import this found set into Entities and the validation rule on NameAddress will keep duplicate values from being imported.
              6. Finally, repeat the above replace operation now that you have matching records in Entities so that all records in EntityData have EntityID# values that match a record in Entities.

               

              Note that "fuzzy match" operations are not strictly impossible in filemaker as you can script finds that use wild cards or use different permutations of the imported data to search for a matching record instead of this relationship based approach.