Creating data table input apps

You can use Longview Designer to create data table input apps.

Specifying settings

You can use Longview Designer to specify global settings for a data table input app.

Specifying pre-selection commands

Use pre-selection to perform any commands before the selection form is displayed. The typical use of pre-selection is to perform advanced logic to determine default selections or other configuration related to the selection form.

Specifying user selections

Selection is used to capture any input from the user prior to retrieving data and displaying the view. This could be used for flittering options related to the data or view formatting options. Selection is a form and supports all keywords related to the form document.

For more information on designing forms, see the Longview Developer’s Guide.

Specifying post-selection commands

Use post-selection to perform any commands after the selection form is displayed. The typical use of post-selection is to determine any additional settings that are calculated based on the user’s selections.

Specifying the data table definition

Use Data Table Definitions to specify the data tables to be used in the data input view. For each data table definition to be created, right-click on Data Table Definitions and select New Document.

Fill in the data table definition sections:

1. For the Name, enter the name of your Data Table

   Note: The name must 29 or fewer characters and only contain alphanumeric characters and underscore “_”. The actual name of the data table in application framework will be “dt” + Name, so if you need to refer to the Main data table in application framework, use dtMain as the name.

2. In the Options table, complete the following fields

   Field	Notes
   Create this data table	Select this option if you would like the data table to be created prior to the view. Note: In some complex cases you may want to define the data table, but only created when required during, save, validate or some other processing.
   Download this data table	Select this option if you would like to download data in this data table. Note: If you have more than one data table set to download, the last on in the list will be displayed when the layout type is Single.
   Submit from this data table	Select this option if you would like to submit from this data table to the database once the submit button is clicked within the view. Note: You cannot set a data table to submit without enabling the “Download this data table” option as well. At least one data table definition in your data table input app must be set to submit
   Import spec to use for importing to this table	If the user will be allowed to import data into this table, select the import spec that will be used to process the import. The selections available are determined by the contents of the Import Specs folder.
   Export spec to use for exporting from this table	If the user will be allowed to export data from this table, select the export spec that will be used to process the export. The selections available are determined by the contents of the Export Specs folder.

3. In the columns section the table and columns are defined.
• For the table name, select the table to use. The list includes all app tables defined in the system.
• If you pick a table based on a view it will display an information icon indicating that the table will be read-only.
• After selecting a table, the columns in the table will be displayed. All columns will be selected by default. Also, the columns that define the primary key will be indicated with a key icon. If some or all the primary key columns are not included, the table will be read only.
• You may add or remove temporary columns using the Add Column and Remove Column buttons.

  Note: Temporary columns are added to the table for use with an app but are not submitted to the database.

• For each column complete the following fields:

  Field	Notes
  Include	Check each column to be included in the query. Use the check box in the header to select all columns.
  Column	For persistent table columns, displays the name of the column. For temporary columns, enter the name of the column.
  Nullable	Indicates whether the column can be saved with no value specified. If any non-nullable columns are excluded the table will be read-only.
  Type	For persistent table columns, displays the data type of the column.For temporary columns, select the data type for the column. If you select Symbol[Dimension], you will be prompted to select the dimension that applies. If you select Symbol[DimensionColumnName], you will be prompted to select a column (of type Dimension).
  Values (String)	For String columns allows you to optionally define a list of values the user can select from. Enter a | delimited list of values. Optionally enter a | delimited list of display values by placing a semi-colon after the list of values. Note: You can also use variables to define the list of values and display values.
  Values (Symbol)	For Symbol columns allows you to limit the symbols available. Enter a | delimited list of symbol specifications. You can also use a form to select the symbol specifications by clicking the search icon. Note: You can also use a variable to define the list of symbol specifications.
  Values (User)	For User columns, allows you to limit the users available for selection. Enter (or select) a | delimited list of user and group names. Note: You can also use a variable to define the list of users and groups.
  Values (User List)	For UserList columns, allows you to limit the users available for selection. Enter (or select) a | delimited list of user and group names. Note: You can also use a variable to define the list of users and groups.
  Allow Override	For String columns with Values defined, indicates whether the user can enter a value that is not in the list of values.
  Min / Max (Date)	For date columns, allows you to set a valid range of selectable dates. Note: The interface only supports explicit dates. To specify the valid date range using variables use the Additional Configuration section to define the date range using the SetColumn function.
  Default (Boolean)	For Boolean columns, allows you to indicate if the default value for the column is checked.
  Default (Dimension)	For Dimension columns, allows you to set he default dimension to be selected when a new row is added. Default is no default selection.

