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.
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:
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.
