Welcome back for the second installment in our exploration of Virtual List Reporting (a.k.a. VLR). Demo file: VLR-part-2.zip
To avoid repetition, this article will assume the reader is familiar with concepts and techniques introduced in part 1 (some of what follows may appear to be gibberish if the reader is not)… but to briefly recap, here are some benefits of VLR:
This is a quick follow up to my previous article on using a Summary List field as a relational predicate, and transferring a found set from table A to the corresponding found set in table B across that relationship via the Go To Related Record (current record) script step.
To avoid needless repetition, I will assume the reader has read that article. As discussed in the comments section (thank you Mardi Kennedy and Jerry Salem), there is a tried and true method for going to a found set of related records that has worked since FileMaker Pro 3 was released in December 1995:
Continue reading “Global Multiline Key As Relational Predicate”
Several recent postings on this blog have offered variations on a theme: using the new-in-13 summary list field type as the basis for a multiline relational key. The net effect is to produce a relationship that is found-set-aware. (If you aren’t familiar with summary lists, you can read about them here.)
Last week we looked at basing a value list on one of these “summary list relationships”, to facilitate counting unique values within the current found set…
…and today we have a demo file, FM-13-Count-Unique-v1, that extends the concept to sub-summary reporting.
When you consider how easily most common reporting tasks are accomplished in FileMaker Pro, the lack of a built in, clear cut method to count unique values within a given found set seems a bit surprising. But where there’s a will, there’s a way, and in part 1 we looked at a method that required the found set be sorted. Well I have some good news: today’s demo (Count Unique – Three Variations, 6.4 Mb) has no sorting requirement.
Here we have a flat file (single table) of contact information. For any given found set, the primary key (ID) will of course be unique, but each of the other fields will potentially contain duplicates, and we want to be able to quickly count the unique values without having to first sort the found set.
The basic approach can be summarized in a few words: Continue reading “Unique Records Revisited, part 2”
[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 “Filtered Relations, part 1”
