Overblog
Folge diesem Blog Administration + Create my blog

Working with trees

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

The task

Trees are a nice feature to present hierarchical data. And, since forms 5 they are available as standard components in forms. A lot of forum posts are related to trees and the way they are populated. I want to show some different techniques to populate a tree and a way to interact with "standard" data-blocks.

Sample

Lets build a form with a tree showing the employee-hierarchie and a "standard" datablock, showing all the employees which have the selected employee in the tree as manager.

Building the basic form

First, we build a datablock EMP based on the table employees with a tabular layout. Then create another datablock BL_TREE with property "Single Record" set to true and create a tree-item in it, lets name it IT_TREE. In the layout we place the tree at the left of the tabular layout, so that it looks like the explorer.

Populating the tree - using a fixed query

The easiest way to populate a tree is by defining a query with a specific result-set-structure and just put that query in the property "Data Query" of the tree.

The query has to be defined so that it has the following result-columns

column-name

meaning

NODE_STATE

defines, how the node is initially shown in the tree
-1 = collapsed
 0 = leaf-node
 1 = expanded

NODE_DEPTH

The tree-level the row it at. This column defines the hierarchy of the tree-data

NODE_LABEL

The text to be shown in the tree-node

NODE_ICON

The name of an icon-file which should be rendered for the treenode. The icon must be accessible to forms as any icon used, e.g. on a button

NODE_VALUE

The "value" to be stored for the tree-node

 

 

 

 

 

 

 

 

 

 

Our example-table EMPLOYEES already has a hierarchical structure, each employee has a manager assigned via the MANAGER_ID, where the referenced manager is an employee him (or her)self.
So, our query look like

SELECT -1                              NODE_STATE,
       LEVEL                           NODE_DEPTH,
       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,
       NULL                            NODE_ICON,
       EMPLOYEE_ID                     NODE_VALUE
  FROM EMPLOYEES
CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID
START WITH MANAGER_ID IS NULL
ORDER SIBLINGS BY LAST_NAME

One last thing is to do, we have to populate the tree on startup of the form. So we create a WHEN-NEW-FORM-INSTANCE-trigger with the following code

Ftree.POPULATE_TREE('BL_TREE.IT_TREE');
Populating the tree - using a query which may change at runtime

A quite similar approach is to use a record-group as base-data for the tree. The structure of the data has to be the same. But, with a recordgroup you can change the query at runtime, e.g. when some userinput influences the data to be shown in the tree.

For this approach, we create a recordgroup named RG_TREE with the same query as in the last approach and assign it to the tree using the property "Record Group"

The population could also be done in the WHEN-nEW-FORM-INSTANCE-trigger, but because of the idea of re-populating it under certain condition, we put the logic in a procedure and just call that from the trigger. Here's the procedure

PROCEDURE PR_TREE IS
  rgTree  RECORDGROUP;
  vcQuery VARCHAR2(2000);
  nStatus NUMBER;
  itTree  ITEM := FIND_ITEM ('BLOCK.TREE');
BEGIN
  rgTree := FIND_GROUP ('RG_TREE');
  -- Clear Tree
  Ftree.DELETE_TREE_NODE (itTree, Ftree.ROOT_NODE);
  -- Build up a new Query, if desired
  vcQuery := 'SELECT -1                              NODE_STATE,' ||
             '       LEVEL                           NODE_DEPTH,' ||
             '       LAST_NAME || ', ' || FIRST_NAME NODE_LABEL,' ||
             '       NULL                            NODE_ICON,' ||
             '       EMPLOYEE_ID                     NODE_VALUE' ||
             '  FROM EMPLOYEES' ||
             ' CONNECT BY PRIOR EMPLOYEE_ID=MANAGER_ID' ||
             ' START WITH MANAGER_ID IS NULL' ||
             ' ORDER SIBLINGS BY LAST_NAME';

  -- Repopulate Record-Group
  nStatus := POPULATE_GROUP_WITH_QUERY (rgTree, vcQuery);
  -- Repopulate Tree
  Ftree.POPULATE_TREE(itTree);
END;
Populating the tree - using PL/SQL and FTree

The third way to populate a tree is to do everything "by hand". All functionality to interact with a tree are included in the standard-package Ftree. So, there is also a function to add a new node to a tree.

