About physical data fields

Physical data fields are fields that are defined based on their physical position in the data source. The definition of the field in the table layout includes the start position of the field in the record, the length of the field, the data type of the field, which determines how ACL reads the data stored in the field, and the name of the field. Additional information may also need to be specified based on the data type, and the settings you want to provide to override default values. For example, the formatting to apply to numeric fields, or the column title used in views and reports, can either be left blank and a default value is assigned, or you can specify the value to use.

ACL has a number of reserved keywords that cannot be used as field names. For a complete list, see Reserved keywords.

Defining datetime fields

Depending on the data source you are working with, datetime information (dates, datetimes, or times) may be stored as character data or numeric data. When you define a field that contains datetime information, ACL treats it as character data by default. To ensure ACL reads datetime information correctly, you need to select Datetime as the data type, and specify the datetime format from the Format drop-down list, when you define or modify the field in the Edit Fields/Expressions tab in the Table Layout dialog box.

The datetime format identifies the characters or digits in the source data that represent year, month, day, and/or hour, minutes, and seconds, and any characters used to separate these parts of datetime data. To match the way datetimes are stored in the data source, you can select an existing datetime format, specify your own datetime format, or select an existing format and modify it. For example, if December 31, 2014 is stored in the data source as 14-31-12, you would need to enter YY-DD-MM as the datetime format so that ACL can interpret the date values correctly. For more information, see Formats of date and time source data.

The datetime format you select or specify does not affect how datetime values are displayed in ACL or formatted in reports, which depends on the Date Display Format and/or Time Display Format settings specified in the Date and Time tab in the Options dialog box. For more information, see Options dialog box: Date and Time tab.

Defining overlapping fields

In most cases, when you define the fields in a record each byte position in the record will only be assigned to one field, and defining a table is a matter of defining the start position and length of each field in the record. In some cases, however, you may need to define fields that overlap with each other, and where some byte positions are used in more that one field. This can happen either because of the way the data source is structured, or because of how you want to work with the data in ACL.

For example, you could define the first six positions in a data source as a datetime field with the format DDMMYY, and then separately define a two-byte numeric field in position 3 and 4 for the month. This approach would allow you to access the entire date in one field for aging purposes, and have the month as a separate value in another field for generating monthly totals.

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