Exploring the Calculation Dialog Box

Now that you know something about how and where calculation formulas are used, it's time to turn next to the anatomy of the calculation dialog box itself. There are some small differences among the calculation dialogs you find in particular areas within FileMaker Pro. We'll focus our attention on the dialog used for defining calculation fields because it's the most complex. Figure 8.3 shows the calculation dialog for a field called FullName, which serves as the model for this anatomy lesson.

Figure 8.3. When creating calculation fields, it helps to know your way around the Specify Calculation dialog box.

Specifying Context

Across the very top of the dialog, you're asked to specify the context from which to evaluate this calculation. This choice, new to FileMaker Pro 7, is necessary only in those cases where the base table you are working with appears in your Relationship Graph more than once. And even in those cases, it really matters only when your calculation formula involves related fields. In such cases, the calculation may return different results, depending on from which context it's evaluated.

Writing the Formula

The large box in the middle of the Specify Calculation dialog is where you define the formula itself. If you know the syntax of the functions you need and the names of the fields, you can simply go ahead and type the formula in by hand. In most cases, though, you'll want to use the lists of fields and functions above the text box. Double-clicking on an item in those lists inserts that item into your formula at the current insertion point.

NOTE

On a Macintosh, after an item is highlighted, you can also press the space bar to insert it into your formula. On Windows, the Insert key functions similarly.

Every calculation formula is made up of some combination of fields, constants, operators, and functions. All the following are examples of formulas that you might write:

 2 + 2 FirstName & " " & LastName Get(CurrentDate) + 14 Left(FirstName; 1) & Left(LastName; 1) "Dear " & FirstName & ":" LastName = "Jones" 

