DEFINE FIELD...COMPUTED command

Used to define computed fields in ACL table layouts.

Syntax

To define a computed field:

DEFINE FIELD field_name COMPUTED expression

To define a computed field with optional parameters:

DEFINE FIELD field_name COMPUTED
<IF test> <STATIC> <WIDTH value> <PIC format> <AS alternate_column_title> <field_note>
expression

To define a conditional computed field:

DEFINE FIELD field_name COMPUTED
<IF test> <STATIC> <WIDTH value> <PIC format> <AS alternate_column_title> <field_note>
value IF condition
default_value

Parameters

field_name

Specifies the name of the computed field to create.

expression

Specify any valid ACL expression to use to define the value of the field.

IF test

Optional. Specifies a condition to use to limit the records displayed in the field.

STATIC

Optional. Specifies that the field should display the same value on every line of the table until ACL encounters a new value. For example, if there is a Last Name field in the source data where the first record displays the value “Smith”, the next five records display blank lines, and the seventh record displays the value “Wong”, ACL displays “Smith” on six consecutive lines, then it displays “Wong” on the seventh line.

WIDTH value

Optional. Specifies the display width for the field. This value is used as the column size when displaying the contents of the field in ACL views and reports.

PIC format

Optional. Specifies the formatting to apply to numeric values in the computed field. The format parameter must be a quoted string.

AS alternate_column_title

Optional. Specifies a column title that is different from the field name.

field_note

Optional. Specifies the text of a field note, which is added to the field definition in the table layout. The field_note parameter must be the last parameter on the second line, after any other optional parameters. The text of the note cannot be multiline. Quotation marks are not required.

value IF condition

The value parameter specifies the value or expression to use when the condition evaluates to true. The condition parameter specifies the logical test that is evaluated.

default_value

Specifies the value or expression to use if none of the conditions evaluate to true.

Remarks

Multiline syntax must be structured exactly as shown in the generic syntax above and the examples below. If you do not specify any of the optional parameters on the second line, you must leave the second line blank.

In addition to a default value, conditional computed fields require at least one conditional value. You must use the following multiline syntax to define conditional computed fields:

You can overwrite a field in a table layout by defining a field that uses the same name as the existing field. If SET SAFETY is ON, ACL displays a confirmation dialog box before overwriting the existing field. To avoid interrupting a script, you can SET SAFETY to OFF, and ACL overwrites the existing field without asking for confirmation.

Examples

a. Define a computed field

The following example defines a computed field named Value as the product of the Cost and Quantity fields:

DEFINE FIELD Value COMPUTED Cost * Quantity

b. Define a computed field with optional parameters

The following example defines a computed field named Value, with a specified column width, a specified numeric format, and a field note:

DEFINE FIELD Value COMPUTED
WIDTH 10 PIC "($9,999,999.99)" Value is cost times quantity
Cost * Quantity

c. Define a conditional computed field

The following example defines a conditional computed field named Sales_tax that calculates a different sales tax depending on the state in which the transaction occurred. Transactions that occurred outside the three states have a sales tax of $0.00.

DEFINE FIELD Sales_tax COMPUTED
 
.0750 * Sale_amount IF State = "CA"
.0400 * Sale_amount IF State = "NY"
.0625 * Sale_amount IF State = "TX"
0.00


(C) 2015 ACL Services Ltd. All Rights Reserved.