Defining DataTable Objects

Before you can create a DataTable object using the Create DataTable command (or the VIRTUAL option), you must define the DataTable object in a DataTable definition. The DataTable definition defines the structure of the in-memory DataTable object. DataTable definitions are stored as ASCII files and can be created and edited in any text editor. The recommended file extension for DataTable definitions is .lvdtd.

The basic syntax for the DataTable definition document is:

where:

AppTableName is the name of the App table to use to create the in-memory DataTable object. If you are using Oracle, the AppTableName must match the case that is in the Oracle database.

Note: If you do not specify any filtering options, the entirety of the AppTableName App table will be downloaded to the DataTable object, which may use an undesirable amount of memory. Longview recommends that you refine your DataTable object to include only the necessary elements of the App table using the relevant functions.

You can use the following functions to filter a DataTable definition:

• Columns (for DataTable definitions)

• RowLimit (for DataTable definitions)

• SymbolFilter

• UserFilter

• Where

VIRTUAL defines the DataTable object as a virtual DataTable object. Use this parameter when you want to define a DataTable object structure on-the-fly without creating an App table in your database. If you use this parameter, you must use the AddColumn function to define the appropriate columns.

WRITABLE is an optional parameter used in conjunction with VIRTUAL Tables to define the Table as writable.

Note: Longview Tables and Hierarchies that are based on a virtual DataTable are read-only when the WRITABLE option is not defined.

To view a sample DataTable definition, see Sample documents for Tables.

AddColumn

Use this function to define a column in a virtual DataTable. This function applies only to virtual DataTables.

Syntax:

where:

• DataType is the data type for the column, and can be one of the following:
  • boolean — Use this data type to add a column with a check box.
  • number — Use this data type to add a column with an editable text box that accepts numeric values.
  • string — Use this data type to add a column with an editable text box that accepts alphanumeric entries. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.
  • date — Use this data type to add a column with a calendar control.
  • dimension — Use this data type to add a column with the Longview Dimension Selector.
  • symbol — Use this data type to add a column with the Longview Symbol Selector. If you use this data type, you must specify the Values:MyValues keyword-value pair.
  • user — Use this data type to add a column with a combo box that is populated with users in the system. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.
  • userlist — Use this data type to add a column with a multiple-selection list that is populated with users in the system. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.

  Note: Virtual DataTables without the WRITABLE option defined are read-only. In these cases, you cannot access interactive controls, such as the calendar control, symbol selector, drop-down user list, or multiple-selection user list when viewing the Table; however, data types are taken into consideration when importing data into a read-only virtual DataTable.

• ColumnName is the name for the new column.
• SymbolDimension applies only to symbol data types and is the dimension containing the symbols to include in the Symbol Selector. For symbol data types, specify either the Dimension keyword or the DimensionColumn keyword, but not both.
• DimensionColumn applies only to symbol data types and is the column containing the dimension which will control the symbols to include in the Symbol Selector. For symbol data types, specify either the Dimension keyword or the DimensionColumn keyword, but not both.
• MyValues syntax depends on the column’s data type.

  Column data type	Syntax
  string	"ReturnValue1|ReturnValue2...|ReturnValueN[;DisplayValue1|DisplayValue2... |DisplayValueN]" where: ReturnValue1|ReturnValue2...|ReturnValueN are the values to include in the drop-down list as stored in the database. For example, FT. DisplayValue1|DisplayValue2...|DisplayValueN... are the values to display in the drop-down list. For example, Full Time. You can not specify a DisplayValue when AllowOverride is TRUE. If you do not specify a corresponding DisplayValue for a ReturnValue, the ReturnValue displays in the drop-down list.
  symbol	Valid symbol specification. For more information, see Working with symbol specifications.
  user	A pipe-delimited list of the usernames to include in the drop-down list.
  userlist	A pipe-delimited list of the usernames or user groups to include in the multiple-selection list.

• BooleanDefault is the default value for a boolean column and can be either TRUE (selected) or FALSE (cleared).

Example:

Columns (for DataTable definitions)

