Disclaimer: This article contains speculative and experimental techniques that are in the proof-of-concept stage. Use at your own risk and test thoroughly.
Today we’re going to delve further into the concept of virtual calculations, picking up where we left off last time, and with the assumption that readers are familiar with the material in part 1 and part 2 . We’ll look at some ways to make this technique less brittle (prone to breakage if objects are renamed), and also less opaque to DDR analysis tools such as BaseElements and Inspector. We’ll also see if the technique can be applied to auto-enter calc fields, and finally, we’ll explore some ways to make the technique easier to implement.
Demo Files:
Background
In part 2 we implemented a method where “virtual” calculated fields pull their definitions from a global text field, zzVirtualCalcDefinitions, in the data file…
…which has been populated via a script in the interface file. All three of the demos we’re going to look at today rely on this basic structure, though in demos 2 and 3 I decided to lose the color coding in the interest of simplification.
Demo #1
Something that has bothered me about the demos we’ve seen so far in this series, is brittleness. We’ve been using a “VC” table in the interface file to set up the definitions — here’s how it looks in part 2…
…and if any referenced field name or table occurrence name changes in the data file, the corresponding virtual calculation will break. (In fact, it happened to me just last week.)
And bear in mind that this isn’t just about “reserved calcs” that have been pre-defined to be used at some point in the future… currently-in-use unstored calculation fields can also benefit from this technique, and it can be especially useful when you have beta users hammering on separated systems where business and other logic is still in flux, and you are rolling out new interface files on a regular basis.
So, in today’s first demo, I took a stab at (partially) remediating the situation. There wasn’t anything I could do about the “syntax” field since it’s just a static text field, but the “tableName” and “fieldName” columns are now rock solid because I deployed Fabrice Nordmann’s FM_Name_ID custom function (which you can read about here), to reference internal IDs.
If I type the table ID into the first field, or the field ID into the second, it will be translated to the corresponding name. And now if during development I decide to rename zzVC_Donation_Average to zzVC_Donation_Avg…
…no change will be necessary in the VC table and nothing will break.
Only one minor problem… how to determine the ID in the first place?
Well you could throw FM_Name_ID into the Data Viewer, feed it the appropriate arguments, hit Evaluate, then copy the result and paste it into the appropriate field in the VC table, but that would be time consuming and tedious.
So I added these auto-enter calcs to VC::tableID and VC::fieldID…
…and now the user can input either the name or the ID, and if the former it will be translated to the latter.
This demo has another trick up its sleeve. So far in this series, we’ve used the virtual calculation technique strictly as a way to “define” calculation fields. But there’s no reason the technique can’t be used on auto-enter calcs as well. In the top row of the VC table, we have an entry for the auto-enter calc you see below. So when I type my name into that field…
…this is the result:
And if I modfiy the syntax, and then reload…
…the auto-enter calc will now use the new definition.
Really, FileMaking doesn’t get much more fun than this, does it? (Don’t answer that… and in case you were wondering, “Reverse” and “Expand” are custom functions.)
Demo #2
Well, I suppose I can congratulate myself on having tamed some brittleness in demo 1, but there’s plenty more brittleness where that came from… specifically in the syntax field. As you may recall from part 1, I decided to use a “VC” table because I wanted an easy, obvious way to create, view and update my virtual definitions. But as long as the definition is sitting in a text field, it is prone to breakage due to field or table occurrence renaming in the data file… and brittleness isn’t the only problem.
Another problem is that fields and table occurrences that appear in the syntax field are effectively invisible as far as DDR analysis tools such as BaseElements or Inspector are concerned. Demo 2 attempts to address both of these problems, and it does so by eliminating the “VC” table altogether, and doing all the heavy lifting in the “load virtual calc defs” script.
At first glance, the script is deceptively simple looking, containing a single Set Field step per table. Since there are only two tables with virtual calcs, there are only two script steps.
Let’s examine the second Set Field, the one for Donors, wherein four virtual calcs are defined:
“FNO” is a custom function that stands for “field name only” — it uses the GetFieldName function but then strips off the table occurrence so you get just the field name.
What’s going on with the substitutions on the {{A}}, {{B}} and {{C}} place holders? They aren’t strictly necessary, but I’m finding them useful when building complex calcs, as way to identify objects (fields, TOs) that we want to a) protect from renaming, and b) make visible to DDR analysis tools such as BaseElements or Inspector. [Thank you Bruce Robertson and Barry Isakson for convincing me this was worth doing.]
…and here’s the result:
Hmm… the first one’s a bit of a mess, isn’t it? Of course this is merely a cosmetic problem — the virtual calc works fine, and FileMaker doesn’t care how it looks. Still, it would be nice if it were more legible.
Also, I’m not excited about having to manually escape these quotation marks in the Set Field step. Not because it can’t be done, but they add a layer of tedium and obfuscation, and as you may recall, one of my original goals from part 1 in this series was to be able to define and debug virtual calc syntax in the data file via the Data Viewer, like so:
And while I’m complaining, another thing that bothers me about all the demos we’ve seen so far in this series is the replication of the virtual calculation itself in hundreds or thousands of fields, because if I ever decide to redefine it, there will be a lot of places it needs to be changed.
So demo #2 has achieved its objectives, but at costs I’m not thrilled about paying, and in demo #3 I attempt to mitigate some of this.
Demo #3
First off, in this demo I have moved the virtual calc definition parsing logic into a custom function, so now fields can be defined like this…
…and the CF can do the heavy lifting, and if I ever change the rules for parsing defs, I only have to update the CF, rather than hundreds or thousands of individual VC fields.
The other big change is that the script has been expanded, like so:
The “Insert Text” step is a very unusual holdover from a bygone era, but in this case it comes in handy because it’s the only script step (that I’m aware of) that allows you to a) enter literal text, and b) do something with that literal text after you’ve entered it.
The drawback of using Insert Text is that you must provide a field on a layout to insert into, so in this demo we have a global “helper” field on a layout for this purpose.
I think it’s a small enough price to pay, since you set it up once and then never have to think about it again, no matter how complex the script becomes or how many tables you end up processing.
And here’s what happens once the text has been inserted:
In the interest of not further complicating the script (or prolonging this explanation), I’m building up the list of definitions directly in the zzVirtualCalcDefinitions global field by appending each new virtual def to existing field contents. For performance reasons, if I had a lot of virtual calcs, I would probably build up the definition list in a variable, and then push that into the global field.
At any rate, does it work? Yes it does, and the results are nicely formatted too.
Conclusion
I said at the end of part 1 that in an upcoming installment we would look at ideas to a) reduce the field count, b) solve the “define more fields than you’ll ever need” problem, and c) accommodate summary fields. And we will do so in part 4.