About formulas
Formulas perform specific operations on one or more values in a database file, and return a single result.
Formulas can be used to define:
- a calculation field. See Defining calculation fields.
- a calculated value for an automatic entry into a field. See Defining automatic data entry.
- a calculation that evaluates to true or false for data validation. See Defining field validation.
- a calculated value that can be used to replace the values in a field (by using the Replace Field Contents command in the Records menu or a script step). See Replacing the contents of a field.
- calculations in some FileMaker script steps, such as the script steps
If
,Exit Loop If
,Set Field
,Insert Calculated Result
, andReplace Field Contents
. See Automating tasks with scripts. - a calculated value that affects the appearance of layout objects, such as conditional formatting, placeholder text, tooltips, and visibility (see Hiding or showing layout objects).
Formulas can contain:
- constants - numbers, text, date, or time values that don’t change.
- operators - symbols that indicate how to combine or compare two or more values.
- functions - predefined, named formulas that perform specific calculations and return single, specific values for each repetition.
- field references - fields in the same table or in a related table. A related field referenced in a calculation has the syntax
tablename::related
field. See Working with related tables.
If a formula can’t be evaluated, it returns "?". To get an error code that indicates the cause of the problem, enclose the formula with the EvaluationError function.
Examples
Formulas can be used in many places throughout FileMaker Pro. Here are some examples:
- Returns the first and last name separated by a space:Copy
FirstName & " " & LastName
For example, Michelle Cannon.
- Returns the value in the SubTotal field multiplied by .08:Copy
SubTotal * .08
- Returns the first and last name, each on a separate line:Copy
Field1 & ¶ & Field2
For example,
Fred
Jane
Notes
- If you specify the context for a calculation, the calculation will be evaluated based on that context. Otherwise, it will be evaluated based on the context of the current window. See Specify Calculation dialog box.