7 June 2015: This technique has been substantially revised to work with FileMaker 14; see FM 14: Separation Aggregation Aggravation revisited.
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 called 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). At the left, we are looking at a newly created parent record in the Data file, with a couple newly created portal rows, and the three aggregate calc fields below the portal are tracking the changes in real time.
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.
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, 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 there are two Relationships Graphs (one per file): 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, 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 approach, but they can get convoluted in a hurry. For the purpose of this article, I am intentionally ruling out scripted solutions, in favor of something more dynamic.
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 calculated fields, we’re out of luck. But who says fields are the only way to define or display a calculation? It turns out that web viewers can act as pseudo-fields.
With the release of FileMaker Pro 9, the web viewer gained the capability to display locally generated data, via the “data:text/html” specifier. The ability to have the equivalent of a calculated field in the UI file of a separated solution takes some getting used to, but the results speak for themselves.
Dr. Ray Cologon of Nightwing Enterprises has generously provided a demo called Layout Calculations, which makes it all very easy, thanks to a brilliant custom function which I have used with minor adjustments. You can see Dr. Ray’s demo here:
To give you an idea of how much effort this custom function can save you, here is the syntax for two pseudo-fields. Which would you rather type?
The custom function is called LayoutCalculation in Dr. Ray’s demo. I renamed it to pfCalculation, because I have written a companion CF, pfContent, and I wanted to “namespace” them together in my custom function library (the “pf” stands for pseudo-field).
Since pseudo-fields aren’t really fields at all, accessing their results programmatically takes a bit more work than doing the same for regular fields.
- Since they’re objects, not fields, we need to use GetLayoutObjectAttribute
- …which requires that the web viewer (or any object) be named
- …and which returns too much information, so the results must be parsed
Assuming that the “Total” pseudo-field has an object name of “wvTotal”, here’s the raw result. Not particularly useful, is it?
However, the pfContent custom function returns just the visible contents of the pseudo-field. Note that pfContent returns a text string, which could be useful or not, depending on what you intend to do with it. If you intend to push this value into a number field, then wrap the expression in GetAsNumber to play it safe.
Obviously pseudo-fields 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.
7 thoughts on “Separation Aggregation Aggravation”
Hi Kevin, great article, very well written and easy to understand, learnt something new, cheers !
Wow, being able to do calcs in the webviewer like this is very, very handy. Thank you!
I also only recently stumbled upon that approach to data separation, ie no fields in the interface file and all TOs based upon the external file ref(s). I don’t think that it occurred to me however to even dispense with the table in the interface file.
Re the calc in the webviewer, personally I prefer to use straight code rather than the pfCalculation CF, eg
p style=font-family:arial;color:red;font-size:12px;text-align:right;border:0;margin-top:-15;padding:0>” & AddDollarFormat(Sum(po_PIT::extPrice)) & “, inside the appropriate tags.
That of course is enclosed in the rest, ie data:taxt/html, body, p, /p, /body.
(Hopefully this bit will display nicely; I’ve already discovered that if I attempt to paste in the whole, beautifully spaced web viewer calc, it’ll get thoroughly mucked up in the post, sigh.)
Perhaps in a more complex, CSS context, this might however come unravelled?
The currency formatting CF remains indispensable.
Thank you for this fresh insight,
Great article (and blog),
Some years back I received a file from you with a slightly different technique, I think you referred to it as ‘tickling’ the data to cause a refresh.
I never used the original technique since I decided to remain single file, however I am doing a rebuild in 12 and data separation is the goal.
I only recently noticed this webviewer technique but noticed that it does not appear to work in 12, the value does not refresh itself. Works well in 11.
I know there have been a few changes in 12 as to how things refresh (Conditional Formatting) and was wondering if you have any fantastic solutions for 12 perhaps using FSQL.
My thought was to use conditional formatting to set a global var i.e
let ( $$gTotal = executeSql( “SELECT sum(extPrice) from po_Pit WHERE idPO = ?” : “”; “”; PO::id )
However, due to the behaviour changes in 12, the var is evaluated (or rendered) before the Conditional Formatting sets the desired content. Ive tried numerous things to refresh it.
Interestingly in my own solution, If I have two occurrences of this object, one in front of the other, the upper most total stays current and valid, the lower uses the last value set (always 1 behind). It refreshes with no script triggers or commit record steps.
If I try any other Conditional Formatting calc in the second occurrence, it fails to refresh.
Whilst it seemed like a solution, I thought I would implement it into your demo file rather than my own solution, only to find it doesn’t work. Not to mention, its running the same query twice (or more when resizing the doc window)
I think the additional CF calcs on my layouts are causing a ‘delay’ giving it just enough time to evaluate before rendering the second occurrence, not sure on this to be honest.
Maybe the older ‘tickle’ method is still the best, although I have to find it again :) unless of course I’m missing something with the WV method?
Triggering scripted refreshes, or the total itself still feels the wrong way to go and would require a lot of management to keep it up to date.
Thanks for your kind words re: FM Hacks… and for bringing this to my attention. When I wrote this article 18 months ago, I submitted a bug report re: the original behavior not working, hence the need for a work around. Now I have submitted a second bug report, re: the work around no longer working. Sigh.
If I learn of a fix, I will certainly post it here, and let you know.
Or perhaps someone reading this will share one.
I’ll keep my eyes on here but perhaps this is something to try, this time I can replicate it in your sample file and it kind of makes sense that its working, but wondered if you can also reproduce it?
Add a text area to the layout (content can be just a space) with the following Conditional Formatting calc:
$$sTotal = ExecuteSQL( “SELECT sum(extPrice) from po_Pit WHERE idPO = ?” ; “”; “”; PO::id)
; “” )
Next, add a merge variable <> to the layout, stacked higher than the query object. Then add conditional formatting to this object “Sum(po_PIT::extPrice)”
Actually, just po_PIT::extPrice seems to work too without using sum() but sum seems more relevant ??
The CF in the display seems to cause it to refresh as desired, of course this is just working on the subtotal at the moment, but it seems to be a solution??
Here is a copy of your sample file with the above implemented on both sub-total and total’s to save messing around implementing it.
Both queries are set in the first object’s CF, then refreshes triggered on each individual merge variable using the same technique.
Its seems to be a workable solution, I have just tested with your file and my own on both windows and mac with success.. but that’s still a limited test.
I just took a quick look and nice work Steve. Thanks for following up.