In these examples, FirstName and LastName are fields. Get(CurrentDate) and Left are functions. The only operators used here are the addition operator ( + ) and the concatenation operator ( & ). ( Concatenation means combining two text strings to form a new text string.) There are also numbers and text strings used as constants (meaning that they don't change), such as 14 , "Dear" , and "Jones" . Text strings are the only things that need to be placed within quotes. FileMaker assumes that any unquoted text in a formula is a number, a function name, or a field name . If it's none of these, you get an error message when you attempt to exit the dialog.

Selecting Fields

Above the formula box to the left is a list of fields. By default, the fields in the current table are listed. You can see the fields in a related (or unrelated) table by making a selection in the pop-up above the field list. Double-click a field name to insert it into your formula. You can also type field names directly.

CAUTION

Be aware that the only fields you can use from an unrelated table are those with global storage. There's no way FileMaker could determine which record(s) to reference for non-globally stored fields. You get an error message if you attempt to use a non-global field from an unrelated table in a formula.

If you have difficultly typing field names directly into formulas within ScriptMaker, see "Formulas in Scripts Require Explicit Table Context" in the Troubleshooting section at the end of this chapter.

Choosing Operators

In between the field and function areas in the calculation dialog is a list of operators that you can use in your formulas. Operators are symbols that define functions, including math functions such as addition, subtraction, raising to a power, and so on.

NOTE

Strictly speaking, not all the symbols listed here are operators. The paragraph symbol ( ), for instance, is used to represent a literal return character in strings.

There is often some confusion about the use of &, +, and the and operator. The ampersand symbol (&) is used to concatenate strings of text together, as in the previous example where we derive the FullName by stringing together the FirstName, a space, and the LastName. The + symbol is a mathematic operator, used, as you might expect, to add numbers together. The and operator is a logical operator used when you need to test for multiple conditions. For instance, you might use a formula Case (Amount Due > 0 and Days Overdue > 30, "Overdue") . Here, the and indicates that both conditions must be satisfied for the test to return true.

The other operators are quite intuitive, with the exception of XOR. XOR, which stands for "exclusive or," is used to test if either of two statements is true, but not both of them. That is, (A xor B) is the same thing as (A or B) and not (A and B). The need for such logic doesn't come up often, but it's still handy to know.

Selecting Functions

The upper-right portion of the calculation dialog contains a list of the functions you can use in your formulas. By default, they are all listed alphabetically , but you can use the View pop-up menu above the list to view only formulas of a certain type. The Get functions and External functions, in fact, will display only if you change to View By Type.

Double-clicking a function inserts the function into your formula at the current insertion point. Pressing the space bar (Macintosh) or the Insert key (Windows) while the function is highlighted also adds the function to your formula. The "guts" of the function ”the portion in between the parentheses ”is highlighted so that you can begin typing parameters immediately.

You can learn more about how to read and use functions later in this chapter in the section titled "The Parts of a Function," p. 223 .

Writing Legible Formulas

Whether you're typing in a formula by hand, or are using the selection lists to insert fields and functions, we have a few general comments about how to make your functions easy to read.

First of all, when writing functions, spacing doesn't matter at all. You can put spaces and returns just about anyplace you want without changing how the formula evaluates . For legibility, it's therefore often helpful to separate the parameters of a function on separate lines, especially when you have nested functions.

Also, one of the new features of FileMaker Pro 7 is the capability to add comments to calculation formulas. You can use either C or C++ “style comments. Using C-style comments, you can type // and anything that follows on that line is considered a comment. C++ “style comments use /* and */ to set off commented sections. This type of comment can appear in the middle of a line or can span multiple lines.

Compare, for example, the legibility of a complex function written two different ways. In Figure 8.4, you can see a mildly complex function with no commenting or spacing. In Figure 8.5, that exact same formula has been rewritten with comments and extra spacing to make it more legible. Legibility isn't merely an idle concern; it has real value. If you, or someone else, ever need to debug or alter one of your formulas, it will take much less time and effort if you've formatted your formula well in the first place.

Figure 8.4. A complex formula written without adequate spacing can be very difficult to understand and troubleshoot.

Figure 8.5. Adding spaces, returns, and comments to a formula can make it much more legible, and hence, easier to maintain in the future.

Other Options

Before ending this calculation dialog anatomy lesson, we must cover the miscellaneous options you can see at the bottom of the calculation dialog. These options pertain only to defining calculation fields; you don't see them in any of the other calculation dialogs.

Data Type

The first of these miscellaneous options is to specify the type of data that the calculation will return. Usually, it's quite obvious. If you're concatenating the FirstName and LastName fields to form the FullName field, your calculation result will be a text string. If you're adding the SalesTax to an InvoiceSubTotal to generate the InvoiceTotal, the result will be a number. Adding 14 days to the current date to generate a call-back date will result in a date. Simply ask yourself what the formula will produce and select the appropriate result.

If you do choose the wrong data type for a calculation field, you may experience some unexpected results. See "Errors Due to Improper Data Type Selection" in the Troubleshooting section at the end of this chapter.

Number of Repetitions

The only time you'll ever have to worry about the number of repetitions in a calculation field is when your formula references one or more repeating fields. If it does, then you'll typically define your calculation to have the same number of repeats as the fields it references. The formula that you define is applied to each repetition of the source fields, resulting in different values for each repetition of your calculation field.

If you reference non-repeating fields in your calculation, they affect only the first repetition of output. You can, however, use the Extend function to allow a non-repeating field to be applied to each repetition of output.

For instance, in Figure 8.6, Quantity and ItemCost are both number fields defined to allow 10 repetitions. TaxRate is a regular number field. The formula used to determine the LineTotal is as follows:

 Quantity * ItemCost * (Extend(TaxRate) + 1) 

Figure 8.6. Calculations fields can be defined to allow multiple repetitions.

LineTotal itself is defined to allow 10 repetitions.

Do Not Evaluate

By default for new calculation fields, the Do Not Evaluate If All Referenced Fields Are Empty box is checked. This means that the calculation will return a null (empty) value as long as all the fields it refers to are empty. The difference in behavior caused by unchecking this box is subtle, but sometimes important. For instance, say you had a StatusCode field in an invoice database and wanted to use it to generate a status message, the formula of which was If (StatusCode = "P"; "Paid"; "Not Paid") . If you left the Do Not Evaluate box checked, then invoices with no status code would have no status message. If it were unchecked, then their status message would be "Not Paid."

There's no simple rule we can provide as to when you want to check or uncheck this option. You need to look at your formula and determine whether or not the inputs to the formula (those fields referenced in the formula) could all ever be blank, and if so, whether or not you would still want the formula to evaluate. Typically, if your formulas have default results (as in the StatusCode example) rather than use explicit logic for determining results, you probably want to uncheck the box.

Storage Options

The last things we'll touch on in this anatomy lesson are the storage options available to you when defining calculation fields. Be aware that the output of your calculation formula may differ depending on the storage method selected. The Storage Options dialog box is shown in Figure 8.7.

Figure 8.7. The Storage Options dialog enables you to set calculation fields so that they have global results and to specify indexing options.

In the top portion of the dialog, you may specify Global Storage as an option. This is a concept new to FileMaker Pro 7, and one perhaps not immediately intuitive even for long-time FileMaker developers. Global storage for regular fields (that is, text, number, date) is typically used when you need a temporary storage location for a value or for infrequently changing, solution-wide values such as your company's name and address. For instance, globally stored text fields are often used in scripts as variables or as a place to hold users' preferences or selections as they navigate through your interface.

For more information on global storage of field data, see "Storage and Indexing," p. 82 .

If you set a calculation field to be stored globally, then the results of the calculation formula will be available to you from any record, and indeed, any table, in your system. The formula isn't evaluated for each record in the system; it is evaluated only when one of the inputs of the formula changes or when you modify the formula. So picture a scenario in which you have a table called SystemPrefs that contains a single record with system-wide information, such as your company name. This field is a regular text field so that the company name can be stored from session to session and updated without the system having to be taken offline. If you have another field, gCompanyName , that's set to be the value of the company name field, but that has global storage, that field could be placed on any layout or in any script without needing a relationship to the SystemPrefs table. Whenever the company name field is updated, however, the calculation formula re-evaluates. The moment you make the change, all the system's users will see the new value.

The example given here makes the assumption that the SystemPrefs table would only ever have one record. If in fact it was allowed to have more, the value of gCompanyName would be the value of the last modified record.

For another example of when to use global storage for calculation fields, see "Sortable Column Headers," p. 278 .

The bottom half of the Storage Options dialog enables you to specify indexing options. Indexing a field speeds up searches based on that field, but it results in larger files. FileMaker also uses field indexes for joining related tables.

For more detailed information on indexing, see "Storage and Indexing," p. 82 .

In most cases, the default indexing option for a calculation field will be set to None, and the Automatically Create Indexes As Needed box will be checked. For most calculations you write, this configuration is perfect. FileMaker determines whether an index is needed and creates one if it is. Performing a find in the field or using the field in a relationship are both actions that trigger the automatic indexing of a field.

For some calculation formulas, the default storage option is to have the Do Not Store Calculation Results option checked and for everything else to be grayed out. This is an indication that the field is unindexable. Calculation fields that return text, number, date, time, or timestamp results can be indexed so long as they are stored. Calculations can be stored so long as they don't reference any unstored calculations, globally stored fields, related fields, or summary fields.

There are a few circumstances in which you'll want to explicitly turn off storage. For instance, when you use any of the Get functions in a calculation, you should make sure the calculation result is unstored. If you do so, the calculation is forced to evaluate based on the current environment each time it's displayed (as opposed to the environment at the time the record was created or modified). Let's say that you defined a calculation to return the number of records in the current found set by using the Get(FoundCount) formula. If you don't explicitly set the results to be unstored, then for a given record the formula evaluates once and keeps that value, regardless of changes to the size of the found set ”it's stored there. As their name implies, unstored calculations do not make your files larger, but because they must evaluate each time you view them, they can slow down a system if they're based on complex formulas.

As a rule of thumb, you should usually stick with the default storage options unless you know for sure that you need the result to be unstored. You'll almost never need to explicitly turn indexing on; let FileMaker turn it on as necessary. Very seldom, you should uncheck the option to have FileMaker turn on indexing as needed. Be aware that indexing increases the size of your files, sometimes by a great deal. By unchecking the option to have FileMaker turn on indexing as needed, you can ensure that the certain fields won't be indexed accidentally just because a user performs a find on them.