Oracle Applications: 05/23 FORMS

List of Values (LOV)

A List of Values (LOV) is a modal window that populates a text item based on a selection made by the user from the list. The user can search a List of Values (LOV) for strings that occur anywhere within the values.

Record Group

A Record Group is a query that displays data from the database into the List of Values.

Hands-On

Your client needs more robust customer’s information retrieval. You are going to provide them with a list of customer names by adding a List Of Values (LOV) to the Form. This will help the data entry clerks to find a customer quickly without knowing the customer’s ID. The list should contain at a minimum, the customer's name and ID.

This will provide a fast and quick way to access customer information and increase the accuracy of the data entry.

See Figure 5.

Your tasks are:

1- Add a List Of Values (LOV) to the Form.

2- Retrieve customer ID by using customer name.

3- Query specific customer information using customer name.

4- Run and test all user functional requirements.

In this Hands-On, you will learn how to: use the List of Values (LOV), and group records, use tab canvas, use “object navigator,” use “Data Blocks,” use “Layout Editor,” use “Property Palette,” use “Run Form,” and “Execute Query.”

Figure 5

Open a Module

In the ‘Object Navigator’ window, highlight Forms. Go to the Main menu and choose “File,” select “Open” to open an existing form (customer_orders_V02)

from the “iself” folder.

Save with different name

Click on the “customer_orders_v02” form. The color changes to blue. Then change the name and save the Form name as version 03 (customer_orders_v03). This way the original form is untouched.

Layout Editor

In the Main menu, choose the "Tools" option; you can see all the Form tools. Select the "Layout Editor" option.

Create a List Of Value

In the ‘Object Navigator’ window, select the LOVs object known as "List of values."

Then click on the green ‘+’ (Create) icon. In the ‘New List Of Value’ window, leave the default radio button which is: ‘Use the LOV Wizard’ and the click ‘OK.’

Record Groups

LOVs get their data from Record Groups, which may be populated using SQL queries, or by entering a static list of values. In the LOV Wizard window, use the default to base your LOV on a new Record Group; and then click ‘Next.’

In the ‘SQL Query Statement’ box, write a ‘SQL’ statement to query customers name and their ID. Then click ‘Next.’

(SQL Query Statement)

SELECT name, custid FROM customer

The LOV may include some or all of the columns in the Record Group. Select the columns that you wish to include in your LOV. In this hands-on exercise, select all the columns and click “Next.”

You can specify the LOV column properties with its return value. Click on the "return value" box which is on the same row of the ‘CUSTID’ item; then click on "Look up return item." In the ‘Return Value’ window, select the "customer.custid" item and click "OK."

Notice that the ‘CUSTOMER.CUSTID’ value will be replaced in the box.

Click “Next” to continue.

Appearance of LOV window

If you want to display a title in your LOV window, type the title. You can change the size of the LOV window (Width and Height). You can also let the Forms Runtime to position your LOV. Apply changes if needed and click “Next.”

Behavior of LOV

To change the behavior of your LOV, change number of rows to be retrieve at a time, refresh record group data before displaying LOV, or Let the user filter records before displaying them. Accept the defaults as they appear and click ‘Next.’

Assign your LOV to ‘CUSTOMER.CUSTID’ by moving ‘CUSTOMER.CUSTID’ to the ‘Assigned item’ column and click “Next” again.

Click ‘Finish’ to create the new LOV. Now, your ‘Object Navigator’ should have an entry for your new LOV.

In the ‘Object Navigator’ window, right click on the new record groups to open its property palette. In the Property Palette, change the name to a more meaningful naming convention (RG_CUST_NAME); and then press the enter key.

Do the same for the new LOV object. Change the ‘Name’ value to LV_CUST_NAME. Notice that the ‘Record Group’ value was changed to RG_CUST_NAME.

In the ‘Object Navigator’ window, expand the ‘CUSTOMER’ data block in the ‘Data Blocks’ item. Expand the ‘Items’ object. Now, you should see all your selected data block items.

Assign LOV to an item

Right click on the CUSTID item and open its Property Palette. Go to the ‘List of Values’ item. Check to make sure that the List Of Values was assigned to it and then close the window.

Run the Form Runtime

Click on the "Run” icon (Traffic Green icon), to compile and run the application.

Notice that when the cursor is on “CUSTID,” the "List of Values" message will be displayed.

Enter Query

Click on the "Enter Query" icon to query a specific customer.

In the Main menu, click on “HELP” and select the ‘Keys’ option, to see all the function keys. Notice that the “F9” key is used for the “List of Values” item.

Retrieve LOV (F9)

Press “F9,” while the cursor is in the "CUSTID" item. A list of all the customers will be displayed. Select "Every Mountain" and then click “OK.”

Execute Query

Notice that its “Customer id” was returned.

Click on “Execute Query,” and navigate through the application and test the changes.

Click on “Enter Query,” and Press on the “F9” function.

In the LOV window, type the letter "W." then select the customer.

Click "Execute Query."

Navigate through the application.

Then close the window and save the form.

Questions:

Q: What is a List of Values (LOV) in a Form Module?

Q: How can you search a List of Values for a string?

Q: What is a Record Group in the Form Module?

Q: What is a Tab Canvas?

Q: How does a Record Group work with a List of Values?