Working with trees

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.

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.

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.

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

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

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;

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;

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".

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;