FileMaker Relationships That Can Range Based Upon User Input

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

So you know that one record can be linked to many records via a relationship and a portal is a great place to view multiple related records. But what if you want to view just a subset of the related records in a portal and not all the records? In a case such as this, you may want to view all the related records between two dates. For the most part, relationships that range will involve numerical data or chronological data. It is possible to view a range of text information but it’s not that common. What does that mean really?

Date Ranging Examples
This is by far the most popular type of ranging relationship and leads towards the great dashboard movement of 2006 and beyond. The concept is that you can set a starting date and an ending date and view all the related records in between.

Number Ranging Examples
On the surface, you would think the ability to view records that range between numbers would be almost as popular as date range relationships. However, that is not the case because most of the numbers people want to see are summary totals. For example, customers that have “x” number of invoices overdue, would be very helpful. The problem is that determining that at the source requires calculations and those values are normally unstored. This can still be done, but it would require some prep work to make sure the child key values can be stored and indexed.

Text Ranging Examples
About all you can do with a text range is see records that range from A – Z or somewhere in between. This isn’t all the useful in most situations but I have seen a few interesting implementations of it.

Ranged relationships are normally accomplished through a Multiple Comparison setup. Here you can see a ranged relationship I use in my internal project management data (a variation of InBizness SOHO). Here you can see that for a campaign (project), I have a relationship setup where I can see all the timecards for a campaign by a start and end date range.


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

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