Use this DataTable function to specify a subset of the columns from the App table to include in the DataTable object. This function does not apply to virtual DataTables.

If you want to include all columns from the App table in the DataTable object, omit this function.

Note: You can further filter the columns displayed in a Table or Hierarchy using the Columns function. For more information, see Columns (for Table Views) and Columns (for Hierarchy Views).

Caution: If you restrict the columns and do not specify the column that is designated as the primary key, the entire Table or Hierarchy will be read-only. If you omit any non-nullable columns, the Add and Duplicate buttons will be disabled and users will not be able to add new rows.

Syntax:

where:

• ColumnName is the name of the App table column to include in the DataTable object. Separate multiple column names with a comma.

Note: The order of columns in this function determines the order that you must use for the String list variable in the Insert DataTableRow command.

Example:

OrderBy (for DataTable definitions)

Use this DataTable function to order the rows retrieved from the App table. Ordering of the rows can be done by one or more columns in a table.

Syntax:

where:

• ColumnName is the column to sort by. If the column name contains spaces, you must enclose it in square brackets. For example, [Employee Type].

Note: OrderBy does not apply to columns of type Symbol, File, User or Userlist. Using OrderBy with these types of columns will return unexpected results.

Example:

RowLimit (for DataTable definitions)

Use this DataTable function to restrict the number of rows retrieved from the App table to include in the DataTable object. This function does not apply to virtual DataTables. If you do not want to restrict the number of rows from the App table in the DataTable object, omit this function.

Note: You can use this function in conjunction with the OrderBy function to retrieve a meaningful but restricted subset of rows from the App table.

Syntax:

where:

• numRows specifies the number of rows to retrieve from the App table into the DataTables object.

Example:

SetColumn (for DataTable definitions)

Use this DataTable function to specify the options for columns in the DataTable object. Options depend on the data type for the column.

For more information on data types, see the Longview Installation Guide.

Note: Longview recommends that you include all necessary SetColumn parameters for each column in a single statement to avoid undesirable results.

Use the following table to determine whether a keyword in this function is appropriate for the column’s data type.

In addition, certain keywords determine the cell control that appears in the Table based on the column’s data type. Use the following table to determine the keywords to use to generate the appropriate cell control.

Syntax:

where:

• ColumnName is the name of the column for which to set properties in the DataTable object. For Oracle systems, this must be in the same case as in the Oracle database.
• MyValues syntax depends on the column’s data type.

  Column data type	Syntax
  string	"ReturnValue1|ReturnValue2...|ReturnValueN[;DisplayValue1|DisplayValue2... |DisplayValueN]" where: ReturnValue1|ReturnValue2...|ReturnValueN are the values to include in the drop-down list as stored in the database. For example, FT. DisplayValue1|DisplayValue2...|DisplayValueN... are the values to display in the drop-down list. For example, Full Time. You can not specify a DisplayValue when AllowOverride is TRUE. If you do not specify a corresponding DisplayValue for a ReturnValue, the ReturnValue displays in the drop-down list.
  symbol	Valid symbol specification. For more information, see Working with symbol specifications.
  user	A pipe-delimited list of the usernames to include in the drop-down list.
  userlist	A pipe-delimited list of the usernames or user groups to include in the multiple-selection list.

• AllowOverride can have one of the following values:
  • TRUE — Converts the drop-down list to a combo box, which allows users to type their own value in the cell control or select the values defined by MyValues.
  • FALSE — Restricts the cell control to a drop-down list, which allows users to select only the values defined by MyValues. This is the default.

  Note: You must specify AllowOverride in the same SetColumn statement as the Values keyword; otherwise, it produces a blank field.

• Date is the date range for a calendar control, defined using the Min and Max keywords. The format is yyyy-mm-dd. Date can be expressed only as equal to, and not greater than or less than.    

  Note: You must specify Min and Max in the same SetColumn statement. If you use separate statements, only the last instance of either Min or Max is valid.

