Computed Data Field Syntax (Formulas)

The following procedures are the same for both windows and non-windows (except where noted).

A computed data field is created by using any of the Data Names from the format in an expression. 3GL functions and variables can be used as well as single and multiple occurrence Data Names.

Computed data fields make use of formulas.

The value in the computed field is created using the current values for any Data Name and 3GL variable. The operator cannot change the resulting contents of the field.

A computed data field is for display only.

A Data Name field can be changed into a computed field simply by specifying the addition of a space or zero to it, depending upon whether the element is string or numeric. This excludes the displayed value from change by the operator, regardless of the settings for security in format.

When using multiple occurrence Data Names in a computed field you must specify which occurrence to use. For example, COST(3) is valid, COST is not.

A display window can be created for a formula by preceding the Data Name with:

/FIELD(width)=

Width is the length of the window. This must be shorter than or equal to the element length.

String Formula

Use the following syntax to set up string formulas:

[/FIELD(len)=]str-elmt[(strt,len)]|
      rep-str-elmt(num)+str-val...

/FIELD

is the keyword.

len

is the window size.

str-elmt

is the name of a string Data Name defined in format.

strt,len

(optional) is a substring of the Data Name value. Strt indicates the position to begin the substring and len is the number of characters of the substring.

rep-str-elmt

is a repeating field name (multiple occurrences).

Examples:

The following examples are based on a format with alphanumeric Data Names LAST-NAME with length=20 and FIRST-NAME with length=10, and alphanumeric multiple occurrence element MONTH with length=12 *4.

/FIELD(6)=LAST-NAME(1,4) + FIRST-NAME This specifies a computed data field, based on the current values of the Data Names. The resulting value is the first four characters of the value in LAST-NAME plus the value of FIRST-NAME. Only six characters of the entire value are displayed on the screen. This field cannot be modified in file maintenance or scripts.

LAST-NAME + “ “
This specifies a display-only field which is the value of LAST-NAME with a space appended. The user cannot modify this data field.

MONTH(1) + MONTH(12)
The values of occurrence 1 and occurrence 12 of the Data Name MONTH are concatenated together. The user cannot modify this data field.

Numeric Formula

Use the following syntax to set up numeric formulas:

[/FIELD(len)=] num-elmt|rep-num-elmt(num) num-oper    num-
val...[:"mask"]

/FIELD

is the keyword.

len

is the window size.

num-elmt

is the name of a numeric Data Name defined in the format.

rep-num-elmt

is a repeating field name (multiple occurrences).

num-oper

is a valid arithmetic operator ( + - * / ^ ).

num-val

(optional) is a numeric constant.

"mask"

(optional) is a mask used for numeric formatting. Only one mask (positioned at the end of the formula) is used per formula. For more information, see Mask Characters in the Dictionary-IV User Guide.

Examples:

These examples are based on a format with numeric Data Name COST, length=8.2, and numeric multiple occurrence element VALUE, with length = 8.2 * 5.

COST * VALUE(4)
The product of the current value of COST and the fourth occurrence of VALUE is displayed in this field and cannot be modified by the operator.

/ 4:“$###,###.00”
If the value of COST is 8.4, then the value of this field (8.4/2) will be calculated and formatted for display as $2.10. The user cannot modify this field.

VALUE(1) + VALUE(2) + VALUE(3) + VALUE(4) + VALUE(5)
The values of all occurrences of VALUE are added together and displayed in this field. The user cannot change the resulting value.

Conditional Formula

This formula displays the calculated value if the condition is true.

IF cond THEN /FIELD(len) = val

cond

is an expression or statement, which is tested to determine whether or not the formula will be calculated.

len

is the number of characters used to display the calculated value.

val

is a Data Name or formula (as described previously) that results in a value.

Examples:

Assume that you want to calculate Gross Profit for an accounting screen. You could define a formula for Gross Profit as follows:

YTD-SALES - YTD-COST However, if you want to calculate Gross Profit Percent, you need to divide Gross Profit by the YTD-SALES:

(YTD-SALES - YTD-COST) / YTD-SALES

This would cause an error whenever YTD-SALES had a value of zero, because division by zero is an undefined mathematical statement.

To avoid this error, you can use a conditional formula as follows:

IF YTD-SALES <> 0
      THEN /FIELD(8) = (YTD-SALES-YTD-COST)/YTD-SALES

Assignments in a Formula

This is useful for creating a calculated default value, this formula displays value-1 and assigns value-2 to the specified Data Name.

val-1; data-name = val-2

val

is a Data Name or formula (as described previously) that results in a value.

Conditional Assignments in a Formula

This formula displays value-1 in the formula field, and if the condition is true, assigns value-2 to the specified Data Name.

val-1; IF cond THEN data-name = val-2

val

is a Data Name or formula (as described previously) that results in a value.

cond

is an expression or statement, which is tested to determine whether or not the assignment will be performed.

This type of formula is a combination of a conditional formula and using an assignment in a formula.

Calculated Default Values in a Formula

It is sometimes useful to assign the result of a formula calculation to another field. For example, using a formula, you can assign a default value to a field based on a calculation of values in other fields. This, in effect, creates a calculated field for which the operator can change the value of the result.

Calculated Fields in a Formula

You can use a calculated field in a formula, but the fields used in the calculation must be defined in the format (although it does not have to be in the data file).

Example

HOURS * RATE = GROSS-PAY If the GROSS-PAY field is in the format, you can assign the result of the calculation to the GROSS-PAY field.

GROSS-PAY * .0127 = STATE-TAX
You can then use the calculated GROSS-PAY field, in a formula, calculate State Tax.

Calculated Values Stored in the Data File

The result of a formula calculation is not stored in the data file. However, you can define a formula so that it will automatically store the calculated value in the data file.

This may be an uncommon operation, but it is presented here to illustrate additional features in the screen formula. To do this, you must:

• Define in the format a Data Name that will store the calculated value.
• Define in the screen a special formula where you assign the calculated value to the Data Name.

Example:

Define the following format:

NAME (length = 10)
RATE (length = 5.2)
HOURS (length = 2.0)
GROSS-PAY (length = 6.2)

Define a screen containing all the Data Names. Then, in between RATE and HOURS, add the following formula field called OVERTIME-RATE:

RATE + (RATE / 2); GROSS-PAY = HOURS * RATE

This formula will display the Overtime Rate of time-and-a-half and then assign the Gross Pay calculation to the GROSS-PAY field. When the record is added or changed, the calculation will be stored in the data file under GROSS-PAY.

Additional Formula Examples

The following fields are taken from a customer inquiry screen and show examples of formulas with assignments:

CUST-SALES-YTD:“###,###.00”

Displays the year-to-date sales for customers using the defined mask.

CUST-COST-YTD:“###,###.00”; GROSS-PROFIT =
CUST-SALES-YTD − CUST-COST-YTD

Displays the year-to-date cost for customers using the defined mask. Calculates a Gross Profit field by subtracting year-to-date customer cost from year-to-date customer sales.

GROSS-PROFIT:“###,###.00”; GROSS-MARGIN =
(GROSS-PROFIT * 100) / (CUST-SALES-YTD + .01)

Displays the Gross Profit field using the defined mask. Calculates the Gross Margin by multiplying the gross profit by 100 and dividing it by the year-to-date customer sales (plus 1%).

From the Dictionary-IV Developer Guide, version 8.9.1. Open this page in the PDF