Searching FileMaker – calculation and summary fields

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.

calculation typeahead

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:

calculation result

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:

calculation storage

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.

summary field average

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.

Leave a Reply

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