A READER ASKS: Only One Join Record FileMaker Technique

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).

Hi Dwayne - I found your filemaker blog online and found it very
useful. I was wondering if you could help me with a quick question I
had, if you wouldn't mind:

I am creating a database of contacts and organizations with a many to
many relationship via a join table. any given organization will
contain some contacts, and any given contact could belong to multiple
organizations. I have the join table set up via ID fields. Using a
portal on my contacts layout, I can add, say, multiple organizations
to a single contact, and it works fine EXCEPT:

Every time i add an organization to a contact, a new and unique record
is created in my join table. what i want to be able to do, in the
simplest manner, is to have a join table with one record that will
collect the IDs of the organization as they are added to a contact.

i hope that makes sense - again i appreciate it if you could even just
point me in the right direction...

What you are looking for is a multiple line or compound match field. 

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


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


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


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

So when you add a new organization, you will want the script to add a carriage return and then the organizations key id data to an existing join record (if one exists). If there isn’t a join record, then create the first one and add that data.

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

© 2008 - 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.