Skip to main content
insightsoftware Documentation insightsoftware Documentation
{%article.title%}
Published:
Was this article helpful?
0 out of 0 found this helpful

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:

Copy
DATATABLE AppTableName |VIRTUAL [,WRITABLE]                 

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.

Copy

Syntax

AddColumn DataType, "ColumnName", "Dimension:SymbolDimension""DimensionColumn:DimColumn" "Values:MyValues" "Default:BooleanDefault"

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).
Copy

Example

DATATABLE VIRTUAL
ADDCOLUMN SYMBOL, "Dimension:ENTITIES", "Values:USNorthEast###"
ADDCOLUMN USER, "UserName","Values:CChase|BSummers|WRosenberg|XHarris|RGiles"
ADDCOLUMN DATE, "StartDate" 
ADDCOLUMN BOOLEAN, "Contract" "Default:FALSE"
ADDCOLUMN STRING, "EMAIL" 
ADDCOLUMN DIMENSION, "MyDimensionColumn"
ADDCOLUMN SYMBOL, "MySymbolColumn", "DimensionColumn:MyDimensionColumn"

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.

Copy

Syntax

Columns ColumnName1[, ColumnName2]...        

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.

Copy

Example

Columns ID,EmployeeNumber,Name,Start_Date,EmployeeType,Salary, Wage,FullPartTime,Entity,Active,Location,Manager,Attachments,CreatedBy

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.

Copy

Syntax

OrderBy “ColumnName1 [ASC|DESC][, ColumnName2 ASC|DESC, …]”    

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.

Copy

Example

OrderBy “[Employee Type] ASC”
OrderBy “Revenue DESC”
OrderBy “HireDate DESC, LastName ASC”    

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.

Copy

Syntax

RowLimit numRows         

where:

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

Example

RowLimit 1000         

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.

Keyword

Column data types

Values

string

symbol

user

userlist

Min

Max

date

Default

boolean

AllowOverride

string

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.

Column data type

Keywords used

Resulting cell control

string

none

Text box

Values

Drop-down list

Values + AllowOverride:TRUE

Combo box

symbol

none

Drop-down list with all the symbols for the dimension or linked dimension column specified for the DATAVALUES column in the LV_APPTABLE_COLUMNS table.

Values

Drop-down list with the symbols as restricted by the symbol spec specified for the Values keyword.

Note: Specifying Values for symbol data type is only supported when specifying a dimension for the DATAVALUES

user

none

Drop-down list with all users in the system.

Values

Drop-down list with the users as restricted by the Values keyword.

userlist

Values

Multiple-selection list populated with users as restricted by the Values keyword.

Copy

Syntax

SetColumn "ColumnName", "Values:MyValues", “AllowOverride:TRUE|FALSE”, “Min:Date”, "Max:Date", "Default:DefaultValue"

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 typeSyntax

    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.
Copy

Example

SetColumn "EmployeeType", "Values:FT;PT"
SetColumn "EmployeeType", "Values:FT;PT|$FT$;$PT$"
SetColumn "EmployeeType", "Values:FT;PT|Full Time;Part Time"
SetColumn "Method", "Values:Check/Cheque|Electronic Funds Transfer", "AllowOverride:True"
SetColumn "Tax_Type", "Values:[[Symbol,AZTaskTaxTypes,{{COLUMN,Jurisdiction}}]]"
SetColumn "Processed_Date", "Min:2015-01-01", "Max:2015-01-31"

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.

Copy

Syntax

