The All-in-One Database Software Solution

Online Help System

Back to Brilliant Database Web-Site | Write to Support | Help Index

Formulas Editor

Introduction

Formula Editor is a tool for editing formulas used in your database. Use of formulas makes it possible to automate processing data in your database. For example, your database stores prices for some goods and quantities of the goods. You may use the [Cost]*[Quantity] formula to calculate the total cost of the goods. You may use even more complicated formulas containing functions working with a set of database software records. For example, the repMathOp(|Cost|,|SimpleAverage|) function will calculate simple average of costs in the set of database software records that the edited report incorporates.

You can access Formula Editor in the following ways:

Create a Mathematical Field on the form using Form Editor.
Add a Formula Field into the report using Report Style Editor.
Create a button with the Apply Formula to a Field action using Scripts.
Create a mathematical variable.

Formula Editor User Interface

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(/);
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 database software 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 database software records in the report.
If(repMathOp(|Cost|,|SimpleAverage|),|>|,100). This example returns 1 if the simple average of costs of all database software records in the report is greater than 100; otherwise 0 is returned.