The FileMaker Calculation Dialog Box

The Calculation Dialog Box is the FileMaker developers sandbox for all things calculated. Many times in FileMaker discussions you might hear the term “calculated result” and it’s a powerful term to latch on to. A calculated result is defined via the calculation dialog box. This box can be found in a number of areas. One is a calculation field, ie.. you have a field that contains a calculated result. It can also be found in scripts, so you have a calculation that will control when a script runs, which script steps run and when the script ends. It can also be found in validating the information entered in a field, so you can control what a user types into a field via a calculated result.

How about we take a step back and start again at the most basic level. Let us create a simple calculation field, shall we? Under the File menu, choose Define and them from the sub menu that appears on the right, choose Database. In this dialog box, you will see three tabs for Tables, Fields and Relationships. Click the second button and here we are going to create a new calculation field. In the field name, type in test ok to delete. I give fields names like this just in case I forget to delete the field later on. From the Type menu, choose calculation and finally click the create button.

The top half of the calculation dialog box is your calculation resource toolkit. It is divided into three main sections for picking fields, operators and functions. This is consistent with the point, click and shoot design method FileMaker uses in so many areas.

The first section is your field pick list. By default, it will list all the fields that can be found in the table you are currently working in. Above the field list, you will see a pull down menu that will allow you to see the fields within other tables in a file (some with valid relationships and some without). In fact, you can even bring up the “define new relationship” dialog box via this pull down menu. This is nice because it allows you to define a relationship without closing out of your working calculation!

Continuing on with our conversation about the calculation dialog box ....

The second section is where you can find the right operator for the right task. The eight most popular operators can be found as easy to click buttons. They are the concatenate, text constant (quote marks), carriage return and precedence in the first vertical row.

In the second vertical row, you have the division, multiplication, subtraction and addition buttons.

Concatenate - connects or concatenates two text strings together

Text Constant - each shows the start and end of a text string constant

Carriage Return -inserts a text carriage return or paragraph break between elements

Precedence - performs the calculation left to right inside of the parentheses

Now the second section ( part two ) is a scrollable list of operators which will contain your comparison operators ( greater than, less than, greater than or equal to, less than or equal to, equal, not equal ) and your logical operators ( AND, OR, NOT, XOR). We discuss logical operators in detail in later conversations.

The third area is a scrollable list of the functions that we will be discussing in the great detail in later discussions. By default, you will see all the functions, sans the Get, Design, Custom and External, listed in alphabetical order. Above the list you will find a pull down menu that will allow to to view a particular family of functions exclusively ( just the text functions, just the logical functions, etc...). This pull down menu is the only way to see the Get, Design, Custom or External family of functions.

The large scrollable area in the middle is where you will be placing your calculation.

At the bottom of the calculation box is where you can define the result type of the calculation ( text, number, date or container ). There is also a button to define the storage options for the calculation. I discuss this in detail later in a section called “Discussion About Indexing.”

FYI... Both FileMaker Client and FileMaker Advanced can use custom functions but you can only create/edit custom functions using FileMaker Advanced.
=
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.