FileMaker And Relationship Comparison Operators

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A key field is something that is used to validate a relationship between two ( or more ) table occurrences. When the data in the two fields match ( or compare in a defined way ), you have a valid relationship between the two. So what is this ... comparing in defined way ... stuff? You might have thought that a valid relationship was only possible when the data in the two key field matched exactly. You may be wondering " is this another one of those new FileMaker 7 features?"

Well ... there is a feature in the FileMaker define relationship dialog box, you can define what is a compatible relationship. For example, say we have a relationship between a clients table and an invoice table, the normal operator would be clients:client_id = invoices:client_id. The valid relationship would be when the data in the two fields equal.

With FileMaker 7 and higher, some of the OTHER comparison operators used in a realtionship can be ...

≠ ( not equal )
> ( greater than )
< ( less than )
≥ ( greater than or equal to )
≤ ( less than or equal to )
x ( means all records in both table occurrence compare, no matter the data in either )

≠ ( not equal )
This could be used to flip the results on an equal to relationship. Before FileMaker 7, if you wanted to see a not equal relationship, you would have to create additional key fields that were the calculated opposite of the equal to relationship ( so that “yes” data strings would read as “no”). No need for those redundant fields these days, now you can use the exact same relationship setup but use the not equal operator to flip the results.

> ( greater than ) < ( less than ) ≥ ( greater than or equal to ) ≤ ( less than or equal to )
These operators allow you to create relationship ranges. Before FileMaker 7, range relationships were done with multiple line key fields, scripting or even a plug-in. None of which were that easy to use or setup. Now you can simply select the correct comparison operator for the range you want.

x ( means all records in both table occurrence compare, no matter the data in either )
This operator is used to see all the records in a table outside of the table the layout is using. You might be wondering “ where would I ever want to do that?”. One classic example might be a hopper table. This table would accept data that needs to be processed and passed on to another table. So you could use this relationship to see if there are any records in that table and then have a portal show them.

I built a database for a call center that held customer exception requests. If a product was out of warranty, the phone rep could enter in a case for a repair exception. This record went into a hopper table for approval. The approval person would immediately see when new exception requests came in via a portal, no matter what record they were working in their approval / declined table at the time. When they approved or declined the exception, the record was moved via a script to a different table.

More info about the author and FileMaker in general, contact me at

© 2007 - Dwayne Wright -

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.