2 Replies Latest reply on Jan 23, 2014 9:03 AM by yomango

    matching-comparing Information

    yomango

      Title

      matching-comparing Information

      Post

           Hello, fellow forists. I am looking for a guidance when comparing two text fields from separate tables.One lists property owners, around 19K. The other table lists court cases (200k+ cases) where the defendant may or may not have a property. I want to find that out one way or the other. Fine up to here.

           My first problem is that the defendant field could have more than one defendant . (ie, City of Orlando, John Doe, Jean Smith and others). And my second and most serious problem is that for instance, in the Property table the property owner field reads CITY OF ORLANDO, but in the case table the defendant may read THE CITY OF ORLANDO or ORLANDO CITY or CITY OF ORLANDO or CITY OF ORLANDOet al,etc, etc. Same for people's name, in one table their name reads JOHN DOE or DOE,JOHN or JOHN DOE Jr, or JOHN L DOE, etc., I guess you get the idea. Is there a way to go about comparing and matching this two fields. Another instance, the defendant goes ORANGE COUNTY or ORANGE COUNTY SHERIFF DPT, or SCHOOL BOARD OF ORANGE COUNTY, or any other combination.

           It has been a pain for me. I am reaching for your knowledge and guidance. Thank you

        • 1. Re: matching-comparing Information
          philmodjunk

               Messy data is one of the biggest headaches you can have to deal with in a database. There are no simple, fool proof solutions. Not only do you have the issues you have described, but there also will be cases where the names may match but be the names of different individuals.

               Best approach, that I can think of would be to use the "contains text" search portal method illustrated in this demo file:https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               You can type in some text such as ORL

               and get a list of possible matches such as City or Orlando, Orlando, Orly... from which you can click to choose a specific match.

               If you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open this file and convert a copy to the newer .fmp12 file format.

          • 2. Re: matching-comparing Information
            yomango

                 Thank you, Phil. I will study the sample to accommodate it to my needs.  You are a life saver, I really appreciate the time you take to respond.