Guidelines for Specifying Derivation Formulas
The derivation formula provides the calculation for determining the value of a derived field. When specifying a derivation formula, follow these guidelines.
A simple field can be derived from:
• Other simple fields, including other derived fields
• Aggregates of table field columns (cannot be derived directly from a table field column)
• A table field column can be derived from other columns in the same table field. A table field column cannot be derived from a simple field or aggregate value.
• You can use arithmetic operators, aggregates, and constants in derivation formulas, as explained in the following sections.
Arithmetic Operators in Derivation Formulas
You can use the following arithmetic operators in derivation formulas (in descending order of precedence) for both simple fields and table field columns:
‑ (minus sign)
** (exponentiation)
*, / (multiplication, division)
+, ‑ (addition, subtraction)
You can use parentheses ( ) to change the order of evaluation.
Aggregates in Derivation Formulas
You can use aggregate functions in derivation formulas for simple fields, but not in derivation formulas for table field columns. To derive a value for a simple field based on an aggregate of a table‑field column, use the following syntax:
aggfunction (tablefieldname[*].columnname)
This table lists the aggregates that can be used in derivation formulas, the data types that can be used with each aggregate, and the data type of the derived field:
If a derivation formula contains a reference to an aggregate of a table field column, and the table field contains invalid values or is empty (has no values), the value of the aggregate is:
• Zero (0), if the aggregate is count
• Null, if the aggregate is avg, sum, max, or min
If the formula contains references to other fields that contain invalid values, VIFRED blanks out the derived field.
Constants in Derivation Formulas
You can use constants in derivation formulas for simple fields and for table field columns. For example, you can specify the derivation formula for Field_A as:
2 * field_b
Supported data types for constants in derivation formulas are char, varchar, c, text, integer, floating point, date, and money. Specify dates, money, and non‑numeric strings in single quotation marks, as:
'3‑6‑90' + field_b
Dates in Derivation Formulas
You can perform the following arithmetic operations on date data types in derivation formulas:
• interval + interval = interval
• interval + absolute = absolute
• interval ‑ interval = interval
• absolute ‑ absolute = interval
• absolute ‑ interval = absolute
Circular References in Derivation Formulas
A circular reference occurs when Field_A depends on Field_B which, in turn, depends on Field_A. VIFRED does not allow circular references in derivation formulas. VIFRED checks for circular references at form initialization and at form save times. If it finds a circular reference, VIFRED displays an error message. You must correct the problem before VIFRED can save the form.
Examples of Derivation Formulas
The following formulas are valid derivation formulas for simple fields:
Field_3 ‑ Field_2
(Field_3 ‑ Field_1) * (Field_5 ‑ Field_4)
Field_2 + sum(TableField1[*].Column2)
Lastname + ',' + Firstname
'today' + '30 days'
'today' ‑ '1 yrs 2 mos 3 days 12 hrs 24 mins 14 secs'
The following formulas are valid derivation formulas for table fields:
(TableField_1.Column1 + TableField_1.Column2) /2
Order_items.price * Order_items.quantity
Order_items.price *.90