Brilliant Database Software - Home
Database software - Online Help System

Online Help System

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



Formulas Editor

table border="0" width="100%" cellspacing="0" cellpadding="4" bgcolor="#00000077">
Formulas Editor

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.



All topics in the "Calculations" section: