User manual

Chapter 11
Using formulas and functions
This chapter:
1 introduces formulas and functions
1 lists the places you can use formulas and functions
1 shows the components of formulas
1 describes operators you can use in formulas
1 gives a brief description of all functions
See Help for more detail and examples for each function.
About formulas
A formula is a specific operation on one or more values in the
database. For example, suppose your file contains two number fields,
Unit Price and Quantity, and one calculation field, Extended Price.
You want to calculate a value for Extended Price in each record, so
you define a calculation field with the formula:
Unit Price * Quantity
for the Extended Price field. Then, each time you type values in Unit
Price and Quantity, the formula multiplies those values and displays
the result in Extended Price.
You create formulas in the Specify Calculation dialog box
(see“Defining calculation fields” on page 5-5.) You can use
calculations in several areas of FileMaker Pro.
Formulas can include any combination of constants, field references,
operators, and functions. (See “About functions” on page 11-5.)
These elements are arranged in a particular order, or syntax, to
produce expressions giving the result you want. The following
sections explain each formula part and its syntax.
Important The data type (Text, Number, Date, Time, or Container)
of fields and expressions referenced in a formula, and the result
returned, are important. For example, if a date calculation is set to
return a number result, the calculation does not return a date.
When you mix data types in calculations, use the data type
conversion functions (TextToDate, TextToTime, TextToNum,
DateToText, TimeToText, NumToText) to convert data and
expressions to the correct type. These functions are documented in
the Text, Number, Date, and Time function tables below.
To use formulas See also
When defining
calculation fields
“Defining calculation fields” on page 5-5
In certain script
steps
Help topics for the If, Exit Loop If, Set Field,
Insert Calculated Result, and Replace script
steps. Choose Help menu > Contents and Index, click the
Index tab, and type the name of the script step.
To auto-enter
calculated values
“Defining automatic data entry” on page 5-8
For data validation “Defining field validation” on page 5-9
When using
Replace with
calculated results
“Replacing data in fields” on page 2-10