Use Lists of Values to provide validation on a text item when you expect to have more than fifteen values.

For more information, see the Oracle Applications User Interface Standards for Forms-Based Products.

Property Class

Apply the LOV property class to all LOVs.

Base LOVs on Views

You should base your LOVs on views. This denormalizes any foreign key references and provides the same performance advantages as basing your blocks on views.

An LOV view is usually simpler than a form view, since it does not include all denormalized columns. The LOV view does need to join to foreign key tables to get meanings associated with list and radio group values, whereas in a form view the meanings are hardcoded into the boilerplate text or the widget.

When Not To Use a View

If the view is very simple or, conversely, overly-complicated because the bind variables are not in the SELECT list, then you may code the entire SQL needed by the LOV directly into the form.

Rules

• The title of an LOV is the name of the object in the LOV, and is plural.

• The prompt of the first column is related to, or matches identically, the prompt of the item that invoked it.

• The width of each column should be wide enough to show most values (just like the width of fields on a canvas). Make the LOV wide enough to show all included columns, up to a maximum of 7.8".

• Always specify the default value by the value, not the label. This ensures that the default is translated correctly.

• Use your judgement when deciding which columns to bring over for the LOV. Sometimes you only need to bring over a primary key and its display name, if the rest of the data would take too long to fetch. After the row is selected, use the WHEN-VALIDATE-ITEM trigger to bring over any other necessary columns. VARCHAR(2000) columns should not be part of an LOV.

Show Only Valid Values

A LOV should show only those rows that currently can be selected, unless the LOV is in a Find Window (Find Window LOV's show all rows that have ever been valid).

EXCEPTION: Validation can be performed after-the-fact if any of the following apply:

• The validation clause cannot be written in SQL.

• The validation clause is too costly to evaluate in SQL.

• The reason for exclusion from the list is obscure to the user.

In such cases, after the value is selected, show an error message indicating exactly why the value could not be selected.

Row-LOV

For more information on how to code Row-LOVs in response to "View->Find," see:

See: Query Find Windows

Assigning Values in POST-QUERY

If your item has a List of Values, the Validate from List property is set to Yes, and you assign a value to the field in the POST-QUERY trigger, the item is marked as changed because the LOV fires. To avoid this complication, set the RECORD_STATUS back to QUERY at the end of the POST-QUERY trigger.

You may alter the properties on your LOV to create the following behavior:

Automatic Refresh

If the values displayed by the LOV are static during a session and the number of rows is not excessive, turn Automatic Refresh off (No) to cache the LOV values for the session. Caching the values avoids database hits and network round trips for subsequent invocations of the LOV, and eliminating unnecessary round trips is a key factor in producing a product that can run on a wide area network. However, the caching consumes memory that is not recovered until the form is closed.

Filter Before Display

If an LOV may show more than one hundred rows, then the user must be prompted to reduce the list of valid values first (Filter Before Display:Yes).

Never have Filter Before Display set to Yes, and Automatic Refresh set to No, on an LOV. This combination would cause only the reduced set of rows to be cached if the user enters something in the reduction criteria window. With Automatic Refresh off, there is no way of returning to the full set of rows. Typically it is not wise to cache an LOV that returns more than 100 rows.

Example LOV

The EMP table contains the following columns: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM AND DEPTNO. DEPTNO is a foreign key to the table DEPT, which contains the columns DEPTNO, DNAME, and LOC.

A form view of the EMP table would contain all columns in EMP, denormalize EMP.DEPTNO, and include the column DEPT.DNAME, as well. It might also include DEPT.LOCATION and other DEPT columns, and contain records for all past and present employees:

CREATE VIEW EMP_V AS

SELECT EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.MGR, 
   EMP.HIREDATE, EMP.SAL, EMP.COMM,
   EMP.DEPTNO, DEPT.DNAME, DEPT.LOCATION
FROM EMP, DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO;

By contrast, an LOV view of EMP would only include columns EMP.EMPNO and EMP.ENAME. DEPT information would be included only if necessary to help select an employee.

Decoding Y/N Values

For Y/N values, decode "Y" to "*" and "N" to null to avoid a join to FND_LOOKUPS.

A table T has columns ID, NAME, and ENABLED_FLAG. ENABLED_FLAG contains Y/N values. Create your view as follows:

 CREATE VIEW T_V AS
    SELECT ID, NAME, 
    DECODE(ENABLED_FLAG, 'Y', '*', NULL) 
    FROM T;

Dependent Fields

An LOV on a dependent field should use the value in the master field to reduce the list.

For example, if NAME is dependent on TYPE, the entry LOV for NAME's WHERE clause would include the condition:

WHERE TYPE = :MY_BLOCK.TYPE

LOVs in ENTER-QUERY mode should be used sparingly, as Query Find is the preferred method for a user to locate records.

You should only code them where they dramatically improve the usability of ENTER-QUERY mode, and you expect this mode to be used regularly despite Query Find.

An LOV in ENTER-QUERY mode should display all values that the user can query, not just currently valid values.

EXAMPLE: An LOV for vendors in a purchase order form in enter-query mode shows all vendors that could ever be placed on a PO, not just the set of vendors that currently are allowed to be placed on a PO.

Do not reuse the entry LOV in ENTER_QUERY mode unless it provides the correct set of data for both modes.

See: Query Find Windows

Implementation

To enable LOVs in ENTER-QUERY mode on an item, create an item-level KEY-LISTVAL trigger as follows:

Trigger: KEY-LISTVAL

IF (:SYSTEM.MODE != 'ENTER-QUERY') THEN LIST_VALUES;
ELSE SHOW_LOV('query lov');
END IF;

Return into the LOV Item Only

When implementing LOVs in ENTER-QUERY mode, do not return values into any field other than the field from which the LOV is invoked. If the LOV selects into a hidden field, there is no way to clear the hidden field. Clearing or typing over the displayed field will not clear the hidden field. Users must select another value from the LOV or cancel their query and start over.