Formula Editor User Interface

Formula Editor is a tool for editing formulas used in your database.

The Formula Editor window consists of the following parts:

• 1 - Pane for quick insertion of field values and variables.
• 2 - Pane for quick insertion of functions and constants. The appearance of this pane may vary depending on the cursor position in the edit pane. For example, there can be a button to insert a constant particular to the edited function.
• 3, 4 - Current function and its parameters description. When the cursor is positioned inside a function, this pane shows a brief description of this function and the list of its parameters. Double-click any parameter to open a quick insert window.
• 3 - Edit pane. Displays the formula. This pane allows manual formula editing.

While editing the formula, you may press the Ins key to open a quick insert window to add a constant or a field value. Most formulas support this feature.

Formula Composition Basics

You can use the following elements to build formulas:

• Prime and fractional numbers;
• Simple operations - addition (+), subtraction (-), multiplication (*), division(/), residue of division (%) ;
• Brackets;
• Functions, including logical ones;
• Current database field values. Designated by square brackets: [Field Name].
• Constants and field identifiers. Designated by vertical bars: |Constant Name|.

Operation precedence:

• First - actions in brackets;
• Second - all other conditions being equal, multiplication and division operations have higher priority over addition and subtraction;
• Third - all other conditions being equal, expressions are evaluated from left to right.

Simple Examples

• 2+3. This example uses two numbers (2 and 3) and one operator. The result is 5.
• 2*5+3. This example uses three numbers (2, 5 and 3) and two operators. The result is 13.
• 2*(5 + 3). This example uses three numbers (2, 5 and 3), two operators and two brackets. The result is 16 as the sum in brackets 5+3 is calculated first.
• int(6.3). This example uses one number and the int function returning the integer part of the number, with two brackets. The result is 6.
• int(3.9+1.3). This example returns the integer part of the sum of 3.9 and 1.3. The result is 5.
• [Cost]. This example returns the value of the Cost field.
• int([Cost]). This example returns the integer part of the value of the Cost field.
• [Cost]*[Quantity]. This example returns multiplication of the cost and quantity fields.
• If([Cost],|>|,100). This example returns 1 if the cost is greater than 100; otherwise 0 is returned.
• If([Cost],|<|,100). This example returns 1 if the cost is less than 100; otherwise 0 is returned.
• If([Cost],|>|,100)*[Cost]. This example returns the cost value if the cost is greater than 100; otherwise 0 is returned.
• repMathOp(|Cost|,|Sum|). This example returns the sum of costs of all records in the report. In this case |Cost| is an identifier that denotes the fields to sum up, and |Sum| is a constant of an action to perform.
• repMathOp(|Cost|,|SimpleAverage|).  This example returns the simple average of costs of all records in the report.
• If(repMathOp(|Cost|,|SimpleAverage|),|>|,100). This example returns 1 if the simple average of costs of all records in the report is greater than 100; otherwise 0 is returned.