Understanding Formulas

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 formula

You'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.

Table 9.1. Constants

FOR THIS TYPE DATA	REMEMBER TO	EXAMPLES
Text	Enclose text in quotes (")	"Welcome to FileMaker" "94530-3014"
Number	Do not include currency symbols or thousand separators (, or ;)	80.23 450000
Date	Use the value as parameter of the Date function or the TextToDate function.	Date(3,13,1998) TextToDate("03/13/1998")
Time	Use the value as parameter of the Time function or the TextToTime function.	Time(10,45,23) TextToTime ("10:45:23")

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.

Table 9.2. Expression Examples

TYPE OF EXPRESSION	EXAMPLE
Text constant	"FileMaker"
Number constant	80.23
Field reference	Cost per square foot
Function	TextToDate
Combination of expressions	(Price/House Size)*0.10

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.

Table 9.3. Mathematical Operators (see Figure 9.28)

SYMBOL	NAME	DEFINITION	EXAMPLES
+	Addition	Adds two values	2+2, Subtotal+Sales Tax
	Subtraction	Subtracts second value from first	21, TotalDiscount
*	Multiplication	Multiplies value	Subtotal*Sales Tax
/	Division	Divides first value by second	Total/Units
^	Exponentiation	Raises first value to power of second	(A2 + B2) returns A^2B^2
( )	Precedence	Expressions inside parentheses evaluated first	(5*20)/5

Table 9.4. Text Operators (see Figure 9.28)

SYMBOL	NAME	DEFINITION	EXAMPLES
&	Concatenation	Appends the text string on right to end of text string on left	"AAA" & "BBB" returns "AAABBB"
" "	Text constant	Marks beginning and end of text constant. Quotes with no text between them indicate a blank space. Text in formula without quotes is interpreted as a field name or function name. To mark a quote mark within a text constant, precede it with another quote mark.	"Welcome to FileMaker" returns as Welcome to FileMaker " " returns an empty (null) value t"Welcome to our "favorite" place" returns as Welcome to our "favorite" place
¶	Return marker	Inserts a paragraph return in a text constant	"Welcome to¶FileMaker" returns Welcome to FileMaker

Table 9.5. Comparison Operators (see Figure 9.28)

SYMBOL	NAME	DEFINITION	EXAMPLES
=	Equal to	True when both items are equal	4=5 returns False 4=4 returns True
or <>	Not equal to	True when the items are not equal	45 returns True 44 returns False
>	Greater than	True when value on left exceeds value on right	4>5 returns False 5>
<	Less than	True when value on left is less than value on right	4<5 returns True 5<4 returns False
or	Greater than or	True when value on left is greater than or	45 returns False
>=	equal to	equal to value on right	55 returns True
or	Less than or	True when value on left is less than or	54 returns False
<=	equal to	equal to value on right	44 returns True

Table 9.6. Logical Operators (see Figure 9.28)

SYMBOL	DEFINITION	EXAMPLES
AND	True only when both values are true: True when true AND true False when true AND false False when false AND false	Cost per square foot < 200 AND Bedrooms2
OR	True when either value is true: True when true OR true True when true OR false False when false OR false	Cost per square foot < 200 OR Bedrooms2
OR	True when either, but not both, of values is true: False when true AND true True when false AND true False when false AND false	Cost per square foot < 200 XOR Bedrooms2
NOT	Changes value within parentheses from false to true, or true to false: False when NOT (true) True when NOT(false)	NOT Cost per square foot > 200