Forms: How to Dynamically Populate a Poplist

Collected from the link:https://sites.google.com/site/craigsoraclestuff/oracle-forms---how-to-s/forms-howto-dynamically-populate-a-poplist



This demo is for everyone how has ever wanted to use a Poplist populated from a database query but was unable to because there are very few examples in the Forms Help system to help you create one.

Disclaimer:  This demo is built using Oracle Forms 10g R2 (10.1.2.0.2) but it should work with older versions of Oracle Forms that support the List Item Poplist item type.  This demo also uses the new sample HR schema.  If you don’t have access to this schema, you will have to modify the code to suit your needs.

Let’s get started…

1. Open Oracle Forms Builder and create a new form module.
2. Connect to your database with a user that has SELECT privilege to the HR schema tables.
3. Create a new data block using the Data Block Wizard and select the HR.EMPLOYEES table and all columns.
4. Let the wizard call the Layout Wizard and select to display all columns in a Form Layout.
5. From the Object Navigator open the EMPLOYEES block and then open the Property Pallet for the MANAGER_ID item.
6. Change the following Properties:
1. Item Type: List Item
2. Data Type:  CHAR
3. Width: 100
7. Now, select the EMPLOYEE_ID item in the Object Navigator and change the following properties:
1. Item Type:  List Item
2. Data Type:  CHAR
3. Width:  100
8. Your Layout should look similar to the following:

 

Now we’re ready to write the code.  Create a Forms Level When-New-Form-Instance trigger and add the following code:

DECLARE       rg_dept     RecordGroup;       rg_mgr      RecordGroup;       rg_dname    VARCHAR2(4) := 'DEPT';       rg_mname    VARCHAR2(3) := 'MGR';       dlist_ID    Item := Find_Item('EMPLOYEES.DEPARTMENT_ID');       mlist_ID    Item := Find_Item('EMPLOYEES.MANAGER_ID');       nDummy      NUMBER; BEGIN       rg_dept := Find_Group(rg_dname);       rg_mgr  := Find_Group(rg_mname);             -- Delete any existing Group first       IF NOT Id_Null(rg_dept) THEN             Delete_Group(rg_dept);       END IF;       IF NOT Id_Null(rg_mgr) THEN             Delete_Group(rg_mgr);       END IF;       -- Now create a Record Group using a SQL query       -- Your Query must have a Label and a Value (two Columns)       -- and the data types must match your item type          rg_dept := Create_Group_From_Query(rg_dname,'SELECT department_name, to_char(department_id) FROM hr.departments');       rg_mgr := Create_Group_From_Query(rg_mname,'SELECT last_name, to_char(employee_id) from hr.employees');       --Clear the existing List       Clear_List(dlist_ID);       Clear_List(mlist_ID);       -- Populate the Record Group       nDummy := Populate_Group(rg_dept);       nDummy := Populate_Group(rg_mgr);       -- Populate the List Item       Populate_List('EMPLOYEES.DEPARTMENT_ID',rg_dept);       Populate_list('EMPLOYEES.MANAGER_ID',rg_mgr); END;

That’s it!  You are read to run your form.  After you run the form press Ctrl + F11 to query the employees table and your form will look similar to the following.

Note: because we did not define values for the two list items you will get a compiler Warning indicating that there are “No list elements defined for list item.”  This warning can be ignored.

Now you can reassign the Department ID or Manager ID of any employee using the Poplist.

I hope this brief demo was helpful.