Rules for Creating Formulas

Working with Formulas > Rules for Creating Formulas

This page offers general rules for creating formulas and ways to determine whether a formula is valid:

Expressions must follow a set of rules for the software to interpret them. To create a formula with correct syntax, the following rules apply:

•	The expression must be the correct type for the data operation for which it is used. If the data operation requires a logical condition, then the expression needs to produce a logical condition.

•	All of the fields, functions and values used in the expression must have data types that can be used correctly together.

•	All of the fields, functions and values used in the expression must be spelled and formatted correctly.

•	All the functions used in the expression must have the correct set of parameters.

•	Each open parenthesis, "(" used in the expression must have a corresponding close parenthesis, ")", and vice-versa.

•	If the expression has multiple fields, functions or values, these expression elements must be combined correctly.

•	All operators used in the expression must be positioned and employed correctly.

•	All text values must be contained within a set of double quotation marks: " ".

•	All folder, table and field names that include spaces must be contained within a set of brackets: [ ] (see below).

•	The expression must not contain any mathematically impossible elements, such as division by zero.

•	The expression must not contain any extraneous fields, functions, values, symbols or other elements.

Valid and Invalid Expressions

When you create formula, it needs to be valid in order for the software to accept it. A valid formula is one that is syntactically correct, meaning that the identifiers, functions, operators and values in the expression are arranged as discussed in the above rules.

The formula builder has a built-in tool that lets you know the validity of any given formula by showing either a green check mark for a valid formula or a red "X" for an invalid one. This formula checker automatically updates as you type.

NOTE: The formula validation indicator is disabled for external data tables so that native database expressions can be entered in the formula builder and run against the external database.

Handling Folder, Table and Field Names with Spaces

In many situations, folder names, table names and field names do not contain spaces in them, such as "myfield" or "my_table" or "my_data_folder". These can be referenced in a formula or a query using the name or the name with brackets around it. For example the following two expression are both valid:

Field1 * Field2

[Field1] * [Field2]

At other times folders, tables or field names have spaces in them, such as "my field" or "my data folder". In order to reference these in a formula or query, the full name must be contained within brackets. For example, the following two expressions are valid:

[Field 1] * [Field 2]

Field1 * [Field 2]

When field names and table names are used together, the same rules apply, but the table name and field name portion work independently so that brackets are only required for the name that includes spaces. Here are some valid examples:

Table1.Field1 * Table2.Field2

Table1.[Field1]*[Table2].Field2

[Table1].[Field1]*Table2.[Field2]

Table1.[Field 1] * Table2.[Field 2]

[Table 1].[Field 1]*[Table 2].Field2