Configure LOV's

Application Interface Wizard has dynamic data entry forms. Users can double-click on any cell below Row10 and see all columns listed in the form. Administrators can add LOV's to any column to control the validation of data in the Wizard before uploading information to Oracle.

The LOV Builder is used to configure a LOV for a column. This is useful for turning a column/parameter such as Org ID, into a user-friendly column such as Org Code, where the Wizard sheet displays the value but during upload the code is sent to the database.

To create LOV's, click on ‘Edit By Sheet’ to download the template attributes into a spreadsheet for simpler and quicker editing.

An excel worksheet will open in the Wizard like the truncated image below:

Scroll across to any LOV Setup section of the columns in this worksheet and double-click on one of these cells in the row that contains the column/parameter you want to add a LOV to.

This form is intended to be used by a System Administrator. It requires a SQL statement to be split up into separate fields on the form. There is also a dependency builder under ‘Create Dependency’ which will output a dependency clause to be used in the LOV Setup tab to ensure the correct syntax is used.

Setup Page

The setup details the LOV Setup for the SQL Statement being run.

LOV Type	The LOV Type can be a Short, Long or Key Flex Field. This will determine how the LOV is cached and if there is a search control for the form. A Shortlist will have a static, small list of values. A longlist will have a search control, validation will be slower. Free Type allows us to create a LOV where you can either select a value from the list or you can free type into the LOV field. Note – On upload the parameter that will be uploaded for the list of values will be the value shown on the sheet as it will read as a new LOV value. This functionality is recommended to be used for simple fields where for example we have a list of values for a description, however we want to assign a different description to a record that does not yet exist in the list of values.
LOV Display Width	The LOV Column width is automatically determined when parsing SQL if it is blank. You can then modify the widths if the control drop-down list columns are too small or unreadable.
LOV Cache Type	The LOV Cache Type can be Single-Row or Multi-Row, this will set whether the Wizard queries each value (Single-Row) or 200 rows at a time (Multi-Row) during Upload. In order to use Multi-Row the LOV query needs to be optimized to run effectively.

LOV Page

The LOV page is used to set up the SQL statement. The select clause is made up of the LOV Value Field, LOV Code Field and the Extra LOV Fields.

Select Section

Distinct	Tick to ensure distinct values are displayed in the LOV where this can't be controlled by the SQL statement.
LOV Value Field	This is the value displayed and validated in the Wizard sheet.
LOV Code Field	This value will be uploaded into the parameter/column.
Extra LOV Fields	These are display only data that appear in the LOV when opened using the user data entry forms.

From Section

The FROM field is where all the tables that can be added into the ‘from’ clause. Multiple tables are separated by commas and can also be carriage returned for easier readability.

Inline SQL statements can also be added in this section.

Where Section

The WHERE field is where any joins and filtering criteria can be added. Dependencies will also be automatically placed into the field when the ‘Add Condition’ button is clicked on the Setup page in the dependency builder.

Grouping Rule/Order By Section

The GROUPING RULE/ORDER BY field is where you enter your additional grouping and ordering SQL.

Important - If you prefer to leave redundant code in your LOV SQL it's better practice to comment the code out using the '/* … */ syntax rather than using the double hyphens '--'at the beginning of the code.

While the LOV may still successfully be validated with the double hyphens you may experience unexpected errors when opening data entry forms.

Dependency Builder

The dependency builder is used to generate dependency clauses using values from other LOV's in a format that the wizard can translate. This provides a guided form to ensure the syntax where SQL conditions that use Wizard columns are correct.

Note - Any column available in a LOV can be used to create dependency. This means the 'LOV Display Field', 'LOV Upload Field' and any of the 'Extra LOV Fields' values can be used.

In order for dependency to work as expected, ensure the dependent column has an order by value less than the current LOV column to ensure it is evaluated first.

Click the

button in the LOV Page to open the Dependency Builder form.

Important - The ‘Create Dependency’ feature will ensure the correct syntax is used. If you are experiencing any issues with your LOV’s, particularly if you have manually entered them into the LOV definition, it’s often better to remove them and use this feature to recreate them.

Dependent LOV Field	The dependent LOV Field is a SQL field from the current LOV definition.
Independent Sheet Column	The independent sheet column is a column on the TEMPLATE. This is the parent template column that the dependency value will be derived from. Use the LOV to select a Wizard column.
Independent LOV Field	The independent LOV field is a field from the parent template columns LOV. If the parent column has a LOV, this field will be made available to select from. If this value is blank, it will use the sheet value to derive the dependency.
Output Where Clause	Read Only. This is the clause that will be added to the WHERE clause in the LOV tab. Clicking the “Add Condition” button will add the clause to the WHERE field. The clause can then be moved/edited if necessary.

As values are selected the 'Output Where Clause' dynamically updates.

Click the

button to write the condition into the WHERE section.

Independent LOV Field example:

Note - Once you are familiar with the syntax the Wizard expects for dependencies you can simply type these directly into the WHERE section as needed.

Tip - The syntax below refers to a value that is already displayed in the worksheet at the time this LOV is invoked:

[APPS.M4APS_TIMECARD_LINES|PROJECT_NAME]

Tip - The syntax below refers to a value that exists in a LOV column that isn’t displayed in the worksheet:

[APPS.M4APS_TIMECARD_LINES|PROJECT_NAME:PROJECT_ID]

In the above example PROJECT_ID is the ‘LOV Code Field’ value from the PROJECT_NAME field LOV.

Various LOV Syntax Examples