Defining calculation fields

Contents > Creating a database > Defining database fields > Defining calculation fields

Defining calculation fields

Use calculation fields to perform calculations on the following types of data: text, number, date, time, or container.

The data in a calculation field is the result of a formula that you specify. The result can be one of these types of data: text, number, date, time, or container.

To define calculation fields:

1.	With the database open, choose File menu > Manage > Database.

2.	In the Manage Database dialog box, click the Fields tab.

3.	If your database contains more than one table, select the appropriate table from the Table list.

4.	For Field Name, type a name for the field.

See About naming fields.

5.	For Type, select Calculation.

6.	Click Create.

You see the Specify Calculation dialog box.

7.	If necessary, for Evaluate this calculation from the context of, choose a table from the list.

Setting the context for a calculation is only necessary when you're creating a calculation field in a source table that has two or more occurrences in the relationships graph. The choice you make may affect the calculation results, particularly if your calculation will include fields in related tables.

For more information on the use of context in calculations, see Choosing the evaluation context for a calculation field.

8.	Build a formula for your calculation.

Click where you want the item to appear in the formula box, then do the following. (You can also type the formula into the box.)

To add a

Do this

Reference to a field

In the field list, double-click a field name.

To display field names from another table, choose a table from the table list.

Mathematical or text operator

For Operators, click a button or type an operator.

Comparison or logical operator

For Operators, double-click an operator in the list or type an operator.

Constant value

Type the value.

Function

In the functions list, double-click a function. In the formula box, replace the placeholder parameter with a value or expression.

To quickly build a formula:

Tab to or click in the field, operators, or functions list.

Type the first few letters of a field name, operator, or function to move to that item in the list.

Press Insert (Windows) or the Space bar (Mac OS) to place it in the formula box.

9.	Select calculation options for the field.

Do this

Set the field type of the result

Choose a data type for Calculation result is <value>.

Choose the correct type for the result you want. See About choosing a field type.

Make a calculated field repeating

Select Number of repetitions, then type the number of repetitions. See Defining repeating fields.

Prevent calculation if all referenced fields are empty

Select Do not evaluate if all referenced fields are empty.

When enabled, FileMaker Pro does not evaluate a calculation if all fields used by the calculation are empty. This typically improves performance.

10.

To select indexing and storage options for the field, click Storage Options, select options in the Storage Options dialog box, then click OK.

See Defining field indexing options and Defining global fields (fields with global storage).

11.

Click OK to close the Specify Calculation dialog box.

12.

Continue to define fields or click OK.

Important To avoid confusion when using dates in FileMaker Pro, always define calculation formulas to enter four-digit years. For more information, see Conversion of dates with two-digit years.

Notes

You can type field names, operators, and functions in the Specify Calculation dialog box instead of using the fields or operators lists.

A calculation field calculates a result from values in other fields in the current record, from related records using a relationship, or from fields defined with global storage options.

In contrast, a summary field produces a result using values in more than one record in the database. For more information, see Defining summary fields.

Values, expressions, functions, and parameters can be uppercase or lowercase.

FileMaker Pro calculates the formula when you close the Manage Database dialog box. There may be a delay if your database contains many records (for example, 10,000).

To change the way functions are displayed in the dialog box, choose a category from the View list.

Calculations that include a related field, summary field, global value, or a reference to another unstored calculation are unstored.

Calculations defined with global storage options display values calculated using the last modified record.

If any field on a layout containing unstored calculations is set to auto-resize, calculation results will be updated when the window is resized.

You can reference container fields in calculations and display container data in calculation fields. For information, see Defining container fields.

References to related fields can become invalid if the related file is missing, or if you delete a field in the related file. FileMaker Pro alerts you that a field reference is invalid when you display or change the calculation formula. Changing the field type in a related file may cause unexpected behavior.

For more information about creating formulas, see About formulas.

If your FileMaker Pro file accesses data from an ODBC data source, you can use supplemental fields to define unstored calculations or summary instructions that act on data coming from the external sources. For more information, see Using supplemental fields.

If you are using FileMaker Pro Advanced, you can copy field schemas from one file and paste them into the same file or some other file. For more information, see Copying and pasting field schemas (FileMaker Pro Advanced).

Calculation examples

Field name Field type Calculation

Today's Date

Date

Get( CurrentDate )

Full Name

Text

FirstName & " " & LastName

Sales Tax

Number

SubTotal * .08

Related topics

Working with formulas and functions

About functions

Changing calculation formulas

Formatting and setting up field objects in Layout mode

Contents > Creating a database > Defining database fields > Defining calculation fields