SymbolFilter ColumnName, "SymbolSpec1[|SymbolSpec2|…SymbolSpecN]" [,"attributefilters"]

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:
    Copy
    FilterType{AttdrName{Operation{Expression           

    where:

    FieldDescription

    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.
Copy

Example

SymbolFilter entityName, "E1000###"
SymbolFilter entityName, "E1000###", "(ALL{ZGPNativeCurrency{EQ{CAD)"
SymbolFilter entityName, "E1000###|G1000###", "(ALL{ZGPNativeCurrency{EQ{CAD) OR (ALL{ZGPNativeCurrency{EQ{USD)"

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.

Copy

Syntax

TempColumn DataType, "ColumnName", "Dimension:SymbolDimension", "DimensionColumn:DimColumn","Values:MyValues", "Default:BooleanDefault"

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.

Copy

Example

DATATABLE MyTable
TEMPCOLUMN NUMBER, "Percentage Increase"
TEMPCOLUMN STRING, "Favourite Color"
TEMPCOLUMN DIMENSION, "MyDimensionColumn"
TEMPCOLUMN SYMBOL, "MySymbolColumn", "DimensionColumn:MyDimensionColumn"

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.

Copy

Syntax

UserFilter ColumnName, "UserName[|UserName2|…UserNameN]", "GroupName1 [|GroupName2|…GroupNameN]" [,"AttributeFilters"]

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:
    Copy
    {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.
Copy

Example

UserFilter ownerName, "Cordelia", ""
UserFilter ownerName, "", "GRP2"
UserFilter ownerName, "Cordelia|NULL", ""
UserFilter ownerName, "Xander", "GRP2"
UserFilter ownerName, "Cordelia|Xander|NULL", "GRP2" ,"(AUPlanner{EQ{TRUE)"
UserFilter ownerName, "Xander", "GRP2" ,"(AUPlanner{EQ{TRUE) AND (AUInputUser{EQ{TRUE)"

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.

Copy

Syntax

Where "ConditionStatement [AND|OR ConditionStatement]"       

where:

  • ConditionStatement can be condition statements linked by AND or OR, enclosed in double quotation marks, using the following syntax:
    Copy
    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".

Copy

Example

Where "Year >= 2002 AND Year <=2012"
Where "Amount BETWEEN 150 AND 200"
Where "Title LIKE (%Manager%) OR Title LIKE (%Team Lead%)"
Where "Make IN ('Acura', 'Aston Martin', 'Chevrolet')"
Where "\"number\" > 500"
For Oracle: Where "StartDate > TO_DATE('2012/01/01','2012/01/01')"
For SQL: Where "StartDate > '2012/01/01'"
Published:

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:

Copy
DATATABLE AppTableName |VIRTUAL [,WRITABLE]                 

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.

Copy

Syntax

AddColumn DataType, "ColumnName", "Dimension:SymbolDimension""DimensionColumn:DimColumn" "Values:MyValues" "Default:BooleanDefault"

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).
Copy

Example

DATATABLE VIRTUAL
ADDCOLUMN SYMBOL, "Dimension:ENTITIES", "Values:USNorthEast###"
ADDCOLUMN USER, "UserName","Values:CChase|BSummers|WRosenberg|XHarris|RGiles"
ADDCOLUMN DATE, "StartDate" 
ADDCOLUMN BOOLEAN, "Contract" "Default:FALSE"
ADDCOLUMN STRING, "EMAIL" 
ADDCOLUMN DIMENSION, "MyDimensionColumn"
ADDCOLUMN SYMBOL, "MySymbolColumn", "DimensionColumn:MyDimensionColumn"

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.

Copy

Syntax

Columns ColumnName1[, ColumnName2]...        

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.

Copy

Example

Columns ID,EmployeeNumber,Name,Start_Date,EmployeeType,Salary, Wage,FullPartTime,Entity,Active,Location,Manager,Attachments,CreatedBy

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.

Copy

Syntax

OrderBy “ColumnName1 [ASC|DESC][, ColumnName2 ASC|DESC, …]”    

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.

Copy

Example

OrderBy “[Employee Type] ASC”
OrderBy “Revenue DESC”
OrderBy “HireDate DESC, LastName ASC”    

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.

Copy

Syntax

RowLimit numRows         

where:

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

Example

RowLimit 1000         

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.

Keyword

Column data types

Values

string

symbol

user

userlist

Min

Max

date

Default

boolean

AllowOverride

string

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.

Column data type

Keywords used

Resulting cell control

string

none

Text box

Values

Drop-down list

Values + AllowOverride:TRUE

Combo box

symbol

none

Drop-down list with all the symbols for the dimension or linked dimension column specified for the DATAVALUES column in the LV_APPTABLE_COLUMNS table.

Values

Drop-down list with the symbols as restricted by the symbol spec specified for the Values keyword.

Note: Specifying Values for symbol data type is only supported when specifying a dimension for the DATAVALUES

user

none

Drop-down list with all users in the system.

Values

Drop-down list with the users as restricted by the Values keyword.

userlist

Values

Multiple-selection list populated with users as restricted by the Values keyword.

Copy

Syntax

SetColumn "ColumnName", "Values:MyValues", “AllowOverride:TRUE|FALSE”, “Min:Date”, "Max:Date", "Default:DefaultValue"

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 typeSyntax

    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.
Copy

Example

SetColumn "EmployeeType", "Values:FT;PT"
SetColumn "EmployeeType", "Values:FT;PT|$FT$;$PT$"
SetColumn "EmployeeType", "Values:FT;PT|Full Time;Part Time"
SetColumn "Method", "Values:Check/Cheque|Electronic Funds Transfer", "AllowOverride:True"
SetColumn "Tax_Type", "Values:[[Symbol,AZTaskTaxTypes,{{COLUMN,Jurisdiction}}]]"
SetColumn "Processed_Date", "Min:2015-01-01", "Max:2015-01-31"

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.

Copy

Syntax

SymbolFilter ColumnName, "SymbolSpec1[|SymbolSpec2|…SymbolSpecN]" [,"attributefilters"]

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:
    Copy
    FilterType{AttdrName{Operation{Expression           

    where:

    FieldDescription

    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.
Copy

Example

SymbolFilter entityName, "E1000###"
SymbolFilter entityName, "E1000###", "(ALL{ZGPNativeCurrency{EQ{CAD)"
SymbolFilter entityName, "E1000###|G1000###", "(ALL{ZGPNativeCurrency{EQ{CAD) OR (ALL{ZGPNativeCurrency{EQ{USD)"

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.

Copy

Syntax

TempColumn DataType, "ColumnName", "Dimension:SymbolDimension", "DimensionColumn:DimColumn","Values:MyValues", "Default:BooleanDefault"

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.

Copy

Example

DATATABLE MyTable
TEMPCOLUMN NUMBER, "Percentage Increase"
TEMPCOLUMN STRING, "Favourite Color"
TEMPCOLUMN DIMENSION, "MyDimensionColumn"
TEMPCOLUMN SYMBOL, "MySymbolColumn", "DimensionColumn:MyDimensionColumn"

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.

Copy

Syntax

UserFilter ColumnName, "UserName[|UserName2|…UserNameN]", "GroupName1 [|GroupName2|…GroupNameN]" [,"AttributeFilters"]

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:
    Copy
    {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.
Copy

Example

UserFilter ownerName, "Cordelia", ""
UserFilter ownerName, "", "GRP2"
UserFilter ownerName, "Cordelia|NULL", ""
UserFilter ownerName, "Xander", "GRP2"
UserFilter ownerName, "Cordelia|Xander|NULL", "GRP2" ,"(AUPlanner{EQ{TRUE)"
UserFilter ownerName, "Xander", "GRP2" ,"(AUPlanner{EQ{TRUE) AND (AUInputUser{EQ{TRUE)"

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.

Copy

Syntax

Where "ConditionStatement [AND|OR ConditionStatement]"       

where:

  • ConditionStatement can be condition statements linked by AND or OR, enclosed in double quotation marks, using the following syntax:
    Copy
    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".

Copy

Example

Where "Year >= 2002 AND Year <=2012"
Where "Amount BETWEEN 150 AND 200"
Where "Title LIKE (%Manager%) OR Title LIKE (%Team Lead%)"
Where "Make IN ('Acura', 'Aston Martin', 'Chevrolet')"
Where "\"number\" > 500"
For Oracle: Where "StartDate > TO_DATE('2012/01/01','2012/01/01')"
For SQL: Where "StartDate > '2012/01/01'"
For an optimal Community experience, Please view on Desktop