Defining physical data fields

You need to define a physical data field for each field in the data source you want to add to the ACL table layout. In most cases, the required physical data fields will be defined using the Data Definition Wizard, but you can define additional fields manually or you can choose to define all fields in the table layout manually.

To define a physical data field:

  1. Select Edit > Table Layout.
  2. Click the Edit Fields/Expressions tab.
  3. Click Add a New Data Field .
  4. In the data preview area, click and drag in any of the data rows in the grid to highlight the field, or you can specify this information in the Start and Len.text boxes.
  5. Enter the name for the field in the Name text box.
  6. Select the appropriate data type from the Type drop-down list.

    The type you select must match the data type in the source data, or must be appropriate for how you are using the data. For example, a field may be numeric data in the data source but you might want to define the field in ACL as character data. The data types that ACL has identified as being valid selections are displayed in the Valid Data Types list. The most likely matches are listed first, with common types being listed before system or application-specific types.

  7. 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.
  8. 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.
  9. 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.
  10. Depending on the data type you select, you may need to specify values for the following settings:
    • Dec. – Specifies the number of decimal places. This option is enabled only for numeric fields.

    • Format – This option controls the display format of numeric fields in views and reports. It also specifies the input format of datetime fields in source data. The drop-down list is disabled when data types other than numeric or datetime 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 that use an IF statement. By default, if an IF statement evaluates to false, the field is assigned an empty value (blanks for character fields and dates, and zeros for numeric fields). If the Static checkbox is selected and an IF statement 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 IF statement evaluates to true and a new value is used.

    • Datetime – Select this option to specify that a numeric field should be interpreted as a datetime field. If the Datetime checkbox is selected, you must also specify the datetime format to use in the Format drop-down list.

    • 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, and only one default filter can be specified for each table layout.

  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. For information about how to add the field to a view, see Adding columns to a view.

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