FM 14: Separation Aggregation Aggravation revisited

Four years ago I posted the original version of this article, exploring a technique that worked fine in FM 11, but that broke and required extensive work arounds in FM 12 and 13. What you’re about to read is a revision of the original article, showcasing a FM 14 method made possible by a new feature: placeholder text.

Introduction

FileMaker developers take it for granted that calculated sub-total and total fields will update automatically when portal rows are created, edited or deleted. This becomes problematic when one a) uses the separation model, and b) either intentionally or unintentionally holds the parent and related child records open (uncommitted) while editing, but still expects to see these aggregates update in real time.

Today’s demo file is FM 14 Aggregates and Separation, and for demonstration purposes it has interface elements in both the Data file and the Interface file. Normally of course, in a separated solution, you wouldn’t have interface elements in the Data file (since that would defeat the purpose of separation). Here we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three calc fields below the portal are tracking the changes in real time.

Non-Separated Behavior

It’s worth pointing out that the aggregate fields update as soon as the user tabs or clicks out of a portal field where changes have been made, i.e., on field exit rather than on record commit. As I mentioned, the parent record is newly created, as are the child records in the portal. If we were to throw Get(RecordOpenState) into the Data Viewer, we would see a 1, which is what you see when the current record is newly created and has never been committed. Once a record has been committed, it is no longer new, and if you reopen it to edit it, then Get(RecordOpenState) will return 2. And of course if you are not editing the current record, then Get(RecordOpenState) returns a zero.

The operative word in the preceding sentence is “you”; Get(RecordOpenState) doesn’t tell you whether a record is open by another user — only whether you have it open. To test whether a record is open (a.k.a. locked or in-use) by another user, 1) set error capture on, 2) perform an Open Record step, and 3) see if Get(LastError) returns 301 (“record in use by another user”).

But getting back to the above screen shot, what you see is pretty incredible if you stop to think about it, because new, uncommitted records don’t really exist, in the normal sense of the word… yet the aggregate calcs are updating as if those records really do exist, which is of course a highly desirable behavior as far as the user experience is concerned.

Incidentally, no other user will see uncommitted changes that you make, until you choose to commit them. When Get(OpenRecordState) is >0, you can cause all changes made (since you opened the parent record) to evaporate by performing a “Revert Record” script step — or, if your layout is set to not save changes automatically, by clicking “Don’t Save” when the following dialog appears.

Separated Behavior

So what happens when we create a new parent record and begin adding portal rows in the Interface file of a separated solution (i.e., one where the layouts are in a different file than the underlying data tables)?

To see the problem: in the Interface file, make sure you are on the Generic tab, and a) create a new portal row and enter some data in it, or b) delete an existing portal row… in either case, the aggregates will not update. However, if you edit an existing portal row, aggregates will update just fine.

In the Interface file, aggregate calcs don’t update until the parent record is committed, and while inconvenient, there is a certain logic to this behavior, because in a separated solution each file has its own Relationships Graph: in this demo, the portal is based on a relationship in the Interface file, but the aggregate calcs are based on a relationship in the Data file. In a manner of speaking, until the parent record is committed, the left hand doesn’t know what the right hand is doing.

Conversely, in a non-separated solution, aggregate calcs in the uncommitted parent respond immediately (on field exit) to any changes made within a portal, because the same relationship is used for both the portal and the aggregate calcs.

So, is there anything we can do to remedy this sorry state of separated affairs? Certainly script triggers represent one possible solution, but for the purpose of this article, I am intentionally ruling out script triggers in favor of something less convoluted and more dynamic.

Pseudo-Calcs to the Rescue

The problem is, calculated fields must live where the tables live, i.e., in the Data file. Wouldn’t it be nice if there were a way to define calculations in the UI file? Well, if we’re talking about actual calculated fields, we’re out of luck, but now, in FileMaker 14, we can define layout-level “pseudo-calcs” like so:

Define a single “null” field (you have wide latitude re: exactly how and where you define it — just make sure it is, and will remain, empty)…

…place it as many times as you wish on the layout in the interface file…

…and for each null field, define the pseudo-calc via placeholder text (on the Data tab of the Inspector) like so:

Note: since all three pseudo-calcs rely (either directly or indirectly) on summing related data, the placeholder text must reference related summary fields in the child table (in this case PIT) for the technique to work. They will not update reliably and consistently if you try to reference an aggregate calculation in the parent table.

Here’s how it looks in browse mode. The pseudo-calcs update in real time and your users will most likely never suspect that anything out of the ordinary is occurring.

A Few More Details

Since one of our goals is for pseudo-calcs to be visually indistinguisable from regular fields, make sure to format the placeholder text color accordingly.

Also, note that placeholder text does not know or care about your underlying field type, and will disregard data formatting applied via the Inspector. So, if you want, for example, your pseudo-calcs to display their results in currency format, you will need to take care of that within the placeholder text calculation — hence my use of the AddDollarFormat custom function (thank you Jeremiah Small) on the extended price subtotal as well as the grand total pseudo-calculations.

One Other Thought

This article has focused on creating the illusion of a calculated field in the Interface file; that’s fine as far as visuals go, but if you need (for example) the script engine to have access to the results of your pseudo-calcs, you can use Let in your placeholder text calculation to invoke/update $$variables in real time like so:

Let ( $$summaryQty = pit::summaryQty ;
$$summaryQty
) // end let

(Demo file: FM-14-Agg-Sep-Global-Vars.zip, and thank you Danny Mack for helping me gain some clarity around this.)

Wrapping It Up

Obviously pseudo-calcs have utility far beyond the narrow use-case I’ve outlined here. Any time you want to add a calculation to a layout, without having to actually define a field, they can get the job done with a minimum of fuss.