Formulas are used in two kinds of FileMaker fields: calculation fields and summary fields. For the most part, formulas used in calculation fields operate on data in the current record. Formulas used in summary fields operate on data from more than one record. Beneath a sometimes-confusing raft of terms and definitions, formulas are simple. Using a set of specific instructions, formulas take data from one or more fields, calculate or compare or summarize it, and then display the results. That's it. The twist comes in that word specific: Formulas must be constructed in a set order, or syntax. Mess up the syntax and the formula won't work properly, if at all. Syntax and the parts of a formulaYou'll build most of your formulas within the Specify Calculation dialog box (Figure 9.27), which will go a long way in helping keep your syntax straight. The dialog box works like a construction kit with tools to let you assemble the necessary field references, constants, operators, and functions. Once the formula is run, it spits out results, whose form you also control. Before you start, however, take a moment to understand some of the key terms used in formulas. Figure 9.27. Within the Specify Calculation dialog box, formulas are built in the center formula box using pieces taken from (upper left to right) the field reference list, the keypad and scrolling list of operators, and the functions list.Field References: A field reference directs a formula to use the value in the field it's named after. The left-hand list within the Specify Calculation dialog box displays all the field references in the selected database. Constants: As the name implies, a constant is a fixed value used in a formula. It remains the same from record to record. A string of text, a number, a date, or a time can all be constants. Each of these types of constants must be typed in a particular format for the formula to recognize which type of constant it represents. For more on the required formats, see Table 9.1, Constants.
Expressions: An expression is simply a value or any computation that produces a value. Expressions can contain field references, constants, and functions, and can be combined to produce other expressions. See Table 9.2, Expression Examples.
Operators: Operators enable a formula to compare the contents of two (or more) fields. Insert operators into your formulas using your keyboard or the keypad and scrolling list within the Specify Calculation dialog box (Figure 9.28). Operators combine expressions and resolve what operation should be performed on the expressions. For example, the addition sign, +, is simply an operator that combines the value appearing before it with the value appearing after it: Subtotal + Tax. Figure 9.28. Build formulas using the keypad and scrolling list, which contain text, math, comparison, and logical operators.
Mathematical and text operators are used withsurprisenumbers and text. Comparison operators compare two expressions and return a result of True or False, in what is known as a Boolean expression. Logical operators compare two or more conditions, such as whether the Cost field is more than $200,000 (the first condition) and the Square footage field is less than 1,000 (the second condition). See Tables 9.39.6.
|