Using Formula Fields

Formula fields are managed from Formula Fields screen. Once a calculated field is created, it is placed on Fields List screen. You can then drag and drop it on the report like any other field.

1. On the Desktop Studio screen, click  button from the toolbar.
   OR

2. On the Desktop Studio screen, from Tools menu, click Formula Fields. The Formula Fields screen appears with a list of calculated fields (formula fields) designed for the report.

Figure 1:Formula Fields screen

You can perform the following actions:

To add/design a new formula field

Follow the below steps to create formula fields:

On the Formula Fields screen,

Figure 2: Formula Fields screen

1. Click Add The Formula Fields Expression screen appears.

Figure 3: Formula Field Expression screen

2. In Name, specify a unique name for the formula field being designed.

3. In Return drop-down box, select a return type such as number, character, Boolean, and date corresponding to the specified name.

4. In Expression, specify the formula followed by an ‘=’ sign and specify expression for the field.

Note: Report will not run if there is any syntax error in the formula.

5. Click Compile button to check the formula for syntax errors.

6. Click OK to save the created format. The newly designed formula field appears in the list on the Formula Fields.

To edit/modify a formula field

1. On the Formula Fields screen, select the formula field that you want to edit.

2. Click Modify The Formula Field Expression screen appears.

3. Edit/modify the required details.

4. Click OK to save the changes.

To delete a formula field

1. On the Formula Fields screen, select the formula field that you want to delete.

2. Click Delete

3. Click OK to save the changes.

General syntax

You can make simple to complex formula for a formula field.  General syntax for a formula field is:

FormulaName = formula

Follow JavaScript syntax to create a formula.  To create formula, you can use field names and define variables.  A formula may have if construct as well as nested if construct.  You can use logical operators too. If want to add more than one statements in formula, use semicolon ‘;’ as separator between two statements.

Example:

1. NewForm1 = var a = 5 ; b = 3 ; if (a!=b) {  f = a } {NewForm1=f}

2. TotalAmount = var total ; if (unitprice < 10 ) {total = unitprice*quantity} else {total = unitprice} {TotalAmount = total}