Overblog Folge diesem Blog
Edit post Administration Create my blog

Populating listitems dynamically

12. März 2009 , Geschrieben von Andreas Weiden Veröffentlicht in #Forms

The task

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.

Example

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.

:EMPLOYEES.DEPARTMENT_ID:=20;

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');

Diesen Post teilen

Repost 0

Kommentiere diesen Post