The FileMaker Calculation Field And Storage Options

In many ways, the storage of a calculation is the same as the storage of a regular field. In a regular field, the third tab of the field options settings is for the storage of the field in regards of global storage, repeating fields and indexing.

The global field option is used to contain a single literal value for all records in a FileMaker file. Any field that has data stored globally has one value and it can be viewed no matter where you are in a FileMaker file. By anywhere, I do mean that any global field in any table can be view from any record of any other table. Fields with the global option can be edited directly on a layout or via a script. Global fields work about the same as normal entry fields in that you can click inside them, cut, paste and add new data.

FileMaker indexes focus on the data within a field. So if you have a FileMaker field indexed, FileMaker can look at it and go to the associated records quicker than it can if it is not indexed. For this reason, indexing is a key player in searches, sorts, reports and relationships.

A repeating calculation field is any type of field ( except summary ) with a specific storage option setting. The storage option of repeating fields allows you to have multiple cells of information in the same field. If you have two repeating fields in a calculation, each cell repetition will be calculated by the same cell repetition of the other field. In cases where a repeating field is in a calculation with non-repeating fields, you will need to use the Extend function. In fact, with any calculations involving repeating fields, you may need to refer to some of the functions that are particular to repeating fields. I cover this in other discussions but here is a list of those repeating functions and how they are commonly used.

Extend - Extend( non-repeating)
Used to extend a non repeating field to be used in a calculation for each repetition that needs a calculated result.

EXAMPLE: A repeating field has these three values in each repetition ( 2, 3, 6 ). If we extend a field that has the number 2 in it and multiple it by the repeating field, we would get ( 4, 6, 12 ) as our repeating results. Without the extend field, only the first repetition would be multiplied by 2. So you would get ( 4, 3, 6 ).

GetRepetition - GetRepetition( repeatingField ; number)
This function returns a specific value of a particular repetition of a repeating field. For instance it can tell you the second repetition of the field phone type is fax. The function does have the two parameters of repeatingfield and number. The first is the specified repeating field you want to work with. The second is the particular repetition that you want.

Last - Last( repeatingField)
This function returns the last valid, non empty value in a repeating field or a related field. In a relationship, the last can change based upon the setup of the relationship. By default, the last value would be the value that is in the record that was most recently entered. Sorting a relationship may change this because the last value entered may not be the last record in the sort order.

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2007 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.