Chapter 4. Working with Calculations Primer

The calculation dialog in FileMaker serves as a fundamental element in nearly all development activities. Beyond simply defining calculation fields, you will also work with the dialog within scripts, for setting some auto-enter field options, for field validation, and even within a file’s security settings. We encourage all developers to become deeply familiar with calculation functions and to that end have assembled here a concise reference to how the dialog works.

The Calculation Function Interface

The Calculation dialog allows developers easy access to the data fields in their solutions and to a complete function list (see Figure 4.1).

Figure 4.1. Both field names and calculation templates can be double-clicked to insert them into the expression editing area.

If you’d like more detail on calculations including complete examples of how they work, see Chapter 6, “Calculation Functions.”

For more detail and explanation on calculations, including in-depth discussion of beginning and advanced uses, please see Chapter 8, “Getting Started with Calculations,” and Chapter 14, “Advanced Calculation Techniques,” in our companion book, Special Edition Using FileMaker 8.

Calculations: Things to Remember

When working with calculation fields and the various places within FileMaker that make use of calculation functions, there are some common issues to keep in mind:

• The four special operators in FileMaker are:
  & Concatenates the result of two expressions. 1 & 2 will result in “12”.
  “” Designates literal text.
  ¶ 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.
• Spaces, tabs, and carriage returns (¶ or “pilcrows”) are ignored within the calculation syntax, except when within quotation marks (that designate literal text). This allows developers to use these characters 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 Opt-Tab on the Mac.
• 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")

  Please note that the authors do not recommend this shortcut as a best practice. We tend to believe one should write explicit (and, yes, 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; "true" )

Case (
  fieldOne = 1; "one";
  fieldOne = 2; "two"
)
Case (
  fieldOne = 1; "one";
  fieldOne = 2; "two";
  "defaul"
)

  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.
• The Case() function features a “short circuiting” functionality whereby it only evaluates conditional tests 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"
)
• Functions inserted from the function list in the upper right will at times use brackets 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 )

• While 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 the community). The Get, Design, and External functions are excluded from those listed (and are arguably less commonly used or more advanced than the other functions). In order to view these functions, you’ll need to choose to view the desired function group specifically by choosing Get, Design, or External from the menu of function groups.
• Make careful note of the context option at the top of the Calculation dialog. In cases where the calculation’s source table is represented by more than one table occurrence on the Relationships Graph, this menu will become active. Calculation field and expression results can vary depending on the perspective and relationships from which a calculation is evaluated.
• Note also the data type returned menu at the lower portion of the dialog. It is a common source of bugs when developers 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 when you see it the first time.)
• Turning off the Do Not Evaluate If All Referenced Fields Are Empty option will ensure that no matter the condition of referenced fields at least some value will be returned. This is useful for cases involving, for example, financial data where it’s often helpful to see an explicit zero listed in a field.
• Calculation fields that reference related data, summary fields, other un-indexed 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.
• FileMaker Pro client computers typically are where calculations are evaluated; however, certain unstored calculations are evaluated on the host or server computer. In cases where certain information relies on a client computer but is evaluated on the server, the server will essentially cache this information when an account logs in and can, at times, be out of sync with conditions on the client. The following functions may be subject to this:
  Get(ApplicationLanguage)
Get(DesktopPath)
Get(DocumentsPath)
Get(FileMakerPath)
Get(PreferencesPath)
Get(PrinterName)
Get(SystemDrive)
Get(SystemIPAddress)
Get(SystemLanguage)
Get(SystemNICAddress)
Get(UserName)