The logic is encapsulated in the following package. There's a procedure PR_FILL_TREE to add the top-level-nodes to the tree (The managers which do not have managers themselves), and, for every manager, the procedure PR_QUERY_DETAILS to read the employees of this manager. And, because each of these can also be a manager the procedure recursivela calls itself. Here's the code

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
      RETURN Ftree.Add_Tree_Node(itTree,
                                 i_ndMaster,
                                 Ftree.PARENT_OFFSET,
                                 Ftree.LAST_CHILD,
                                 i_nState,
                                 i_vcLabel,
                                 i_vcIcon,
                                 i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      FOR rec IN crDetails LOOP
          nd:=FK_ADD_NODE(i_ndMaster, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
 
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
      Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
      FOR rec IN crTop LOOP
          nd:=FK_ADD_NODE(Ftree.ROOT_NODE, rec.VALUE , rec.LABEL);
          PR_QUERY_DETAILS(nd, rec.VALUE);
      END LOOP;
  END;
END;

Now, in the WHEN-NEW-FORM-INSTANCE-trigger we just call

PK_TREE.PR_FILL_TREE;
Large trees - using partial tree population

Whats the advantage of doing all this "handwork" if it can be done in a single query?
Sometimes it's not possible to put all the logic into a single query, so you have todo it manually. Another possible situation is that you have a large tree with lots of nodes and the population would last very long if all the data would be read at startup. In this case, a partial population can increase performance. The idea behind this is, just read the top-level at startup and collapse all these node, and, at the moment the user expands a node, query the next level of data into that node.

We already have a procedure to populate the top-level and also one for populating the "children" under a master-node. All we need to do is have some event when to query required data and a marker, if the details already have been read. We do this by simply putting a minus in front of the value for "unpopulated" children and remove that minus at the moment the details are queried. The event to populate the details is the moment when the user expands a node, so we use the appropiate trigger, the WHEN-TREE-NODE-EXPANDED-trigger.

So, lets modify the code a litte bit.

PACKAGE PK_TREE IS
 
  PROCEDURE PR_FILL_TREE;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE);
 
END;

PACKAGE BODY PK_TREE IS
 
  itTree ITEM:=FIND_ITEM('BL_TREE.IT_TREE');
 
  FUNCTION FK_ADD_NODE(i_ndMaster IN FTree.NODE,
                       i_vcValue  IN VARCHAR2,
                       i_vcLabel  IN VARCHAR2,
                       i_vcIcon   IN VARCHAR2 DEFAULT NULL,
                       i_nState   IN NUMBER DEFAULT Ftree.EXPANDED_NODE
                      )
  RETURN Ftree.NODE IS
  BEGIN
    RETURN Ftree.Add_Tree_Node(itTree,
                               i_ndMaster,
                               Ftree.PARENT_OFFSET,
                               Ftree.LAST_CHILD,
                               i_nState,
                               i_vcLabel,
                               i_vcIcon,
                               i_vcValue);
  END;
 
  PROCEDURE PR_QUERY_DETAILS(i_ndMaster   IN Ftree.NODE,
                             i_nManagerId IN NUMBER) IS
    CURSOR crDetails IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID=i_nManagerId
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    FOR rec IN crDetails LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(i_ndMaster, -rec.VALUE , rec.LABEL);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_FILL_TREE IS
    CURSOR crTop IS
      SELECT EMPLOYEE_ID VALUE,
             LAST_NAME || ', ' ||FIRST_NAME LABEL
        FROM EMPLOYEES
       WHERE MANAGER_ID IS NULL
       ORDER BY LAST_NAME;
    nd FTree.NODE;
  BEGIN
    Ftree.DELETE_TREE_NODE(itTree, FTree.ROOT_NODE);
    FOR rec IN crTop LOOP
      -- add the node with a negative id
      nd:=FK_ADD_NODE(Ftree.ROOT_NODE, -rec.VALUE , rec.LABEL, NULL, FTree.COLLAPSED_NODE);
      -- don't add details here
    END LOOP;
  END;
 
  PROCEDURE PR_WTNE(i_ndNode IN Ftree.NODE) IS
    nValue   NUMBER;
    ndDetail FTree.NODE;
  BEGIN
    -- Get the value of the node to be expanded
    nValue:=FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE);
    IF nValue<0 THEN
      -- value is negative, then we have to read the details
      PR_QUERY_DETAILS(i_ndNode, ABS(nValue));
      -- Now set the value of the node to positive, so that at next expansion we won't re-read the details
      FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE, ABS(nValue));
      ndDetail:=FTree.FIND_TREE_NODE(itTree,'',FTREE.FIND_NEXT, FTREE.NODE_LABEL,i_ndNode,i_ndNode); 
      -- if there we're no details, set node as lead,
      IF Ftree.ID_NULL(ndDetail) THEN
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      ELSE
        -- otherwise set node to expanded
        FTree.SET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_STATE, FTree.EXPANDED_NODE);   
      END IF;
    END IF;
  END;
 
END;

At the tree-item, we have to create an additional WHEN-TREE-NODE-EXPANDED-trigger which calls our new procedure

PK_TREE.PR_WTNE(:SYSTEM.TRIGGER_NODE);

Important:
To have this working, you must set the property "Allow Empty Branches" at the treeitem to "Yes".

Synchronizing the data block

The last requirement is that whenever the user selects an entry in the tree, all employees who have the selected employee in the tree as manager should be queried in the block at the right.

We enhance the PK_TREE for this with a procedure PR_WTNS and create a WHEN-TREE-NODE-SELECTED-trigger at the tree-item which calls that new procedure for this:
 

  PROCEDURE PR_WTNS(i_ndNode IN Ftree.NODE) IS
    nValue NUMBER; 
  BEGIN
    IF :SYSTEM.TRIGGER_NODE_SELECTED='TRUE' THEN
        -- Get the Employee-id, remeber that we made it negative
        nValue:=ABS(FTree.GET_TREE_NODE_PROPERTY(itTree, i_ndNode, Ftree.NODE_VALUE));
        -- Restrict block
        SET_BLOCK_PROPERTY('EMPLOYEES', ONETIME_WHERE, 'MANAGER_ID=' || TO_CHAR(nValue));
        GO_BLOCK('EMPLOYEES');
        -- execute block
        EXECUTE_QUERY;
    END IF;
  END;



 

Weiterlesen

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