Formula generator

By clicking on the Generate button the following formula generator is displayed:

That dialog box, whose size can be modified and saved to the last used size, allows you to create or edit the formula with the help of the mouse and 3 tabs:

A Field tab where you can see various boxes:

A field box where appears the column names that you can select with a double-click.

A values-button allowing you to search for the values directly from within the data source table

A description box in which you can see information about the selected field.

An expression edit box where the formula is written.

Several short cut to apply quickly some characters such as parenthesis or signs like plus, minus etc. or to select automatically the next variable that appears as §X for example

Example: the next formula calculates the price to be paid according to the quantity and the discount:

(SALES_DETAILS."QTY" * SALES_DETAILS."PRICE") * (1 - SALES_DETAILS."DISCOUNT" )

Tips: How to modify the formula Editor size? If you modify the vertical size of the Formula Generator and if the drawing pin is not activated, the new window size will affect the Field List box. If you click on the drawing pin, then the new window size will affect the Expression box. That new size, horizontally and vertically will be saved until you modify it again.

A Function tab where you can see various boxes:

A group box where you can select among String, Mathematics, Date Time, Conversion and Misc depending on the function type you want to see in the right function box.

A function box that displays the functions available according to selected group in the left group box.

A description box in which you can see information about the selected function if documented.

An expression edit box where the formula is written.

Several short cut to apply quickly some characters such as parenthesis or signs like plus, minus etc. or to select automatically the next variable that appears as §X for example

Example: the next formula calculates the number of days between today and the field DATE in the table “SALES”:

CurrentDate() - SALES."DATE"

A Parameters tab where you can see various boxes:

A parameter box where you can see the parameters already defined in that query using the <Query> <Parameter> command.

An expression edit box where the formula is written.

Several short cut to apply quickly some characters such as parenthesis or signs like plus, minus etc. or to select automatically the next variable that appears as §X for example

Example: you have defined two parameters for a range of date: lowerlimit and upperlimit and would like a virtual field calculating each time the query is run the number of days between the two chosen dates. The formula, defined as a numeric virtual field, will be:

upperlimit - lowerlimit