4. Use additional configuration to:
   • Define sort order.
   • Define any filters (SymbolFilter, UserFilter, or Where).

Specifying the data table view

Use Table Views to specify the views to be used in the data table input app. For each table view to be created, right-click on Table Views and select New Document.

Fill in the table view sections:

1. For the Columns, select the Data Table Definition that will be used with the view. The columns selected in the Data Table Definition appear in the table. For each column complete the following fields:

   Field	Notes
   Include	Check each column to be included in the query. Use the check box in the header to select all columns.
   Column	Displays the name of the column.
   Type	Displays the data type of the column.
   Description	Allows you to enter a description for the column to be displayed in the view.
   Width	Allows you to specify the width of the column in the view.
   Protect	Check to protect the column from input.
   Decimals (Number)	Allows you to specify the number of decimals displayed for numeric values.
   Enabled Criteria	Allows you to specify criteria which allows the user to modify the value of the column in each row of the table.

2. In the Parameters table, complete the following fields:

   Field	Notes
   Default number of decimals for numeric values	Specifies the number of decimals to display for numeric numbers (0-9). Note: The number of decimals for a specific column can be overridden using the ColumnDecimals function in the View Column Settings section.
   Default column width	Specifies the width of each column in the view. Note: The width for a specific column can be overridden using the ColumnWidth function in the View Column Settings section.
   Apply users’ layout preferences	Select Yes to allow the user’s modifications to the view to be saved and applied when the app is accessed. Select No to lock the settings of the initial view each time the app is accessed. Note: The user can modify the view while using the app, but the next time it is executed the view will revert to the one saved in the app.
   Use parentheses for negative numbers	Select Yes to display negative numbers with parentheses. Select No to display negative numbers with a negative sign.
   Use thousands separator for numbers	Select Yes to display a locale-specific thousands separator based on the operating system setting. Select No to not display a thousand’s separator.
   Left title text	Enter text to display in the left title area of the table view. This is optional and can be left blank.

3. In the Table Controls table, complete the following fields:

   Field	Notes
   Add	Check this to allow the user to add new rows to the table.
   Delete	Check this to allow the user to delete rows from the table.
   Duplicate	Check this to allow the user to add new rows to the table by duplicating an existing row.
   Reset	Check this to allow the user to reset a row’s values in the case of invalid input.
   ResetAll	Check this to allow the user to reset all row values in the case of invalid input.

4. In the Values pane Options, complete the following fields:

   Field	Notes
   Enable values pane	Select whether the user will be able to open the values pane within the view. Default is Yes.
   Values pane title	Enter text to appear in the title area of the values pane. Default is Row Details
   Hide values pane initially	Select whether the values panel in initially hidden, if enabled. Default is Yes.

5. In the Import/Export Options table, complete the following fields:

   Field	Notes
   Allow use of import	Select Yes to allow the user to import data from a file. The file to import must be a text file containing comma-separated values and a header row. The user will be able to clear all data in the view prior to import.
   Allow use of export	Select Yes to allow the user to export data to a file. The file exported will contain a header row and comma-separated values.

6. Use Additional Configuration to further configure the view. Additional Configuration is a code editor that allows you to enter view functions to be included in the configuration of the view.
   For more information on the code editor, see "Using the code editor".
   Typical use of View Column Settings in this app would be to:
   • Sort the rows in the view by one or more columns using the SortOrder function.
   • Adding summary rows to the view using the SummaryRow function.
   • Adding a custom editor for a column using the EditProcedure function.

