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.
Using a query
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;
Doing it "by hand"
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.
Dealing with default-values
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.
Encapsulating the logic
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');