Chatting About FileMaker Calculation Fields

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Any advanced FileMaker solution is going to be totally dependent upon calculations or calculation fields. The calculation engine can be found in so many places that the most basic FileMaker solution is going to become more and more dependent upon calculations as it grows and new features are added.

For most purposes, you will use a calculation to evaluate a set of conditions and return a text, number, time or date result. You can even return a container result and do some very amazing things. Calculation fields can get their data from a number of sources including functions, fields in the same record, literal text strings or even fields linked via relationships. In fact, FileMaker calculations can take advantage of variables set by a script, from another calculation or even within the same calculation! Some pretty wild stuff and I’ll explore those areas in other discussions.


It is important that you get as comfortable as possible with all the possible things the world of FileMaker calculations can bring to you.

You cannot type data directly into a calculation field on a layout. The only way you can change a calculated result is to edit one of the fields that the calculation uses or edit the calculation itself. Calculation fields on a layout are also skipped when you tab between fields in data entry cause... you can't edit them anyway.

NOTE: Calculation fields uses certain characters and words to do their magic. If you use one of these characters or words in your field title, FileMaker will bark at you. The barking dialog box will tell you that if you decide to continue to use them, the entire field cannot be used in a calculation. Usually you will see the error of your ways and name the field something else. The characters or words not to use are +, -, *, /, ^, &, =, <, >, (, ), ", ::, AND, OR, NOT, XOR, any digit, a period or the same name as a function. A field named Not Married could not be a calculation field because NOT is a restricted word. You would want to name the field something like NotMarried.

About that calculation engine, I spoke about earlier...

Calculations are not limited to use in only data fields, far from it! You can use calculations in validations, script branching, looping conditions, set field info, custom messages or on whole database level data field updates. What happens is that as you a designing your database, you may see areas where you can put some data in or click an optional button that allows you to use a calculation instead.

One of my favorites can be found within the script step of Show Custom Dialog. Here you can specify when a script a custom dialog box can come up. This way you can give the users a choice of options to perform within your script or even capture user input data into up to 3 fields. More about scripts and calculations in later discussions.

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