Quickly Find Unstored Calcs and Summary Fields

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:

LayoutFieldType
Asset DetailsAssets::StatusUnstoredCalc
Asset DetailsAssets::Days Till DueUnstoredCalc
Asset DetailsAssets::Selected File ContainerUnstoredCalc
Asset DetailsAssets::Book ValueUnstoredCalc
Asset DetailsAssets::Remaining YearsUnstoredCalc
Asset DetailsAssets::Depreciation ValueUnstoredCalc
Asset DetailsAssets::Book ValueUnstoredCalc
Asset DetailsAssets::Count Summary ChartSummary
Asset Details | iPadAssets::StatusUnstoredCalc
Asset Details | iPadAssets::Days Till DueUnstoredCalc
  

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

Are You Using FileMaker to Its Full Potential?

Claris FileMaker 2023 logo
Scroll to Top