[I want to start out by expressing major thanks to Jason DeLooze, who graciously read an early draft of this article, and with some outside-the-box brainstorming (a specialty of his), transformed my sluggish approach into a blazing speed demon.]
Many of the relationships we define in FileMaker are predictable and mundane. A field in table A is linked to a field in table B via an equijoin (=) operator. We do it every day and we’ve done it countless times. Of course we often define more complex relationships as well: ones with multiple predicates and/or more esoteric operators than the venerable equijoin.
Even in this (FM 11) era of filtered portals, we solve many design and business logic problems relationally. However, once in a while a challenge crops up that seems like it should be solvable using the FileMaker relational model, but that frustrates our initial attempts to do so. For example, let’s look at three tables from a sales database:
On our Products list, we’d like to be able to set a date range in the header, and then see cumulative sales for each product during that period. Continue reading