Lists of Values

Lists of values are assign to fields and are used to retrieve values that already exist in the database for the purpose of changing the values in a new or existing database record. Lists of values are used for the following functions in Xephr®.

Assignation to text and text area display fields.
Option List display fields
Obtaining information from the database when tabbing out of a field, such as item description when leaving the item field.

Creating Query Lists of Values

In the Explorer Tree, left click on the List of Values Information object to select it.
When creating a new list of values, the following options are available..

Right-click on the top level List of Values Information object and left click on the New Lov option.
Left-click on the [New List of Values] button in the Main Toolbar.
Left-click on the File Menu. Left-click on the New option. Left-click on the List of Values option.
Press Alt+L on the keyboard.

The Create LOV screen will be displayed.
In the Name property, enter the name for the new list of values. This name should be specific to the information that the list of values will display, to make it easier to find and use.
In the Description property, enter a user-defined description for the list of values.
In the Title property, enter the title for the list of values. The title will be displayed at the top of the list of values when it is activated.
In the Datasource property, select the desired datasource or (from parent).

Selecting (from parent) means that the datasource will be the one assigned to the block for the field to which the list of values is assigned.
In the Values From pop-list, select Query. A datasource specific select statement will be entered in the Query property. This statement will be executed against the datasource to retrieve the values for the list.
In the Query property, enter the query that will be executed. The format is as follows:

SQL*Plus queries are entered as follows:
SELECT (value), (additional value) FROM (table) WHERE (clause) ORDER BY (clause).
The first column (value) and the second column (additional value) are differentiated by the comma.
The value is the database value that will be obtained from the database and inserted into the field when it is selected. The value is also displayed in the list of values, but not in option lists.
The additional value is an additional value that will be displayed in the list of values. For option lists, this is the value that is displayed for selection in the option list.
The table is the database table against which the statement will be executed to retrieve the values.
The where clause restricts the information displayed in the list of values.
The order by clause indicates the order in which the data will be sorted and displayed.
Global values and block.field references can be used in lists of values to further restrict them. In addition, a 'generic' reference can be used in lists of values that defines the field, but not the block. These values are not placed in quotes when entered in the query.
Global Value: $$ORG_UNIT_ID$$
block.field: $$CUSTMAST.CUST_NO$$
generic reference: $$.CUST_NO$$
The generic field reference allows you to create lists of values that can be used on multiple entities that may have the same field names but not the same block names. Such as, item_master,item_no and oedetl.item_no. Both blocks could use a list of values with the reference to $$.ITEM_NO$$. When using the generic field reference, the list of values creation looks for the defined field within the block's hierarchy to find the field. The search begins in the current block, and if the field is not found, continues to the parent block, and it's parent block, and so on. Blocks that are not within the hierarchy are not considered.

An example of a query list of values would be:

SELECT item_no, item_no FROM item_master WHERE stock_um = 'EA' ORDER BY 1
This list of values displays all of the item numbers in the item master table with a stock unit of measure of EA twice, and the list is ordered by item number. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the item number is displayed in the option list for selection, and the item number is entered into the field when a value is selected.

SELECT item_no, description FROM item_master WHERE stock_um = 'EA' ORDER BY description
This list of values displays all of the item numbers and item descriptions in the item master table with a stock unit of measure of EA, and the list is ordered by item description. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the item description is displayed in the option list for selection, and the item number is entered into the field when a value is selected.

SELECT item_no, item_no||' - '||description FROM item_master WHERE stock_um = 'EA' ORDER BY description
This list of values displays all item numbers and a combination of the item number and descriptions (in the format item - description) in the item master table with a stock unit of measure of EA. The list is ordered by item description. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the combination of the item number and description in the format item - description is displayed in the option list for selection and the item number is entered into the field when a value is selected.