Specifying view actions

Use View Actions to specify custom toolbar and context menu actions with a view. For each action to be created, right-click on View Actions and select New Document.

For each action complete the following fields:

Specifying dynamic actions

Use Dynamic Actions to specify actions that execute as the data is changed by the user within the view. For each action to be created, right-click on Dynamic Actions and select New Document.

For each action complete the following fields:

Specifying edit actions

Use Edit Actions to create a procedure to invoke a custom editor for a column in a data table. Edit actions are used when the value stored in a data table cell is more complex than a simple input. Example: a cell contains the definition of a cell or data area. It could also be used to provide a form used to calculate a value for input rather than requiring a user to manually enter a value that may be based on a calculation.

Specifying post-refresh commands

Use Post-Refresh to perform any commands after the data tables are downloaded but before any refresh calculations are performed. Use Post-Refresh to perform any processing that requires data to be downloaded into the data tables.

Specifying tabs

Use Tabs to add tabs to the view. Each tab can display a different table view. For each Tab to be created, right-click on Tabs and select Add Tab.

Note: Tabs are only required if using a Tabbed Layout Type. For more information, see "Specifying settings".

For each tab complete the following fields:

Specifying a tab action

Use tab commands to define commands and functions to be executed when switching between tabs in a tabbed view.

For more information on the code editor, see "Using the code editor".

Specifying an import spec

Use import specs to define the specification for importing data to a specific data table. For each import spec to be created, right-click on Import Specs and select New Document.

Fill in the import spec sections:

1. For the Name, enter the name of your Import Spec.
2. Fill in the Import Settings. Many of the import settings are pre-defined so the main requirement for the import spec is to:
   1. Define the fields in the source file, using Set functions.
   2. Define the relationship between fields in the source file and columns in the data table, using the Define function.
   3. Setting the maximum number of errors allowed during import, using the MaxErrors function.
   4. Defining any filter conditions on the records of the source file, using the Search function.

Specifying an export spec

Use export specs to define the specification for exporting data from a specific data table. For each export spec to be created, right-click on Export Specs and select New Document.

Fill in the export spec sections:

1. For the Name, enter the name of your Export Spec.
2. For Options, fill in the text to create the header row of the target file. The header row allows you to provide context to the values appearing in the file when it is exported.
3. Many of the export settings are pre-defined so the main requirement for the export spec is to:
   1. Define the fields in the target file, using Set functions.
   2. Define the relationship between columns in the data table and fields in the target file, using the Define function.

Specifying calculations

Use Calculations to define calculations to be executed against one or more data tables in the app. A calculation is a series of commands executed to perform calculations for one or more columns in a data table. In general, a calculation will:

1. Create a STRING[] variable to hold each row as it is retrieved.
2. Optionally create one or more NUM variables to hold the index of the columns to be tested. This allows for validations to continue to work even if the order of the columns in the table changes.
3. Use the Open CURSOR command to initiate row processing.
4. Use the Fetch command to retrieve rows from the data table.
5. Use the While command to loop through the rows.

   Note: You can add a template row processor including the above functions using the Open_Cursor_Template_RowProcessor syntax template available in the code editor. For more information on syntax templates, see "Using syntax templates".

6. Use the SetDataTableCell function to set the value for a specific cell in the current row being processed.

Fill in the row processor sections:

1. For the Name, enter the name of your Row Processor.
2. In the Run Processor table, complete the following fields:

   Field	Notes
   Run processor when data is refreshed	Check this to cause the row processor to execute when data is downloaded initially and whenever the user clicks Refresh.
   Run processor after user imports data	Check this to cause the row processor to execute after the user imports data via the import tool. Note: Allow use of import must be enabled.
   Run processor before data is submitted	Check this to cause the row processor to execute when the user clicks Submit, but before the data is uploaded.
   Run processor after data is submitted	Check this to cause the processor to execute when the user clicks Submit, but after the data is uploaded. This is mostly useful when the processor before submitting changes the data in ways that are confusing to a user remaining in the app, for example clearing data that should not be submitted.

