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
defines, how the node is initially shown in the tree
The tree-level the row it at. This column defines the hierarchy of the tree-data
The text to be shown in the tree-node
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
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
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
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
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;
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.
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');