SELECT item_no, item_no||' - '||description FROM item_master WHERE stock_um = $$STOCK_UM$$ ORDER BY description
This list of values displays all item numbers and a combination of the item number and descriptions (in the format item - description) in the item master table with a stock unit of measure is equal to the global value $$STOCK_UM$$ (set before the list of values was generated). The list is ordered by item description. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the combination of the item number and description in the format item - description is displayed in the option list for selection and the item number is entered into the field when a value is selected.

SELECT item_no, item_no||' - '||description FROM item_master WHERE stock_um = $$ITEM_TRANS_LOG.STOCK_UM$$ ORDER BY description
This list of values displays all item numbers and a combination of the item number and descriptions (in the format item - description) in the item master table with a stock unit of measure is equal to the value in the item_trans_log block in the stock_um field. The list is ordered by item description. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the combination of the item number and description in the format item - description is displayed in the option list for selection and the item number is entered into the field when a value is selected.

SELECT item_no, item_no||' - '||description FROM item_master WHERE stock_um = $$.STOCK_UM$$ ORDER BY description
This list of values displays all item numbers and a combination of the item number and descriptions (in the format item - description) in the item master table with a stock unit of measure is equal to the value in the stock_um field that is located somewhere within the block hierarchy for the block in which the list of values was assigned to a field. The list is ordered by item description. When a value is selected, the item number will be inserted into the field on the entity. For an option list, the combination of the item number and description in the format item - description is displayed in the option list for selection and the item number is entered into the field when a value is selected.

Click on the Ok button at the bottom of the screen after the query has been entered.
The list of values will be added to the Explorer Tree.
Click on the [Save] button in the main toolbar to save the new list of values.

Modifying Query Lists of Values

In the Explorer Tree, expand the List of Values Information object. Left clock on the desired list of values to select it.
The Property Sheet will be displayed for the new list of values. These will either be displayed in Alphabetical or Logically, depending on the setting of the [Sort Alphabetically/Logically] button. The following instructions are assuming that the properties are sorted logically.
In the Name property, the name entered during list of values creation is displayed and can be changed. This name should be specific to the information that the list of values will display, to make it easier to find and use.
In the Description property, the description entered during list of values creation is displayed and can be changed.
In the Title property, enter the title for the list of values. The title will be displayed at the top of the list of values when it is activated.
In the Values From pop-list, Query is selected. A datasource specific select statement is entered in the Query property. This statement will be executed against the datasource to retrieve the values for the list.
In the Query property, enter the query that will be executed. The format is as follows:

SQL*Plus queries are entered as follows:
SELECT (value), (additional value) FROM (table) WHERE (clause) ORDER BY (clause).
The first column (value) and the second column (additional value) are differentiated by the comma.
The value is the database value that will be obtained from the database and inserted into the field when it is selected. The value is also displayed in the list of values, but not in option lists.
The additional value is an additional value that will be displayed in the list of values. For option lists, this is the value that is displayed for selection in the option list.
The table is the database table against which the statement will be executed to retrieve the values.
The where clause restricts the information displayed in the list of values.
The order by clause indicates the order in which the data will be sorted and displayed.
Global values and block.field references can be used in lists of values to further restrict them. In addition, a 'generic' reference can be used in lists of values that defines the field, but not the block. These values are not placed in quotes when entered in the query.
Global Value: $$ORG_UNIT_ID$$
block.field: $$CUSTMAST.CUST_NO$$
generic reference: $$.CUST_NO$$
The generic field reference allows you to create lists of values that can be used on multiple entities that may have the same field names but not the same block names. Such as, item_master,item_no and oedetl.item_no. Both blocks could use a list of values with the reference to $$.ITEM_NO$$. When using the generic field reference, the list of values creation looks for the defined field within the block's hierarchy to find the field. The search begins in the current block, and if the field is not found, continues to the parent block, and it's parent block, and so on. Blocks that are not within the hierarchy are not considered.

An example of a query list of values would be:

In the Datasource property, select the desired datasource or (from parent).

Selecting (from parent) means that the datasource will be the one assigned to the block for the field to which the list of values is assigned.
In the Width property, enter the width of the list of values in pixels when it is opened from a field. The default is obtained from the repository configuration.
In the Height property, enter the height of the list of values in pixels when it is opened from a field. The default is obtained from the repository configuration.
In the Rows Per Page property, enter the number of rows per page of the list of values. The default is obtained from the repository configuration.
In the Created On property, the date and time on which this list of values was created is displayed and cannot be changed.
In the Modified On property, the date and time on which this list of values was last changed is displayed and cannot be modified.
When all changes have been made, press the [Save] button in the Main Toolbar.

Creating Static List Lists of Values

In the Explorer Tree, left click on the List of Values Information object to select it.
When creating a new list of values, the following options are available..

Right-click on the top level List of Values Information object and left click on the New Lov option.
Left-click on the [New List of Values] button in the Main Toolbar.
Left-click on the File Menu. Left-click on the New option. Left-click on the List of Values option.
Press Alt+L on the keyboard.

The Create LOV screen will be displayed.
In the Name property, enter the name for the new list of values. This name should be specific to the information that the list of values will display, to make it easier to find and use.
In the Description property, enter a user-defined description for the list of values.
In the Title property, enter the title for the list of values. The title will be displayed at the top of the list of values when it is activated.
In the Datasource property, select the desired datasource or (from parent).

Selecting (from parent) means that the datasource will be the one assigned to the block for the field to which the list of values is assigned.
In the Values From pop-list, select List. The description displayed and its database value will be defined in the lower part of the screen.

In static list lists of values, the values are displayed in the order entered here.
In the Name field, enter the datasource value that will be entered in the field when the name is selected.
In the Value field, enter the description to be displayed in the list.
An example of the entries might be:

Name	Value
Q	Quotation
O	Open Order
J	Partially Shipped
S	Shipped Complete
V	Invoiced

Name	Value
	Please Select Status
A	Active
I	Inactive

When all entries have been made, press the Ok button at the bottom of the screen.
The list of values will be added to the Explorer Tree.
Click on the [Save] button in the main toolbar to save the new list of values.
The list entries will be stored in the Query field, separated by semi-colons.

Q=Quotation;O=Open Order;J=Partially Shipped;S=Shipped Complete;V=Invoiced

=Please Select Status;A=Active;I=Inactive

Modifying Static List Lists of Values

In the Explorer Tree, expand the List of Values Information object. Left clock on the desired list of values to select it.
The Property Sheet will be displayed for the new list of values. These will either be displayed in Alphabetical or Logically, depending on the setting of the [Sort Alphabetically/Logically] button. The following instructions are assuming that the properties are sorted logically.
In the Name property, the name entered during list of values creation is displayed and can be changed. This name should be specific to the information that the list of values will display, to make it easier to find and use.
In the Description property, the description entered during list of values creation is displayed and can be changed.
In the Title property, enter the title for the list of values. The title will be displayed at the top of the list of values when it is activated.
In the Values From pop-list, List is selected. The values entered during list of values creation are displayed in list format in the Query property.
In the Query property, the values entered during list of values creation are displayed in list format. The format is name=value;
For example:

Q=Quotation;O=Open Order;J=Partially Shipped;S=Shipped Complete;V=Invoiced

=Please Select Status;A=Active;I=Inactive
In static list lists of values, the values are displayed in the order entered. To add a new entry to the list, find the location in the list in which the new entry should be displayed and enter it as name=value;.

For example:
Q=Quotation;I=Incomplete;O=Open Order;J=Partially Shipped;S=Shipped Complete;V=Invoiced

