|
|
Some data entry columns require that their values must be selected from a given list. This is where lookup table comes into play. After adding the desired lookup table into the Lookup Table Collections, you may attach it into a column.
The system support three ways of attaching a lookup table
1) Using ColumnValueItem that will show the value items associated to a column using a simple dropdown. This is used for few items only.
2) Using ColumnDropdown that will show dropdown list associated to a column similar to #1. The lookup values can be taken from the Lookup Table Collections.
3) Using ColumnLookupViewer that will show a pop-up datagrid viewer with search functionality. This is an advanced lookup table which is usually used for many items. This also supports selecting one or more items from the list. The lookup values can be taken from the Lookup Table Collections.
In this sample, we will use Vendors v3 Master File to examine the lookup tables attached to its columns.
Step-by-step instructions:
1. Open App Gallery
2. Goto the grid TableGroup and look for the app named Vendors v3.
3. Goto the grid TableCollections and select Vendor. In this sample there is only table anyway.
4. Click Lookup Table tab.
5. Attach lookup table for Regular and Vatable columns under the grid ColumnValueItem. These columns requires a Yes/No input only. The actual values is either Y for Yes or N for No. The actual values are the one saved into the database. Check ValidateEntry if you don't want other values to be inputted.
ColumnValueItem Data Definition
|
Column Name
|
Sample Value
|
Description
|
|
ColumnName
|
Regular
|
The name of the column where the value items will be attached
|
|
PresentationEnum
|
ComboBox
|
The style of presentation to be used as follows:
1. CheckBox
2. ComboBox - default value
3. RadioButton
4. SortedComboBox
5. Normal
|
|
ActualValues
|
Y,N
|
The list of actual values, separated by comman, which are going to be saved in the database instead of the display values
|
|
DisplayValues
|
Yes,No
|
The corresponding list of display values, separated by comma, which will be shown to the user
|
|
ValidateEntry
|
Checked
|
Check if you wish to validate user input. The input must be one of the given items.
|
|
Status
|
A
|
A=Active; I=InActive
|
|
Createdby, DateCreated, Modifiedby, DateModified
|
|
These columns are called audit trails. Their values are auto-generated. One who created or modified the app. It is assumed that the current user is the one who created or modified the record.
|
|
TableGUId
|
|
Hidden, part of primary key and also a child key of the parent table, TableCollection
|
|
SeqNo
|
|
Hidden, part of primay key and use also in sorting of records
|
6. Attach lookup table for VendorCatID column under the grid ColumnDropdown. Enter the column names and click LookupID dropdown and select from the list the corresponding lookup table
Below is the full screen of the ColumnDropdown.
7. Add lookup table for ZipCode, TIN and ATC columns under the grid ColumnLookupViewer. Enter the column names and click LookupID dropdown and select from the list the corresponding lookup table.
Below is the full screen of ColumnLookupViewer.
ColumnLookupViewer Data Definition
|
Column Name
|
Sample Value
|
Description
|
|
ColumnName
|
ZipCode
|
The name of the column where the lookup table will be applied
|
|
LookupID
|
120
|
The ID selected from the lookup table. You can update the list of Lookup Table under the Lookup Table Collections module
|
|
LookupSelectText
|
Select..
|
Associated SelectText for the selected LookupID above. The resultset of the SelectText will be used as the datasource for the lookup table.
|
|
SelectText
|
|
If you leave the above LookupID blank, you can create directly your Select Statement to serve as datasource. However, it is recommend to create the select statement using the Lookup Table Collections module and link the LookupID as described above.
|
|
GridFilter
WhereClause Expression
|
|
Grid filter expression to be evaluated and applied against the datasource everytime the Lookup Table is called
WhereClause Expression to be evaluated and replaced the @WhereCluase variable inside the SelectText above.
|
|
SourceColumns
|
|
The lookup table's column names, separated by comma, to be mapped against grid's table columns when filling up the table row. If blank, the system will match all columns with the same names when filling-up the table row.
|
|
TargetColumns
|
|
The grid table's column names, separated by comma, to be mapped against lookup table's column names when filling up the table row. If blank, the system will match all columns with the same names when filling-up the table row.
|
|
ValidateEntry
|
Unchecked
|
If Unchekced, the user can encode other values.
|
|
RetrieveAllRecords
|
Checked
|
Always Check the value. If Unchecked, the user must press Refresh to retrieve the records.
|
|
AllowMultipleSelection
|
Unchecked
|
This is obsolete already since you can now highlight multiple records to be inserted.
If Check, the lookup table mutiple-tab section will be enabled where the user can select multple records in the lookup table to be inserted into the target table
|
|
Status
|
A
|
A=Active; I=InActive
|
|
Createdby, DateCreated, Modifiedby, DateModified
|
|
These columns are called audit trails. Their values are auto-generated. One who created or modified the app. It is assumed that the current user is the one who created or modified the record.
|
|
TableGuid
|
|
Hidden. Part of primary key and also a child key of the parent table, TableCollection
|
|
SeqNo
|
|
Hidden. Part of primay key and use also in sorting of records
|
8. Click Save
|
|
