Seeking Multiple FileMaker Match Relevance

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

I need to create a relationship where "if A matches A . OR . if B matches B" relationship. Also, I have a situation where "if A matches A then put result C here" but I am getting multiple matches. More specifically, I have a database that has ZIP CODES and City Names and Latitude/Longitude info I have a database of customers that has Customer Name, Zip Code, City Name.

I want to add Lat/long info to the customer database but the problem is that if I match by Zip Code, some Zip codes are newer than the zip code database that I have.  If I match by city name, I can get several different zipcode results for the same city, even though the lat/long will remain the same across the found record set.

Not sure any of this makes sense but I'm sure it's simple for you . not so
much for me.

This reminds of something I did for a customer way back when FileMaker 3 was released. There may be a more elegant method but I feel pretty confident this avenue is still quite valid. What I had done is create multiple relationships between the source database and the information databases. The tables in each relationship are the same, they simply have different attributes that make up the relationship. So in your case, we may want to setup a relationship using city, zipcode and a combination. 

BEGINNER TIDBIT: FileMaker allows you to create more than one relationship comparison layer (commonly called multiple predicates), this is somewhat akin to the multiple available layers in the sort dialog box. Basically, you can setup a relationship like normal and then you can choose another set of comparisons for an additional relationship layer. This is what we did in the PARENT to parent_DATA_cityzip relationship.

Then I would explore creating a calculation field that can span across all three relationships and return a relevance ranking result. The calculation below gives you a basic idea how that might be accomplished.

You can see a returned value if both the city and zip match, if only the zip code is a valid match or if the city is a match. You could get more elaborate with this by having levels to detect if the first 4 characters of a zip code are a match, if the state is a match, if the area code of the phone number is a match and so forth.