3 Replies Latest reply on May 15, 2014 7:04 AM by philmodjunk

    Matching two fields that don't exactly match



      Matching two fields that don't exactly match


           I have two tables, a customer database, and a list of streets with map grids.  I want to create a relationship with the customer addresses, which are full street addresses (IE 12345 Main St) with the street in the other table, which doesn't have house numbers (IE Main St).  Is there a way to create a relationship between two fields were the information doesn't match 100%?

           If that isn't possible, then is there a way I can create a field with a calculation where I can automatically copy the full street address but remove the house number, so that the new field will match the street field in the other table?

           If this is something easy to do, please forgive me, new to filemaker and just learning.  Thank you for any help.

        • 1. Re: Matching two fields that don't exactly match

               Your best bet is to put your street name in a separate field in the Customer table from the very beginning instead of combining it with the street number. That street name should then be a drop down list or pop up menu of street names from your 2nd table to make sure that data entry/spelling errors don't keep the records from matching.

               You can try setting up a calculation that extracts the street name from your current field, but this may not work for every address entered. In most cases, you can't just use a calculaiton that omits the number at the beginning as there could be an apartment or suite designation following the name of the street. So any calculation that you come up with will be at the mercy of how consistently the address was entered in the first place.

               You might want to use a partial text matching search portal to select the street record to link to a given address record. here's a demo file: https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               It's in the older file format so if you are using FileMaker 12 or newer, use Open from FileMaker's File menu to open the file and convert it to the newer file format.

          • 2. Re: Matching two fields that don't exactly match

                 That link you sent me provided the perfect solution.  Thank you.  If I could pick your brain for one more thing, is there a way to write in script to copy just the first five or six characters in a field?

            • 3. Re: Matching two fields that don't exactly match

                   I believe that others have already answered that question in a new thread:

                   Left ( YourTable::Field ; 6 )

                   will refer to the left 6 characters of YourTable::Field