Defining a Compute Field

A compute field computes its value using selected fields, values, and operators.

For example, the database field Pay Rate stores the employee salary. To calculate weekly pay, you can define the compute statement:

Pay Rate / 52

A compute statement can include variable values or constant values. A variable value is a value from a field or another compute statement. The five operators you can use in a compute statement are add (+), subtract (-), multiply (*), divide (/), and exponentiation (^). In the above example, Pay Rate is a variable value and 52 is a constant value. The operator is divide (/).

In batch programs, you can also use a compute field to calculate a total. The two ways to compute a total with a compute field are Recalc Total and Sum Total. Sum Total first calculates the result of each line of the total, then adds the results. Recalc Total first totals each field or value in the equation, then performs the compute statement using these totals.

For example, if you have a purchase order that has several detail lines:

  • To calculate the amount of the purchase order, define a compute statement to compute the amount of each detail line (quantity * item cost) and select Sum Total. Sum Total computes the amount of each detail line, then adds the detail line amounts to get the purchase order amount.

  • To calculate the average item cost of the purchase order, define a compute statement to compute the average cost of each detail line (quantity / total cost) and select Recalc Total. Recalc Total computes totals in the following manner:

    • sums the quantities of the detail lines,

    • sums the total costs of the detail lines, and

    • performs the compute statement using these sums to get the average item cost of the purchase order.

To define a compute field

  1. On the Format form, press Output (F3) and choose Compute.

    - or -

    On the Report Format form, press Field (F3) and choose Compute.

  2. If compute statements exist, the Select Compute Name window appears. Select an existing compute field, or select New Compute to define a new compute field.

    If compute statements do not exist, or if you select New Compute, the Define Compute Statement window appears.

  3. Type a compute statement.

    You can use a total of 10 operands (fields and values) and 9 operators in the compute statement.

    The operators you can use are add (+), subtract (-), multiply (*), divide (/), and exponentiation (^). Use parentheses to define precedence.

    • To use an operator or a constant value in the compute statement, type the desired key.

    • To use a field in the compute statement, press Field (F3) and choose the desired field type. Some options are not valid on certain types of compute fields (for example, a compute field used in a sort order cannot use a one-to-many sum field or a record count field). For a description of each field type, see the table following this procedure.

  4. If the compute field is for a report total, you can define a condition on a field in a compute statement, making the compute statement use the field only if it satisfies the condition. Select the field for which you want to define a condition. Press Define (F6) to add, change, or delete a condition on the field. For more information, see Defining Conditions for Objects.
  5. Press OK twice.
  6. In the Compute Definition window, type a name for the compute statement.
  7. For batch programs, select Recalc Total or Sum Total.

    When computing a total, Sum Total first calculates the result of each line in the file, then adds the results; Recalc Total totals each field or value in the equation, then performs the compute statement. Recalc Total and Sum Total are not valid on certain types of compute fields.

  8. Press OK.
  9. In the Form Output Field Definition window, change the default attributes, if desired. For more information, see Defining Output Field Attributes.
  10. Press OK.

    The field appears on the Format form.

    To label or move the field, see Labeling, Moving, and Deleting Fields.

  11. Press OK twice to save the form.

    The system saves the form and returns to the Database Structure-Program Structure form.

You can use the following field types in a compute statement. For more information on fields, see Painting Fields on a Form - Output Field Typesand Painting Fields on a Report.

Choose To
Database Select a field from the primary file or from a file with a one-to-one relation from the primary file.
Compute Select a previously defined compute statement to use in this compute statement. For instructions, see Defining a Compute Field.
Array Value Define an array value field. For instructions, see Defining an Array Value Field.
OTM Field Select the value of a field from the first record of a file with a one-to-many relation from the primary file.
OTM Sum Sum the values of a field selected from a file with a one-to-many relation from the primary file.
Record Count Count the number of records in the primary file or in a file with a one-to-many relation from the primary file. Select the file whose records you want to count.
Current Date Use the current date in the compute statement.