Multiple Line Or Compound FileMaker Relationship Match / Key Fields

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
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). 

Generally speaking, the most common FileMaker table relationship is when a field in this table equals a field in that table. In fact, prior to FileMaker 7, that was all you can do. With the more modern versions of FileMaker, you can setup multiple predicate relationships using more than two fields and operators other than equal to.

However, there was a technique before FileMaker 7 that kind of allowed developers to use multiple fields in a relationship. One of the two fields was actually a calculation field that was made up of many fields.

But what if you wanted to compare one field but have more than one key value?

A multiple value key field is a great way to setup a many to many relationship. In fact, prior to FileMaker 7, it was the only real way to setup a many to many relationship. It all hinges on the values in the key fields being separated by a carriage return. These carriage return separated data strings are considered their own related values.

That is to say if the parent key field contains

Red
Yellow

and the child key field contains Yellow ... you have a valid relationship! You also have a valid relationship to another record that only contains Red in the child field.

To take it even a step further, it’s possible that a value that is separated by a carriage return in one key field matches a value separated by a carriage return in the other key field, the relationship is valid. That is to say if the parent key field contains

Red
Yellow

it will have a valid relationship to the child Key field containing the value of

Yellow
Indigo.

This is because the carriage returned data string of “Yellow” appears in both key fields.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.