The Columns window defines the access levels required to both view and edit specific fields within the system. If a user opens a window but does not have a high-enough access level to view a column, the column will be invisible to that user. This is appropriate for fields such as salary or social security number. If a user enters a column with high-enough access level to see a field, but not enough to edit the field, it will be visible but disabled so the user cannot change the value.

Precautions

• Avoid changing any column label to something that does not describe the column
• Only authorized personnel such as a System Administrator should be allowed access to the Columns window
• These columns usually have their right to edit set to 5 – Super or higher to provide needed security to these sensitive variables. Avoid changing values in any of the following columns without first consulting an AgileAssets technical representative:
  • Column ID
  • View Type
  • Table Name
  • Item Column Name
  • Column Name Description
  • Where Clause
  • Show Details
• The ITEM_COL_NAME and DESCR_COL_NAME columns must contain real names. These columns are in the table from which the Columns window takes its data but not the window itself and as such, these columns may only be edited directly in the database.

Columns Window

The Columns window contains the following columns:

• Column ID: This column shows the name of the column within the database.
• Column Label: This is the name of the column as seen by a user.
• Data Min/Max: For Number columns, these two fields set the limits of the data for validating entered data. If entered data exceeds the thresholds configured in these fields, the record cannot be saved.
• Data Scale: For Number Decimal columns, this field sets the number of digits to the right of the decimal point.
• Data Size: For Number Decimal columns, this field sets the total number of allowed digits (both right and left of the decimal point).
• Data Split Rule: For Number columns, this column shows the Split Rule used in the Finest Partition operation.
• Description Column: This field shows the name of the column that provides a description of the column.

• Format: This column configures how the data in the column is formatted. Dates and times are formatted automatically as set in the System Parameters window. Monetary values are formatted by putting [Currency] in the column, with the currency format being that which is configured in the System Parameters window. Finally, numbers may be formatted using Java formatting techniques; see this website for more information: http://www.unicode.org/reports/tr35/#Number_Format_Patterns.

  Note: You cannot edit the Format column directly. Instead, right-click the record for the column and then click Edit. The application displays a dialog box. Click Next to view the second page of the dialog box and, provided the View Type is R-Number, the field for setting the format is at the bottom of the dialog box.

• ID Column: This field shows the internal name of the column that increments as records are added.
• Parent Column ID: For those columns that are in a child table, this field shows the internal name of the column with which the column is associated in the parent table.
• Primary Key Table Name: This field shows the name of the table in which the primary key resides.
• Right to Edit: This is the minimum access level setting in a user's profile (for the window) to allow the user to edit the column within that window (also see Access Level Settings).
• Right to See: This is the minimum access level setting in a user's profile (for the window) to allow the user to see the column within that window (also see Access Level Settings).
• Table Name: This field shows the name of the table in which the column appears.
• Use for Weighted Average: A check mark indicates that the column may be used to create weighted-average aggregations in tabular reports.
• Where Clause: When the data in the column is filtered, this field provides the statement that filters the data.
• Window ID: This field provides the internal name of the window in which the column appears.
• View Type: This column only affects the display of data when used and filtered in standard reports. This allows a System Administrator to create new columns for reporting and choose how the columns will be configured for the user in filtering and displaying the data in reports. The permitted variables for View Type are as follows:
• • A: Attachment (allows connection to external files)
  • B: Color
  • C: Check box (a variable that has two values displayed as a check box)
  • D: Date
  • G: Geometric data
  • H: Big List (a filterable list that displays in a popup window)
  • R: Number (real)
  • S: String (character)
  • T: If the Table_Name and Parent_Col_Name columns are completed, this indicates a tree-type list. If the columns are not completed, this indicates a simple list.
    When the View Type variable is T (tree), the following additional variables may be used:

    • DB Tables and Views: This is the name of the lookup table that contains the list of legal values (codes) and a description of each.

    • ID Column: This is the name of the column in the lookup table that is the code. This is the actual data value stored in the database. (This field does not display until a table is selected in the DB Tables and Views field.)

    • Label Name: This is the name of the column in the lookup table that is the literal description of the code. This is what the user will see on the screen when reporting or filtering data based on this column. (This field does not display until a table is selected in the DB Tables and Views field.)

    • PK Tables: If you pick a view in the DB Tables and Views field, this field supplies the underlying table needed for the foreign key that is required for future automatic processing.

    • Where Clause: This is the Oracle "where" statement to apply to the Oracle lookup table in order to get only the set of values that apply to this column ID. (This field does not display until a table is selected in the DB Tables and Views field.)

• Edit: This command provides edit capability for the attributes of an existing column. When you select this command, a new window appears that is identical to the window displayed with the Insert command: except that the column ID is already present and cannot be changed. Three column attributes are editable here: column label, right to see, and right to edit. To edit the remaining column attributes, click the Next button. The information available and editable in the subsequent pane varies depending upon the column type selected as shown at the top of the pane:
  • When the T-List column type is selected, then you select the table from which the data for the T-List column will originate. Then you select the column in that table that uniquely identifies the data. Next, you select the column in that table that will be displayed when the T-List is used as a drop-down list. Finally, you set the "where clause" if you want only a portion of the data selected to be shown for the T-List.
  • When the String column type is selected, then the size of the column (how many characters) must be provided.
  • When the Attachment, Color, Date, or Checkbox column type is selected, then the rest of the pane is blank.
  • When the R-Number column type is selected, then the following information must be provided:
    • The Numeric Type. Select the type by clicking the appropriate radio button: Integer, Decimal, or Real. If Decimal is selected, then additionally provide the size (number of digits on both sides of the decimal point).and scale (number of digits to the right of the decimal point).
    • The minimum value and maximum value used for data validation.
    • Whether the values in the column may be used for weighted-average aggregations.
    • The Split Rule if your agency has implemented either the AgileAssets Pavement Management System or Data Manager product. When one of these products is implemented, you need to specify the Split Rule because that rule is needed in the Finest Partition operation; it determines how the numeric value will be adjusted when road section limits are more finely partitioned. Click here for the available Split Rules.

• Insert: This command inserts a new column into the system. Once a column is defined in this window, other windows (that build or adjust data table structures) can assign it to one or more data tables. When you select this command, the system displays a dialog box. You enter the column ID, descriptive label, comments, and other column attributes. See the Edit command description for details on column attributes.

  Note: The text you enter for comments does not just reside in the AgileAssets database; it is also entered into the Oracle system table that stores comments.

  • The following rules must be observed when naming new columns:
    
    • The column name must begin with a letter.
    • The column name cannot be longer than 30 characters.
    • The column name must be made up of alphanumeric characters or the following special characters: $, _, and #. If the column name uses any other characters, it must be enclosed in double quotation marks.
    • The column name cannot be an Oracle "reserved word." (See the following site for reserved words: http://download-uk.oracle.com/docs/cd/A87862_01/NT817CLI/appdev.817/a42525/apb.htm.)

For a description of rights (that is, access permissions), see the Access Level Settings window.