• DefaultValue is the default value for the column. Supported types are 1) boolean column and can be either TRUE (selected) or FALSE (cleared), and 2) dimension column, which can be any dimension name within the system. For boolean columns, Longview recommends that you specify a default for NULLABLE boolean columns, otherwise, the value for new rows will be NULL, which may cause unpredictable behavior.

Example:

SymbolFilter

Use this DataTable function to specify a subset of symbols from the App table to include in the DataTable object. This function does not apply to virtual DataTables and applies only to fields with the Symbol data type. If you do not specify a subset of symbols, all symbols from the App table are included in the DataTable object.

Syntax:

where:

• ColumnName is the column of type Symbol in the App table from which to filter the symbols.
• SymbolSpec specifies the subset of symbols from the App table to include in the DataTable object. You can specify multiple symbol specifications, separated by pipes ( | ). For information on symbol specifications, see Working with symbol specifications.
• attributefilters is optional and can include up to two attribute filters linked by AND or OR, enclosed in double quotation marks, using the syntax:
  FilterType{AttdrName{Operation{Expression

  where:

  Field	Description
  FilterType	specifies the method to use to search the hierarchy for symbols matching the filter criteria and can be one of the following: ALL PARENT LEAF ROOT If you specify two attribute filters, FilterType must be the same for both filters.
  AttrName	is the name of the attribute for which to filter.
  Operation	is one of the following: EQ — Exactly equal to. NE — Not equal to.
  Expression	is a character string. If the expression contains spaces, enclose the expression in double quotation marks preceded by a backslash (\"expression with spaces\"). If the expression is a list, separate multiple items with a pipe ( | ). For Non-List Attributes, the filter behaves as follows: Attribute EQ Expression — Matches only if the attribute is an exact match of the expression. Attribute NE Expression — Matches if the attribute is not an exact match of the expression. For List Attributes, the filter behaves as follows: Attribute EQ Expression — Matches if the attribute is an exact match of the expression, or is a list of values, any one of which exactly matches the expression. Attribute NE Expression — Matches if the attribute is empty or a list of values, none of which exactly matches the expression.

Example:

TempColumn

Use this function to define a temporary column in a persistent DataTable. This function applies only to persistent DataTables. You may find this feature useful if you would like to add a temporary column to display calculated values such as percentages or ratios that are not required to be stored in the database.

Syntax:

where:

• DataType is the data type for the column, and can be one of the following:
  • boolean — Use this data type to add a temporary column with a check box.
  • number — Use this data type to add a temporary column with an editable text box that accepts numeric values.
  • string — Use this data type to add a temporary column with an editable text box that accepts alphanumeric entries. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.
  • date — Use this data type to add a temporary column with a calendar control.
  • dimension — Use this data type to add a temporary column with the Longview Dimension Selector.
  • symbol — Use this data type to add a temporary column with the Longview Symbol Selector. If you use this data type, you must specify the Values:MyValues keyword-value pair.
  • symbol — Use this data type to add a temporary column with the Longview Symbol Selector. If you use this data type, you must specify the Values:MyValues keyword-value pair.
  • user — Use this data type to add a temporary column with a combo box that is populated with users in the system. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.
  • userlist — Use this data type to add a temporary column with a multiple-selection list that is populated with users in the system. If you specify this value, you can optionally specify the Values:MyValues keyword-value pair.
• ColumnName is the name for the temporary column.
• SymbolDimension applies only to symbol data types and is the dimension containing the symbols to include in the Symbol Selector. For symbol data types, specify either the Dimension keyword or the DimensionColumn keyword, but not both.
• DimensionColumn applies only to symbol data types and is the column containing the dimension which will control the symbols to include in the Symbol Selector. For symbol data types, specify either the Dimension keyword or the DimensionColumn keyword, but not both.
• MyValues syntax depends on the column’s data type.

  Column data type	Syntax
  string	"ReturnValue1|ReturnValue2...|ReturnValueN[;DisplayValue1|DisplayValue2... |DisplayValueN]" where: ReturnValue1|ReturnValue2...|ReturnValueN are the values to include in the drop-down list as stored in the database. For example, FT. DisplayValue1|DisplayValue2...|DisplayValueN... are the values to display in the drop-down list. For example, Full Time. You can not specify a DisplayValue when AllowOverride is TRUE. If you do not specify a corresponding DisplayValue for a ReturnValue, the ReturnValue displays in the drop-down list.
  symbol	Valid symbol specification. For more information, see Working with symbol specifications.
  user	A pipe-delimited list of the usernames to include in the drop-down list.
  userlist	A pipe-delimited list of the usernames or user groups to include in the multiple-selection list.

• DefaultValue is the default value for the column. This parameter is valid for the following data types:

  • boolean, and the value can be either TRUE (selected) or FALSE (cleared)
  • dimension, and the value can be any dimension name within the system.

  For boolean columns, Longview recommends that you specify a default for NULLABLE boolean columns, otherwise, the value for new rows will be NULL, which may cause unpredictable behavior.

Example:

UserFilter

Use this DataTable function to specify a subset of users from the App table to include in the DataTable object. This function does not apply to virtual DataTables and applies only to fields with the user or userlist data type. If you do not specify a subset of users, all users from the App table are included in the DataTable object.

Syntax:

where:

• ColumnName is the column in the App table from which to filter the users. This column must have a data type of user or userlist.
• UserName is the name of the user subset to include in the DataTable object. Separate multiple usernames with a pipe ( | ). You can also filter for instances where the column is blank by using the NULL keyword.
• Group is the name of the group subset to include in the DataTable object. Separate multiple groups with a pipe ( | ).
• AttributeFilters is optional and can be up to two attribute filters linked by AND or OR, enclosed in double quotation marks, using the syntax:
  {AttrName{Operation{Expression

  where:

  AttrName	is the name of the attribute for which to filter.
  Operation	is one of the following: EQ — Exactly equal to. NE — Not equal to.
  Expression	is a character string. If the expression contains spaces, enclose the expression in double quotation marks preceded by a backslash (\"expression with spaces\"). If the expression is a list, separate multiple items with a pipe ( | ). For Non-List Attributes, the filter behaves as follows: Attribute EQ Expression — Matches only if the attribute is an exact match of the expression. Attribute NE Expression — Matches if the attribute is not an exact match of the expression. For List Attributes, the filter behaves as follows: Attribute EQ Expression — Matches if the attribute is an exact match of the expression, or is a list of values, any one of which exactly matches the expression. Attribute NE Expression — Matches if the attribute is empty or a list of values, none of which exactly matches the expression.

Example:

Where

Use this DataTable function to define a subset of data to include in the DataTable object using conditions supported by the underlying database. This function does not apply to virtual DataTables. If you do not specify a subset of data, all data from the App table is included in the DataTable object.

Note: You must use the SymbolFilter or UserFilter functions to create a subset of data for symbols and users. For more information, see SymbolFilter and UserFilter.

Syntax:

where:

• ConditionStatement can be condition statements linked by AND or OR, enclosed in double quotation marks, using the following syntax:

  ColumnName Operator Value

  where:

  • ColumnName is the name of the column to filter on.
  • Operator is one of the following:

  Operator	Description	Additional Syntax
  =	Equal to
  <>	Not equal to
  >	Greater than
  <	Less Than
  >=	Greater than or equal to
  <=	Less than or equal to
  BETWEEN	Specifies numeric values for a column within a specified range.	"Column BETWEEN x AND y"
  LIKE	Specifies a search condition that must match a string of characters and wildcards (represented by percent signs).	"Column LIKE (%Value%)"
  IN	Specifies multiple possible values for a column.	"Column IN ('Value1', 'Value2', 'Value3')"

• Value is the value for the condition. If you specify a column with the date data type, the Value syntax is based your database type:

  Database	Syntax
  Oracle	TO_DATE('DateValue,'DateFormat where: DateValue is the date to filter on. DateFormat is the format of the DateValue.
  SQL Server	'yyyy/mm/dd'

  If you use column names that are reserved keywords for either SQL or Oracle, WHERE statements must use quotes. These quotes must then be escaped. For example, Where "\"number\" > 500".

Example: