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.
See Also
Creating Query Lists of Values
Modifying Query Lists of Values
Creating Static List Lists of Values
Modifying Static List Lists of Values
Auto-Populating 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.
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:
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.
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.
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 |
or
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
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.
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.
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.
Copyright