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
INTRODUCTION
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
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.
VARIABLES
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
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.
WEB VIEWER
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
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.
CONDITIONAL VISIBILITY
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.