Editor’s Note: few names in the FileMaker community command as much respect and admiration as that of today’s guest author, Darren Terry. This article is a distilled summary of Darren’s popular “Shaking the Dependency Tree” presentation, as seen at the PauseOnError unconference, the FileMaker Developers’ Conference, and FMPUG user group meetings.
Demo file: Shaking The Dependency Tree.zip
FileMaker Pro has a very powerful calculation engine built into it. The calculation engine is exposed to us in a number of ways: calculation fields, script steps, conditional formatting, filtered portals, web viewers, chart objects, etc. But it can be a challenge to determine precisely when various kinds of calculations will refresh, and what causes them to refresh.
Calculation fields fall into two broad categories: stored and unstored. Stored calculation fields only ever evaluate or re-evaluate under three circumstances, as far as I can tell:
- When they are defined or redefined, all existing records in the table will immediately have the calculated value stored in the field.
- When a new record is created, it will immediately calculate the stored calc field values.
- Whenever local referenced data is changed. This happens whether the referenced field is on the layout or not.
“Local” data means data that is stored in the same record. Global data and related data don’t count. Keep in mind that, except for very specific exceptions, a calc field that references either related data or global data is forced to be unstored.
Unstored calculation fields only ever evaluate when they are displayed on screen and the screen is redrawn (or when the field is explicitly referenced in another way, such as being listed in the Data Viewer). Unlike stored calc fields, defining or redefining the field in Manage Database does not cause the field’s value to be calculated for all existing records — because there is nothing stored in the field at any time. Creating a new record will cause displayed unstored calc fields to immediately evaluate, but only because the window is refreshed. Note that an unstored calc field must be drawn onscreen or otherwise explicitly referenced in order for FileMaker to evaluate it.
If an unstored calc field references a related field, the calc field will normally not re-evaluate as the related data changes. In order for the calc to re-evaluate, the window must be refreshed. This can happen if any fields from the referenced TO are on the layout, regardless of whether they’re displayed or not. The related field(s) can be in an inactive tab panel or slide panel, in an undisclosed popover, hidden by conditional visibility, or placed off the right-hand side of the layout (in “no man’s land”). It does not matter. If a field from the related TO is anywhere on the layout, it is sufficient to cause all unstored calcs that reference any fields in that TO to update as related data changes. It doesn’t matter which field(s) are on the layout either, or whether the field(s) on the layout are the field(s) referenced in the calc formula.
Even more, the fields themselves aren’t necessary to make the unstored calc update. An otherwise empty portal based on the referenced TO is sufficient to cause an unstored calc to re-evaluate, even if it’s in a hidden area of the layout.
Of course, even if there are no related fields or portals on the layout at all, the unstored calc field will re-evaluate if the screen is refreshed for any reason. This includes clicking into the unstored calc field on the layout.
If a stored calculation field references a global variable, and the variable does not exist at the time the calc field is defined, then that calculation field will never evaluate for records that existed at the time the field was defined.
Remember that there is no such thing as an empty variable in FileMaker. If you set $$var to “” (the empty string), FM will explicitly delete the variable, releasing its memory allocation.
For those records, the calc field will always be empty (until the field is redefined). If the global variable does exist when the field is defined, then every record that exists in that table at that time will have that variable’s value in the field, and they won’t update ever again (until the field is redefined). Regardless of when the variable is defined, if it exists when a new record is created, the new record will have the variable’s value in the field — and will never update again until the field is redefined.
The upshot of this is: don’t define a stored calculation field that does nothing else but reference a global variable.
An unstored calc field that references a variable will update whenever the screen is refreshed.
Conditional Formatting follows a simple rule: It just works exactly as you would want it to. Essentially, conditional formatting will re-evaluate immediately whenever anything referenced in the formula changes (or might have changed), or whenever the screen (or the object that has conditional formatting applied to it) has been redrawn. Conditional formatting does not require an explicit window refresh, nor does it require the record to be committed. It reevaluates immediately whenever referenced data changes, whether that data is local, global or related. This has the advantage of causing conditional formatting to always be up to date, but it can be expensive on layouts with lots of conditionally formatted objects.
CALCULATION FIELDS THAT REFERENCE CUSTOM FUNCTIONS
When a stored calculation field references a custom function, and the formula for the custom function is changed, the calc field is not re-evaluated. In other words, changes to a referenced custom function fall outside the dependency tree for calculation fields. Of course, a change to a local referenced field will cause the stored calculation field to update. Unstored calculation fields that reference a custom function will always re-evaluate immediately when they are needed or when the screen redraws.
The Web Viewer will evaluate when the layout is first drawn. It will re-evaluate under certain specific circumstances, many of which are quite intuitive (for instance, whenever referenced data changes, regardless of whether it’s related data or local data). The web viewer will re-evaluate immediately upon referenced data changing, without a screen refresh or the record being committed. In this way, its behavior is very much in line with Conditional Formatting.
However, there are a couple unintuitive behaviors to be aware of. First, if the web viewer is set to be interactive, then it will re-evaluate immediately on mouse in and mouse out (that is, immediately upon you moving the mouse into the web viewer’s bounding rectangle, and again immediately when you move the mouse out). Second, if the web viewer is anchored to resize as the window is resized, then it will re-evaluate whenever the window is resized. This allows us to capture window resize events (and to trigger scripts with a script-execution plugin).
NOTE: This is a change in behavior between FM11 and FM12/13. In FM 11, the web viewer will re-evaluate as the window is resized, regardless of whether it’s anchored to the window’s edges or not.
Filtered Portals behave a lot like conditional formatting in their behavior, with one exception: filtered portals require the record to be committed before they update. Furthermore, if the filter’s formula is based on a global field’s value, then the portal won’t update until the window is refreshed. This isn’t necessary if the portal’s formula is based on a local value in the same record.
CALCULATED CUSTOM MENU ITEMS
If you have a custom menu with a menu item whose title is derived by a calculation, FileMaker will never refresh that menu item’s display even if the underlying formula is re-evaluated. It doesn’t matter if the record is committed or if the window is refreshed. In order to update the menu item after the formula has been re-evaluated, you must reinstall the menu set.
If you use the new feature to hide objects when a formula is true (Conditional Visibility), be aware that it follows the exact same rules as Conditional Formatting. It immediately reevaluates as referenced data changes and as the window is redrawn. No commit or refresh is necessary. Like Conditional Formatting, this is nice, but it can lead to a significant performance impact if there are a lot of conditionally hidden objects on the layout.
OTHER CALCULATION TYPES
There are many other places where calculations can be referenced in FileMaker, that are not covered in the demo file. Here’s a brief rundown of many of them.
Scripts: Calculations referenced in script steps are evaluated at run time on demand. However, it might not be easy to tell that they’ve updated (for instance, if you have an unstored calculation that references a variable set in a script, it won’t update until redrawn).
Tool Tips: Tooltips are displayed after a delay of a few seconds, but the formula is evaluated immediately on mouse over.
Record Level Access: RLA calculations are evaluated as needed, but that can lead to unexpected behavior. If the formula references a field in the table where the RLA is defined, then the formula is evaluated on a record-by-record basis as the record is loaded. But if the formula doesn’t reference a field at all (instead relying on an environmental variable like the IP Address, or a dynamic-sounding value like the layout name), then FileMaker will evaluate the RLA formula once, when the file is opened. It will not check the formula again after evaluating it. This can lead to very unexpected and apparently “buggy” behavior.
Chart Objects: The rules for charts are the same as for filtered portals, and charts will update as any referenced data changes, pending a record commit. One thing that often surprises people is that a chart object’s displayed values can be derived from a found set of data, which is dependent on the sort order. Therefore a chart can update itself as records are manually omitted from the found set, or when the sort order is changed (much like a sub-summary report can).
Darren Terry is a FileMaker 13 Certified Developer, and heads up the FileMaker Pro Development team at Pacific Data Management. He was Technical Liaison for Developer Relations at FileMaker, Inc., where he provided technical support to the FileMaker Solutions Alliance. Darren has been a featured speaker at the annual FileMaker Developer Conference, and has written articles for FileMaker Pro Advisor Magazine and ISO FileMaker Magazine. He’s been developing FileMaker solutions for clients like Apple, Adobe and FileMaker Inc. for over 14 years.
14 thoughts on “Shaking the Dependency Tree”
Good article, Darren.
“If an unstored calc field references a related field, the calc field will normally not re-evaluate as the related data changes. In order for the calc to re-evaluate, the window must be refreshed”
It appears that you can also force an unstored calc to recalculate if you reference it through an external API. For example, performing a query on the unstored calc via AppleScript, will return updated answers as the related data is updated. I suspect the same would be true querying the unstored calc through filemaker’s PHP API, though I have not tested that.
Yes,if the unstored calc is explicitly referenced (either onscreen or by some other reference), it will evaluate. Thanks for keeping me honest, Rob.
The tooltip calculation triggers as soon as the mouse passes over the field, the delay is in showing the tooltip to the user. This behaviour also works even if the returned tooltip is ‘blank’ so never actually shows the tooltip.
Good to know, John. Thanks!
Hi John, thank you for the article. What about a popover and its popover button? If the popover is closed and popover button is hidden using the new hidden field feature, will the objects still recalculate if it is on the layout of the user? When do the objects in a popover recalculate? In webdirect what would be the best strategy to keep resources from the FileMaker calculation engine to work most efficiently? firstname.lastname@example.org
Hi Howard: If an unstored calculation is hidden on the layout (whether in a popover, an undisclosed tab or slide panel, or off the right-hand side of the layout), it won’t evaluate until explicitly referenced, or until it is drawn onscreen. However, if there is an unstored calculation field on the layout that references a related field, and that related field (or any field from that same TO) is in an undisclosed and hidden popover, then the mere fact that the related field is on the layout will cause the unstored calculation to update.
There is nothing special about a popover. It’s just part of the same layout where the button exists. Stored calculation fields inside a popover will re-evaluate as local referenced data changes. Unstored calculations will also, or when the popover contents are exposed.
Your question about FileMaker WebDirect is worthy of its own article. Unfortunately I don’t have the space here to go into it in great detail, but I think I would build the layout the same way for FileMaker Pro use as for FileMaker WebDirect use (as far as calculations go).
OK this is good feedback. you are saying that a popover is subject to the same rules for FP engine calculations.
I am building a new application specially for use with mobile devices and webDirect.
I am using Fiddler, a separate product application) to measure the amount of ‘K’ that passes back and forth from the fp server to the client device. I chose not to change layouts but to keep everything on one layout. So far I have about 100 popovers on the layout, some times hidden. On these popovers there are very few portals because I find portals expensive in FP engine resources.
But I do not know what will happen when many users are logged in at the same time. Have you done any testing in this area. The feedback I have received so far from notable sources is that if an object is hidden then it is not refreshed or updated. Your article sheds new light on this for calculation fields. but what about other objects such as regular indexed text, number fields.
I think (I am not totally sure) that it is better to stay on the same layout and use lots of popovers than to change layouts when dealing with mobile devices using webDirect. I think that the hit on resources is higher to move from one layout to another than using popovers. Do you have insight or opinion on this. Any feedback you have would be much appreciated.
Thank you again for your article and insight so far.
Careful with that, Howard. FileMaker WebDirect isn’t certified to run on any mobile devices, and doesn’t work well on any mobile platform right now. You’re going to see very poor performance regardless of how you design the layouts.
As for other objects in popovers, the FMI engineers have assured me that no data is brought down for hidden objects. So normal data fields should have minimal impact until the popover is opened.
Thanks for the feedback. is there a paper out there on the problems in detail with mobile devices? or is it just a blanket statement about this version of webDirect. I have found the popovers very good for low resource usage. How do they compare with witching layouts? In other words if I use a popover instead of going to another layout will that be more efficient and use less resources?
thank you again for your feedback
It’s a blanket statement from FMI, Howard. Here’s one example (at the bottom of the article):
Thanks Darren. So far it is working for me maybe because of the way I have made the application. No navigating from one layout to another. Popovers instead. As mentioned I used Fiddler software which measures the amount of K passing back and forth to and from the server. So wherever we noticed a big hit in K back and forth from the server, we changed the programming to reduce the K passed back and forth. So far on my iPad and on Galaxy tablet it works and is surprisingly fast. But I have had to make many changes and many hours of work to get it there so far. Of course it also works on the desktop with FileMaker and webDirect.
Hopefully changes can be made to make it usable out of the box with mobile devices too. I feel I do not have much of a choice so I have struggled with it so far. I do not know PHP. My colleague does but the gui is not as pretty. Yes we could go with something like WordPress but I wanted to have complete control to be able to sell it as an application or accessible online with something I know I can fix as a FileMaker developer.
If you have any idea about the future of webDirect, please let me know. Are they coming out with a patch or a new version of webDirect (version 2?). How soon do they say?
Unfortunately this year I could not make it to DevCon so any news you have or inklings that are legal I would appreciate to know. I have been trying to find a webinar or something that would talk about the happenings of the conference but so far I could not find a webinar or anything about the conference.
Any thing you have would be appreciated.
Thanks and much appreciated and best wishes
Thanks for your very good article.
Speaking of calculation field, I’ve created a invoicing database, so far the calculation on the sub total, discount, tax do compute automatically once user enter product in the invoice line item, however, when I split or data separate the database, it does not do the calculation anymore on the sub total, tax, discount & etc field, it does do the calculation when I click outside the portal area. Any suggestion or advice. Thank is advance.
Thank you four your article. Sample file link is broken. Could you check it please?
Hello Osman, it should be fixed now.