Defining computed fields

To define a computed field:

  1. Select Edit > Table Layout.
  2. Click the Edit Fields/Expressions tab.
  3. Click Add a New Expression .
  4. Enter the name for the field in the Name text box.
  5. Enter the expression in the Default Value text box, or click f(x) to create the expression using the Expression Builder.
  6. Optional. Specify the display width for the field in the Width text box. This value is used as the column size when displaying the field in ACL views and reports.
  7. Optional. Specify the display name in the Alternate Column Title text box. This name is used as the column heading, instead of the field name, when displaying the field in ACL views and reports. If a value is not specified, the field name is used.
  8. If you want to limit the records displayed in the field, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
  9. Depending on the data type specified in the Default Value text box, you may need to specify values for the following settings:
    • Format – This option controls the display format of numeric fields in views and reports. The drop-down list is disabled when other data types are selected. You can select the format from the drop-down list, type in the format manually, or edit a format from the list after you have selected it.

      If the Format drop-down list is blank, the default display format specified in the Options dialog box applies to the data in this field. The format you specify here overrides the default format.

    • Suppress Totals – Select this option to prevent the values in this field from being totaled. ACL automatically totals numeric fields in reports. Some numeric fields contain information that should not be totaled, such as unit prices or account numbers. This option is enabled only for Numeric data types.

    • Static – Select this option to alter the default behavior ACL uses when evaluating conditional fields. By default, if a test evaluates to false, the field is assigned an empty value (blanks for character fields and zeros for numeric fields). If the Static checkbox is selected and a test evaluates to false, ACL repeats the last valid value in the field rather than using an empty value. This behavior is repeated as each row is tested until the test evaluates to true and a new value is used.

    • Datetime – This option is not available for computed fields.

    • Control Total – Select this option to identify the field as a control total field. When you extract or sort data to a new table, ACL includes the input and output totals of a control total field in the table history. Input refers to the original table. Output refers to the new table. If you specify control totals for more than one field, the table history reports on only the numeric field with the highest starting position.

      A control total is the sum of values in a numeric field, which can be used to test for data integrity. For example, you can compare the control totals that the data provider supplies with those that ACL computes to determine whether you received all of the data.

    • Default Filter – Specify this option if you want to filter the records in the default view based on the value of this field each time the ACL table is opened. Only records that evaluate to true are displayed, and the filter is applied automatically. This option is enabled only for the Logical data type.

  10. Complete the following steps if you want to add a condition. You can add multiple conditions, if necessary.
    1. Click Insert a Condition .
    2. In the Add a Condition and Value dialog box, click Condition to create an expression using the Expression Builder, or enter an expression in the Condition text box. Click Value to create an expression using the Expression Builder, or enter a value in the Value text box, and click OK.
    3. If you want to create a condition that is similar to an existing condition, select the condition you want to copy, click Duplicate Condition , and then click Edit Condition and Value  to modify the settings for the new condition.
    4. If the order in which multiple conditions are evaluated is important, select a condition and click Move Condition Up  or Move Condition Down  to move it to the appropriate position.

      If you have defined multiple conditions, ACL evaluates them in the order they are displayed, starting at the top. For each record, the value associated with the first condition that evaluates to true is assigned as the value of the computed field.

  11. If you want to add a note about the table layout, click Edit Field Note , enter the note text, and click Close.
  12. Click Accept Entry .

    ACL adds the field definition to the table layout. You can add the field to views or reports, and use it in commands. For information about how to add the field to a view, see Adding columns to a view.

Related tasks
Creating expressions using the Expression Builder

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