Welcome to the fourth and final installment in our series on Radical Separation. Today’s article assumes familiarity with part 1, part 2 & part 3, and continues in the direction we were headed at the end of part 3. Specifically, we will finish up our exploration of “virtual calculations” by examing an intriguing approach suggested by Barry Isakson to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields, and I invite you to follow along in today’s demo file, Virtual Calculations, Part 4, if you are so inclined.
Without further ado, the solution to reducing the field count and having more virtual fields than you’ll ever need is to use repeating calculated fields, e.g.,
With 32,000 repetitions per field type, it’s a pretty safe bet you will never run out. We can use the VirtualDefinition custom function from part 3, with one minor tweak:
As we saw in part 3, the virtual calc fields will pull their definitions from a global zzVirtualCalcDefinitions field in the same table… but now the rep number will be included:
And summary fields are a piece of cake. Here are the ones I’ve set up in the demo file…
…with each summary field defined to summarize repetitions individually, like so:
Here are some of the summary fields placed on the “Donor Summary” layout for demonstration purposes. (In reality, unless the total record set were guaranteed to remain small, for performance reasons, one would not typically place a bunch of summary fields on a browseable list layout.)
So much for summary fields. Returning to our standard repeating VCs…
The benefits have been mentioned, but they have at least two drawbacks:
- individual reps cannot be searched
- no rep greater than 1 can be used as a relational predicate
…and #1 is why I grayed the repeating fields out on the main layout, and made them non-searchable.
If we need to work around either of the above-cited drawbacks, we can do so by using a non-repeating reserved calculation field, as per earlier articles in this series.
But hang on a sec: a major selling point of today’s article was to reduce field count, but it seems like we’re going to have to add a bunch of reserved (non-repeating) calcs… doesn’t that bring us full circle? Not quite… we do need to have some, but it turns out that with a simple trick we can re-use those fields on a per-layout basis, so we only need a relatively small number of them. And what better way to identify a layout than its internal ID?
FileMaker doesn’t have a Get ( LayoutID ) function, but you can determine a layout’s internal id like so:
GetValue ( LayoutIDs(Get(FileName)) ; Get (LayoutNumber) )
…or via a custom function like Fabrice Nordmann’s FM_Name_ID, or the cfLayoutID custom function included in today’s demo.
So internal layout ids can be easily identified; the real challenge is how to target them in such a way that the virtual calc field will be searchable. After experimenting with various approaches, I ended up scripting all navigation like so…
…thus ensuring that the current layout id was always sitting in dev::zz_g_layout_id, and not only easily accessible to the virtual calculation, but more importantly, in a manner that allows the field to be searched. Yes, it will be a search on an unstored calc, i.e., potentially slow if record counts are large, but at least it will (sooner or later) return the correct results.
Here’s a bit of pseudocode suggesting how a searchable virtual calc might be flexibly defined…
To be clear: it may seem like overkill to store the layout id in a global field, and then refer to that global field from inside the virtual calc. There are a number of simpler approaches that correctly display results on screen, but they fail utterly when one attempts to search on fields thus defined. I’ll end this article with a familiar request: if you know of a better method, I hope you’ll post a comment.