From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer
In FileMaker 6, you could only setup a relationship that said "when this field equals equals this other field, you have yourself a valid relationship." If you wanted to throw a curve ball into the mix, you would need to be a little creative with the parent match field, the child match field or a combination thereof.
Those days are long gone now and one of the little things I like most about modern FileMaker design is the pop up list of relationship comparison operators.
Here you can see that the FileMaker child match data has two layers. The appointment date needs to be greater than then start date and less than the end date. You can see how multiple layers are very handy for relationships that range.
FileMaker allows you to create more than one comparison layer, this is somewhat akin to the multiple available layers in the sort dialog box. The setup for a multiple layer comparison relationship starts off just like a regular FileMaker relationship. You navigate to the relationship tab in the define database dialog box. You draw a relationship between two table occurrences and then open up the relationship setup box.
Here are some of the things that are different about the Edit Relationship dialog box that are used in the multiple layer comparison relationship. First off there is a button below the right side table box with the label of "Add" and sits along side a button labeled "Change." These new buttons are linked to a box below the buttons that lists the levels of valid relationship comparisons in order of first listed is compared first and then on down the line. Normally, you will only see the one comparison line but that may change as you grow to appreciate what a multiple layer comparison can do for you.
Let's illustrate the multiple layer comparison in a likely "real world" situation. Let us say that you have a client information table and a table that holds meetings with clients. The two tables are linked together via a client id field in each table. This all works grand and you have bettered the business lives of your coworkers and clients using it. Now they would like to have portal that shows meetings between a set of dates. This portal is to appear on a layout that is linked to the client table.
A multiple layer comparison (actually called multiple predicate relationship) can be very handy when building date range related comparison using the new greater than and less than operators. Say you have two global date fields, one called start date and one called end date. One layer of the relationship could be greater than start date and that would normally return a set of related records. Add to this a second layer of less than end date and you have a range of related records!
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 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.