=Please Select Status;A=Active;I=Inactive;H=Hold
In the Width property, enter the width of the list of values in pixels when it is opened from a field.
In the Height property, enter the height of the list of values in pixels when it is opened from a field.
In the Rows Per Page property, enter the number of rows per page of the list of values.
In the Created On property, the date and time on which this list of values was created is displayed and cannot be changed.
In the Modified On property, the date and time on which this list of values was last changed is displayed and cannot be modified.
When all changes have been made, press the [Save] button in the Main Toolbar.

Testing Lists of Values

In the Explorer Tree, expand the List of Values Information object and find the list of values that you wish to test.
On the Main Toolbar, press the [Preview] button.
If the list of values contains any global variables, a screen will be displayed so that you can enter the values for those variables. Enter the valuesand press [Ok].

The list of values will be displayed. If any errors are generated by the list, they will be displayed in the list of values.

Auto-Populating Lists of Values

Query type lists of values can be used to populate the data in other fields in the block. The first column remains the code that will be returned to the field, and the second column remains the description displayed in the list of values. However, you can add additional columns to the select statement to return additional values. Each additional column that is returned must be unique and must match the field into which the additional value will be inserted. Aliases can be used to guarantee the match, and to insert expressions into the field.

When the list of values is run and the user selects the value, the code defined by the first column in the list of values is returned to the field from which the list of values was run. All additional fields that are selected with by the query in the list of values will be inserted into the appropriate fields as well.

When you enter a value manually in a list of values field, and tab out of that field, the additional fields will also be populated from the list of values.

Auto-populating lists of values will not auto-populate fields on query (QBE) screens.

Auto-populating queries are entered as follows:

SELECT (value), (additional value), (additional value 2) FIELD_NAME, (additional value 3) FIELD_NAME2 FROM (table) WHERE (clause) ORDER BY (clause).
Database columns are separated by commas within the select statement.
The field_name and field_name2 are optional aliases for the additional fields. If the additional field names match the name of the fields on the block, you do not have to alias them.
The value is the database value that will be obtained from the database and inserted into the field when it is selected.
The additional value is the text that will actually be displayed in the list of values.
The additional value 2 is the database value that will also be obtained from the database and inserted into the matching field on the block when the value is selected.
The additional value 3 is the database value that will also be obtained from the database and inserted in the matching field on the block when the value is selected.
The table is the database table against which the statement will be executed to retrieve the values.
The where clause restricts the information displayed in the list of values.
The order by clause indicates the order in which the data will be sorted and displayed.

An example of an auto-populating query would be:

SELECT item_no, description ITEM_DESC, stock_um UM FROM item_master WHERE stock_um = 'EA' ORDER BY item_no
This list of values displays all of the item numbers, their descriptions, and their stock units of measure in the item master table with a stock unit of measure of EA. The list is ordered by item number. When a value is selected, the item number will be inserted into the field on the block, the description value will be inserted into the ITEM_DESC field on the block, and the stock_um value will be inserted into the UM field on the block.

SELECT item_no, description, stock_um FROM item_master WHERE stock_um = 'EA' ORDER BY description
This list of values displays all of the item numbers, their descriptions, and their stock units of measure in the item master table with a stock unit of measure of EA. The list is ordered by item description. When a value is selected, the item number will be inserted into the field on the block, the description value will be inserted into the DESCRIPTION field on the block, and the stock_um value will be inserted into the STOCK_UM field on the block. Since these fields lack aliases, they are inserted into fields that match the database column name.

SELECT empno, firstname||' '||lastname NAME FROM lremployee WHERE ORG_UNIT_ID = $$ORG_UNIT_ID$$ ORDER BY lastname
This list of values displays all employee numbers and a concatenated version of the first name and last name (in the format first name last name) in the employee master table with a financial enterprise value that matches the global financial enterprise value. The list is ordered by the employee last name value. When a value is selected, the employee number will be inserted into the field on the block, and the employee's first name and last name values will be inserted into the NAME field on the block, with a space between them.

Xephr is a registered trademark of NDS Systems, LC.