3. Specify the commands and functions to execute in the Processor. Processor is a code editor that allows you to enter procedure commands and functions to execute for row processing. For more information on the code editor, see "Using the code editor".

Example: 

Create VARIABLE colPriceIdx AS NUM

Create VARIABLE colRevenueIdx AS NUM

Create VARIABLE colUnitIdx AS NUM

Create VARIABLE revenue AS NUM

Create VARIABLE row[] AS STRING

Create VARIABLE status AS NUM

Set VARIABLE colPriceIdx = GetDataTableColumnIndex(dtMain, "PRICE")

Set VARIABLE colUnitIdx = GetDataTableColumnIndex(dtMain, "UNITS")

Open CURSOR cursor SELECT "*" FROM dtMain

Fetch FIRST DATATABLEROW FROM cursor INTO row

While $LVS_FETCHSTATUS$ != -1

    If $LVS_FETCHSTATUS$ == 0

        //Insert data table row processing here

        If "$row[$colPriceIdx$]$" != "NULL" AND "$row[$colUnitIdx$]$" != "NULL"

            Set VARIABLE revenue = $row[$colPriceIdx$]$ * $row[$colUnitIdx$]$

            Set VARIABLE status = SetDataTableCell(dtMain, $LVS_FETCHINDEX$, $colRevenueIdx$, $revenue$)

        END If

    END If

    Fetch NEXT DATATABLEROW FROM cursor INTO row

END While

Close CURSOR cursor

Specifying data validations

Use Validations to specify data validations to perform when the user clicks the submit icon. Validation failures can be specified as errors or warnings. Errors will prevent the user from submitting data, while warnings will inform the user of validation failures, but still allow the user to proceed with submitting data.

Note: For the Data Table Input app data there is a single validation procedure for all table views. Be sure to reference the correct data table name in any validation functions.

In many ways, validations for data table input apps are like calculations. Like calculations, validations will require processing each row to check for any validation errors or warnings.

To perform data table validation:

1. Create a STRING[] variable to hold each row as it is retrieved.
2. Optionally create one or more NUM variables to hold the index of the columns to be tested. This allows for validations to continue to work even if the order of the columns in the table changes.
3. Use the Open CURSOR command to initiate row processing.
4. Use the Fetch command to retrieve rows from the data table.
5. Use the While command to loop through the rows.

   Note: You can add a template row processor including the above functions using the Open_Cursor_Template_RowProcessor syntax template available in the code editor. For more information on syntax templates, see "Using syntax templates".

6. Repeat for each validation.
   1. Write a test or tests to execute a specific validation.
   2. If a test fails, set the VALD_Result variable and run procedure VALD\AddResult.lvpro to add the validation failure result to the list of validation failures.

Example: 

Create VARIABLE colNameIdx AS NUM

Create VARIABLE row[] AS STRING

Set VARIABLE colNameIdx = GetDataTableColumnIndex(dtMain, "NAME")

Open CURSOR cursor SELECT "*" FROM dtMain

Fetch FIRST DATATABLEROW FROM cursor INTO row

While $LVS_FETCHSTATUS$ != -1

    If $LVS_FETCHSTATUS$ == 0

        //Insert data table row processing here

        If "$row[$colNameIdx$]$" == "NULL"

            Set VARIABLE VALD_Result = "ERROR|$LVS_FETCHINDEX$|Name must be specified"

            Run PROCEDURE "VALD\AddResult.lvpro"

        END If

    END If

    Fetch NEXT DATATABLEROW FROM cursor INTO row

END While

Close CURSOR cursor

Setting variable VALD_Result

The VALD_Result variable is a string list variable, set as follows (separate each item with a pipe character “|”):

1. ERROR or WARNING to specify the validation failure level.
2. An identifier for the row, usually the primary key.
3. A description of the issue that caused the test to fail.

Specifying forms

Use Forms to capture user input:

1. In an action within an input template
2. During post-selection in an app

For each form complete the following fields: