In our last post of this series, we looked at how time and timestamp searches work with the FileMaker search engine. In this post, we will consider searches in calculation and summary fields.
Calculation fields
A calculation field is defined with an expression and a result type. The expression is similar to a formula in a cell in Excel. A FileMaker expression can contain:
- field references (from the current or related tables)
- functions (pre-defined in FileMaker Pro)
- operators (mathematical, text and logical)
- constants (numbers or strings)
The expression is constructed in a calculation dialog with access to fields, operators and functions. FileMaker Pro provides a typeahead feature making it easier and faster to include fields and functions – just start typing and FileMaker will suggest options.
When creating a calculation, it is important to specify the data type of the expected result. this is set in the lower left of the dialog:
The result type will affect search results on the field. Searches will treat the data as the defined type. For example, if you create a calculation field for a full name (first name & ” ” & last name) and the result is number, searches on the full name field will not return expected records.
Also in this area is the setting for Storage Options…. This allows for the calculation result to be stored or unstored:
Annoyance
This is one of the times that FileMaker presents a double-negative option in a dialog. Unchecking the Indexing option means “Do not not store the result” – it will be stored. The option should really read “Store the calculation result” – check for stored, uncheck for unstored.
If a calculation result is stored, the field can be indexed and this can improve search performance on the field. However, that may be at the expense of a larger file for both the data and the field index.
It may not be possible to store the calculation result. This is so if the expression references a related field, a summary field, another unstored calculation field, or a field with global storage.
Searching calculation fields
Calculation fields can be searched just like most other fields. As stated above, searches treat the calculation field as the defined type of data. A search on a calculation field defined as a date result will search as for dates (see the previous article on date searches).
If the calculation result is unstored (either by choice or necessity), searches including that field may be slower because there is no field index available and all results must be calculated first to be searched. However, except where there are very large sets of records or the calculation is complex, the FileMaker engine is quite efficient and fast to return a result.
As with standard container fields, a calculation field with a container result is not searchable.
Summary fields
A summary field provides an aggregate summary of a set of records for a specified field. For example, it may display the average test score for a set of test result records.
Searching summary fields
Since the result in a summary field is for a specific set of records, it doesn’t make sense to search a summary field. There is no access to a summary field in Find mode.
NEXT UP
In the next post in this series, we will look at QuickFind and field indexing.
4 Responses
“Since the result in a summary field is for a specific set of records, it doesn’t make sense to search a summary field. There is no access to a summary field in Find mode.”
This is not true. For example, in my case I have summary set to max of a date field, and am displaying t through a relationship in a report. I’d like to set the report to only show records where its summary meets some criteria (not empty, etc.)
If you are looking at a ‘summary’ for related records, you can use a calculation field to produce the summary e.g. Max (related field) and that can be searched.
“Searching summary fields
Since the result in a summary field is for a specific set of records, it doesn’t make sense to search a summary field. There is no access to a summary field in Find mode.”
Did you forget a paragraph after this? Because it does not explain any workarounds on how to search summary fields.
There are no workarounds for searching summary fields directly – they are not accessible in Find mode at all. What would be an example where you would want to search a summary field? Since a summary result is the result for a set of records, would you want a search to return the sets of records that match the criteria? Also, the summary result often depends on the sort order of the records. So it is confusing to think of an example for this. Maybe you have one?