Populating listitems dynamically
Listitems in forms are quite useful if one has to offer a smaller choice of values to the enduser. Often the values to be displayed have to be taken from another table, so the listitems values have to be read at runtime.
Also, listitems are often used for so called "domains". A domain in general is a list of discrete values, each of which has a meaning in form of a text. To avoid hardcoding the values, in many projects there is a domaintable which stores the allowed values along with the textual meaning.
So, lets have a look at how a listitem can be "populated" using Forms-builtin's.
As example, lets assume we want to have a listitem in a form which is based on the column DEPARTMENT_ID from the HR-table EMPLOYEES, and in the form the item should be displayed as a listitem where the user can choose the department-name from.
One way to populate a listitem is to use a record-group in conjunction with a query. The query to populate a list must have to columns of datatype VARCHAR2, where the first one is the Label to be displayed to the user, and the second one is the value stored as itemvalue. Here's the example to populate the department-item, in my example used in the PRE-FORM-trigger:
DECLARE
rg RECORDGROUP;
nDummy NUMBER;
BEGIN
rg:=CREATE_GROUP_FROM_QUERY('RG', 'SELECT DEPARTMENT_NAME LABEL,
TO_CHAR(DEPARTMENT_ID) VALUE FROM DEPARTMENTS ORDER BY 2');
nDummy:=POPULATE_GROUP(rg);
POPULATE_LIST('EMPLOYEES.DEPARTMENT_ID', rg);
END;
The other way to populate a listitem is to use the built-in's to handle a listitem in detail. There are built-in's to add or delete a row or to clear the entire list-item.
To achieve the same result as in the former example using these built-in's, the following code can be used
DECLARE
CURSOR crDept IS
SELECT DEPARTMENT_NAME LABEL,
TO_CHAR(DEPARTMENT_ID) VALUE
FROM DEPARTMENTS
ORDER BY 2;
it ITEM:=FIND_ITEM('EMPLOYEES.DEPARTMENT_ID');
BEGIN
CLEAR_LIST(it);
FOR rec IN crDept LOOP
ADD_LIST_ELEMENT(it, GET_LIST_ELEMENT_COUNT(it)+1, rec.LABEL, rec.VALUE);
END LOOP;
END;
Of course, you could also use dynamic sql instead of the "fixed" cursor.
When filling a listitem dynamically you won't be able to define a "hardcoded" deafult-value, you will receive a FRM-30362.
To set a default-value in this case, you can either use an "indirect" initial value with using a global (something like :GLOBAL.G_INITIAL_DEPTNO) or parameter (something like :PARAMETER.P_INITIAL_DEPTNO). In both cases you have to make sure that the initial value in in the list. The other way is the use of a WHEN-CREATE-RECORD-trigger ad there setting the value of the listitem to the desired value, e.g.
:EMPLOYEES.DEPARTMENT_ID:=20;
Using the first method its quite easy to encapsulate the logic in a generic procedure:
PROCEDURE PR_POPULATE_LIST(i_vcItem IN VARCHAR2, i_vcQuery IN VARCHAR2) IS
itList ITEM:=FIND_ITEM(i_vcItem);
rgQuery RECORDGROUP:=FIND_GROUP('RG_QUERY');
nDummy NUMBER;
BEGIN
-- Check item-existance
IF ID_NULL(itList) THEN
-- Error-message "Item does not exist"
message('Item does not exist', ACKNOWLEDGE);
RAISE FORM_TRIGGER_FAILURE;
ELSE
-- delete possibly existing record-group
IF NOT ID_NULL(rgQuery) THEN
DELETE_GROUP(rgQuery);
END IF;
-- create the group from the query
rgQuery:=CREATE_GROUP_FROM_QUERY('RG_QUERY', 'SELECT LABEL, TO_CHAR(VALUE) VALUE FROM (' || i_vcQuery ||')');
IF NOT FORM_SUCCESS THEN
RAISE FORM_TRIGGER_FAILURE;
ELSE
-- populate the group
nDummy:=POPULATE_GROUP(rgQuery);
IF nDummy!=0 THEN
-- Error-message "Error during group-population"
message('Error during group-population' || TO_CHAR(nDummy), ACKNOWLEDGE);
RAISE FORM_TRIGGER_FAILURE;
ELSE
-- fianlly, populate the list-item
POPULATE_LIST(itList, rgQuery);
END IF;
END IF;
END IF;
END;
The error-handling would have to be adjusted according to the message-handling used in the application.
So finally, the code to fill a list-item is simply a
PR_POPULATE_LIST('EMPLOYEES.DEPARTMENT_ID', 'SELECT DEPARTMENT_NAME LABEL, DEPARTMENT_ID VALUE FROM DEPARTMENTS ORDER BY 2');
/http%3A%2F%2Fimg.youtube.com%2Fvi%2FtDKzcd-i-S8%2F0.jpg)
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/idata%2F2654307%2Ftreetable_01.jpg)
