Quickly Find Unstored Calcs and Summary Fields

By September 18, 2015 September 5th, 2019 No Comments

Certain types of fields, like unstored calculation and summary fields, can really slow down a solution, especially if they are used on layouts that are primarily accessed in list or table view.

It’s not uncommon to come across list layouts in inherited solutions – files which you are maintaining but were originally developed by someone else – which use unstored calcs and summary fields. If the list view is loading slowly, one of the first step in diagnosing the performance issue is to remove these fields to see what impact that has.

But to do this, we need to determine which fields on the slow layout are unstored calcs or summary fields. When a layout which you didn’t create in the first place has dozens and dozens of fields, and the field naming convention doesn’t readily reveal the type of field, then this can be a somewhat tedious task.

Creating a Database Design Report (DDR) will give us this information, but it takes a little bit of time to do, so is there a quicker way?

Yes, there is (with a caveat, of course) – we can use the two FileMaker design functions FieldNames and FieldType to accomplish this.

The caveat is that if the unstored calc or summary field exists on a layout as a merged field, it will not be detected.

The accompanying demo file, which is based on the FileMaker Assets starter solution, has two approaches built-in:

The first approach uses a script that scans the entire file to create a sort of mini-DDR that lists all layouts containing unstored calc and summary fields. The result is reported in a global variable which you can access using the Data Viewer.
Message after running the script

Here is a portion of what is returned when this script is run in the demo file:

Layout Field Type
Asset Details Assets::Status UnstoredCalc
Asset Details Assets::Days Till Due UnstoredCalc
Asset Details Assets::Selected File Container UnstoredCalc
Asset Details Assets::Book Value UnstoredCalc
Asset Details Assets::Remaining Years UnstoredCalc
Asset Details Assets::Depreciation Value UnstoredCalc
Asset Details Assets::Book Value UnstoredCalc
Asset Details Assets::Count Summary Chart Summary
Asset Details | iPad Assets::Status UnstoredCalc
Asset Details | iPad Assets::Days Till Due UnstoredCalc

The second approach uses a custom function, which I named “SlowFields”, to find unstored calc and summary fields on the current layout only. Here is an example of what this custom function returns when evaluated on the Asset Details layout:

Custom function "SlowFields"
The script and the custom function are each standalone and completely portable (and they are independent of each other), making them easy to copy into other solution files.

You can use either approach depending on your preference and the situation you’re working with.

Mislav Kos

Mislav Kos

Mislav is a FileMaker developer and a Technical Solution Architect at Soliant Consulting.

Leave a Reply

Need to adjust your business processes quickly? We're helping clients use technology to keep their teams productive and running smoothly in these times of uncertainty. Our team can guide yours if you need help in these areas.

Talk to a Consultant