Lookup Table Collections

Navigation: Build Your Own ERP >

Lokup Table Collections

The Lookup Table Collections app contains the list of all lookup tables. A lookup table is usually used to create a dropdown or pop-up window showing records where you can easily search and select the item(s) from another table. It is recommended to add your lookup tables in this app, so you can attach them to one or more apps.

Step-by-step instructions:

1. Click Cemtral Menu.

2. Select Systems -> ADMIN SETUP

3. Double-click Transactions ->Lookup Table Collections v2. You will be redirected to Lookup Table Collections v2 tab window.

4.Add new new lookup table at the end of the grid.

Fill-up the columns properly as follows:

Column Name	Sample Value	Description
LookupID	111	Automatically generated. This is the primary key of the table.
TableName	vw_Customer	This is the table or view that the lookup table represents.
LookupDesc	Customer	Any descriptive note for the lookup table
LookupSelectText	Select ....	SQL Select Statement that will be evaluated and generated. The resultset will be shown when the lookup table is called.
DBLinkKey	T217806	Optional only. The DBLinkKey serves as an AppID to open a particular App. In this sample, the key T217806 represents the app for Customer Master File. When the DBLink button is click, the system will open the customer master file so you can easily update the file.
Status	A	A=Active; I=InActive; L=Locked which will avoid inadvertent changes.
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.

5. Add column format for the lookup table if you wish to overide the default column format. In this sample, we will accept the default format by leaving this grid blank.

ColumnFormat - Contains the list of format schema to be applied into a column. The system has predefined format schema based on the column type, if you wish to overide the default format, you may use this table to define your preferences.

Column Name	Sample Value	Description
ColumnName		One or more column names, separated by comma, where the formatting will be applied. The column name must be present in the corresponding Select Statement
Lock		If Checked, the column will be locked from editing
Hide		If Checked, the column will be hidden from the grid.
Required		If Checked, the value is required to be inputted for the column.
Encrypted		If Checked, the value will be encrypted when saved to the database.
ColumnCaption		Column header to be shown. The default caption is the field name in the Select Statement
ColumnHeadingAlignment		Column heading alignment as follows: 1. Left - default for numeric 2. Right - default for string 3. Center
HorizontalAlignment		Horizontal alignment as follows: 1. Left - default for numeric 2. Right - default for string 3. Center
VerticalAlignment		Vertical alignment as follows: 1. Center 2. Top 3. Bottom - default value
ColumnFooterText		Footer text of the column, if any. You can add a total value as footer for numeric fields using ColumnFormatEnum, see ColumnFormatEnum below.
NumberFormatEnum		Number format of the column. You can type or select from the dropdown list. For TIN you can type the format 000-000-000 and select Edit Mask under ColumnFormatEnum, see below. You can check the Vendors app as an example.
ColumnFormatEnum		Column style format as follows: 1. AutoIncrement - Column will be chronologically filled-up 2. AutoSumToColumnFooter - Sum value of the column as footer 3. ButtonAlways - A button icon will be placed in the column 4. ButtonText - Alsame as ButtonAlways 5. CheckBoxColumnHeader - A check box will be place at the column header 6. ColorDialog - A pop-up color dialog will be placed in the column 7. Edit Mask - Input mask used to edit cells. Ex: 000-000-000 for TIN 8. FetchCellStyle - Customize fonts and colors on a per-cell basis 9. FetchColumnStyle - Customize fonts and colors on a per-column basis 10. FetchControlStyle - Customize fonts and colors on a per-control basis 11. FetchFooterStyle - Customize fonts and colors on a per-footer basis 12. FetchRegexCellStyle - A Regex to be applied on a per control basis 13. FetchRowStyle - Customize fonts and colors on a per-row basis 14. FolderBrowserDialog - A Folder browser dialog pop-up window 15. FontDialog - A Font dialog pop-up window will be attached 16. OpenFileDialog - An Open file dialog pop-up window will be attached 17. PasswordChr - Character to hide input. Ex: *
FetchForeColor		Forecolor to be applied when a FetchStyle is attached. See ColumnFormatEnum above
FetchBackColor		Backcolor to be applied when a FetchStyle is attached. See ColumnFormatEnum above
RegexString		Regex to be evaluated when applying FetchStyle
FetchFontStyle		Font to be applied when a FetchStyle RegEx is true
FetchCellStyleLocked		If Checked, the row or cell will be locked for editing. See example: 1601C data entry where some cells are grayed and locked.
FetchCellStyleColumnName		The column where the fetch style regex is stored and evaluated. This is usually used for cells or rows that have different styles. See 1601C
CellMergeEnum		Merged style for cells with the same values as follows: 1. None - default value 2. Free - cells with same values will be merged 3. Restricted - adjacent cells with the same values will be merged
RoundOff		Round off the value of the column. Select from the dropdown list.
RemoveSpecialChar		List of characters you wish to remove when the data is saved. Use EdtiMask
Height		Height of the grid rows
Width		Width of the grid's columns
ColumnIndex		Index position of the column in the grid
CellTipHeaderText		Tool tip to be shown on the grid's header caption
CellTipColumnName		Tool tip for the grid's column
MinValue		Minimum value of the column that can be entered by the user, if any
MaxValue		Maximum value of the column that can be entered by the user, if any
Status		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		Automatically fill-up. Part of primary key and also a child key of the parent table, TableCollection
SeqNo		Automatically fill-up. Part of primay key and use also in sorting of records

7. Click Save when done.

After setting the lookup table here, you can now attach it into a column. See Attach Lookup Table into a Column for details.