Chapter 8. Calculation Primer
The Calculation dialog in FileMaker serves as a fundamental element in nearly all development activities. Beyond simply defining calculation fields, you also work with the dialog within scripts, for setting some auto-enter field options, for field validation, for tooltips, for conditional field formatting, for charting, and even within a file’s security settings. We encourage all developers to become deeply familiar with calculation functions. To that end, this chapter provides a concise reference of how the dialog works.
The Calculation Function Interface
The Specify Calculation dialog gives developers easy access to the data fields in their solutions and to a complete function list (see Figure 8.1).
Figure 8.1. Both field names and calculation functions can be double-clicked to insert them into the expression editing area.
→ If you would like more detail on specific functions, including complete examples of how they work, see Chapter 10, “Calculation Functions.”
Calculations: Things to Remember
When you are working with calculation functions, there are some common issues to keep in mind.
The four special operators in FileMaker are
• & Concatenates the result of two expressions. “Joe” & “Smith” results in “JoeSmith”; 1 & 2 results in “12”.
• “” Designates literal text. You don’t need quotation marks around numbers, field names, or functions.
• ¶ Carriage return.
• () Designates a function’s parameter list and controls the order of operations for math expressions.
Entering a less-than character followed by a greater-than character (<>) equates to the “not equal to” operator (≠) within an expression. The following expressions are functionally identical:
1 <> 2
1 ≠ 2
This is also true for >= and <= for ≥ and ≤, respectively.
You can double-click on a field name or function to add it to the calculation formula. On the Mac OS, you can also use the keyboard shortcut Command-Option-Space. Of course, manually typing the formula or field name works, too, and that’s often much quicker than scrolling through lists.
Spaces, tabs, and carriage returns (¶ or “pilcrows”) are ignored within the calculation syntax, except when within quotation marks (which designate them as literal text). This allows developers to use white space to format calculation expressions for easy reading. So the following two expressions are functionally identical:
If (fieldOne < 10;"less than 10";"not less than 10")
If (
fieldOne < 10 ;
"less than 10" ;
"not less than 10"
)
You may insert comments into calculation expressions in two forms:
// this is a one-line comment, designated by two forward-slash characters
/* this is a multi-line comment designated in a block
by a beginning forward-slash-asterisk and
closed by an ending asterisk-forward-slash.
*/
To enter a tab character into an expression (either as literal text or simply to help with formatting), use Ctrl+Tab on Windows and Option+Tab on Mac OS.
Because a quotation mark character (") designates the beginning or ending of a literal text string, if you need to include a quotation mark within a text string, it must be “escaped” by preceding it with a backslash (). For example:
Length ( "The boy exclaimed, "Gee willikers!"" )
FileMaker allows for a shorthand approach to entering conditional Boolean tests for non-null, non-zero field contents. The following two expressions are functionally identical:
Case ( fieldOne; "true"; "false" )
Case ( IsEmpty (text) or text = 0; "false"; "true" )
We do not recommend this shortcut as a best practice. We tend to believe you should write explicit (and, yes, at times more verbose) code, leaving no room for ambiguity.
FileMaker allows for optional negative or default values in both the Case and If conditional functions. The following are all syntactically valid:
Case ( fieldOne = 1; "true" )
Case (
fieldOne = 1; "one";
fieldOne = 2; "two"
)
Case (
fieldOne = 1; "one";
fieldOne = 2; "two";
"default"
)
We strongly recommend you always provide a default condition at the end of your Case statements, even if that condition should “never” occur. The next time your field shows a value of “never happens,” you’ll be glad you did. At the very least, use a null string (“”) as the default so that you have explicitly defined how the function should resolve if none of the tests are true.
The Case function features a short-circuiting functionality whereby it evaluates conditional tests only until it reaches the first true test. In the following example, the third test will never be evaluated, thus improving system performance:
Case (
1 = 2; "one is false";
1 = 1; "one is true";
2 = 2; "two is true"
)
Similarly, logical functions involving and and or also have short-circuiting behavior such that FileMaker stops evaluating an expression after it can determine whether the condition will be satisfied. For example, consider this statement:
If ( color = "blue" or color = "white", "something", "something else")
If the color is indeed blue, FileMaker does not need to evaluate whether the color is white. In a series of or tests, after one test has been determined to be true, that is enough for FileMaker to evaluate the entire expression as true.
Functions inserted from the function list in the upper right of the Specify Calculation dialog use curly braces ({}) to denote either optional or repeating elements.
Fields with repeating values can either be accessed using the GetRepetition( ) function or via a shorthand of placing an integer value between two brackets ([]). The following are functionally identical:
Quantity[2]
GetRepetition ( Quantity; 2 )
Although the default menu in the function list says All Functions by Name, it does not actually display all FileMaker functions (to the general bemusement of all). The Get and External functions are excluded from those listed. To view these functions, you need to choose to view the desired function group specifically by choosing Get or External from the menu of function groups.
When defining calculation fields, make careful note of the context option at the top of the Specify Calculation dialog. If the calculation’s source table is represented by more than one table occurrence on the Relationships Graph, this menu becomes active. Calculation expressions involving related fields can vary depending on the context from which a calculation is evaluated.
Also when defining calculation fields, note the Calculation Result Is menu at the lower-left portion of the dialog. It is a common source of bugs for developers to forget to choose the correct data type for calculation results. (Returning a result as a number instead of a text type is a common and bewildering bug, at least the first time you see it.)
Turning off the Do Not Evaluate If All Referenced Fields Are Empty option ensures that no matter the condition of referenced fields, at least some value is returned. This capability is useful for cases involving, for example, financial data where it is often desirable to see an explicit zero listed in a field rather than for the field to be empty.
Calculation fields that reference related data, summary fields, other unindexed calculation fields, or globally stored fields cannot be indexed; otherwise, even though, by definition, a calculation field returns different results based on different input values, a calculation field can be indexed.
In a multiuser setting, most find operations on unstored calculations are performed on the host. The exceptions to this include unstored calcs that reference most Get functions, any Design function, or a related field from a table in a different file. In these situations, a find operation is performed on the client.
For several Get functions, values are sent from the client to the host when a client first connects and do not change on the host while the connection is active. In the event the item that the Get function references changes, the host returns the cached result instead of what is currently active. These functions are the following:
• Get(ApplicationLanguage)
• Get(DesktopPath)
• Get(DocumentsPath)
• Get(FileMakerPath)
• Get(PreferencesPath)
• Get(PrinterName)
• Get(SystemDrive)
• Get(SystemIPAddress)
• Get(SystemNICAddress)
• Get(TemporaryPath)
• Get(UserName)