There are two ways of calculating the value of a field.
Using a calculation formula
Using the information that is entered onto the selection screen.
Using a calculation formula to determine the value of a field
In its simplest form, a calculation formula consists of a single formula. The usual mathematical rules apply to all formulas. Formulas consist of operands and operators.
Valid operands include the short names of fields as well as numeric constants (10 or 1.5, for example) and character strings ('ABC', for example).
There are a number of special fields that are available as standard.
%NAME (the name of the user processing the query)
%DATE (the current date, on which the query is processed)
%TIME (the current time, at which the query is processed).
If an operand is a text field, you can use the notation textfeld [n:m] to access part of this field. n is the position of the first character and m is the position of the last character.
If you want to access the second up to the fifth character in the field, the notation is text[ 2:5].
If an operand is a date or time field, you can use the following notation to access the individual components of this field:
date field [YEAR] |
Year |
date field [MONTH] |
Month |
date field [DAY] |
Tag |
time field [HOUR] |
Hours |
time field [MINUTE] |
Minutes |
time field [SECOND] |
Seconds |
Valid operators include the basic arithmetic operations plus DIV and MOD. DIV is the operator for whole number division and MOD is the operator for the remainder of the whole number division.
You use parentheses in the usual way.
In the formulas, you specify the names of the symbols and the icons that are displayed in the symbol fields and icon fields. These names begin with SYM_ or ICON_ and are determined by pressing the appropriate pushbuttons. A formula that contains a symbol or an icon consists only of this symbol or icon; meaning that symbols and icons cannot be linked with other operands.
You can make calculating the value of a field dependent on a condition. This means that the calculation formula for calculating the value is available only if the condition is satisfied. If the condition is not satisfied, the field is set to its initial value.
These types of fields are required when calculating special statistics (see Defining Statistics).
You use Boolean formulas to formulate conditions. These formulas consist of comparisons (for example, AMOUNT +5 > LIMIT) that can be linked with the operators NOT, AND, OR. Parentheses are used in the usual way.
In more complex cases, you can make calculating the value of a field dependent on any number of conditions, meaning that the value is calculated in a different way depending on which of the conditions is met. You can even specify a calculation formula to be used when none of the conditions is satisfied. On the screen where you maintain local fields, you use the Complex Calculations function to set these multiple conditions for calculating field values (see Example: Local Fields).
When you determine the calculation formula, you use a number of different functions to display the available short names, symbols, and icons. You can also choose a short name, a symbol, or an icon from the display and place it in the formula at the point where the cursor was positioned.
Determining the field value from the information on the selection screen
In this case, the local field is treated like a parameter whose value is determined once by an entry on the screen. This option is not available for symbol fields or icon fields.
You select the Obligatory field, if you want to make it compulsory to specify a value for this particular field.
You can use these types of fields in the conditions for calculating other local fields. This means that it is possible to influence these calculations during the query runtime.