Tutorial 10: How to build a tree (Part II)

This tutorial is a continuation of the “How to build a tree (Part I)” tutorial. You need to follow and complete the first tutorial in order to complete this tutorial. So, if you haven’t completed the first one, please give it a try.

In this tutorial, we will evolve the Datasource Definition query to supply more nodes to the tree and add an event to populate new nodes.

Open “treeApp” application. Expand Datasource Definitions accordion and then expand Datasource Definitions node on tree. You will see “treeDSD”, double click and open the dialog. Click the “Query” node from DSD navigation tree. You will see our previous query:

select 'DEPARTMENTS' as id, 'Departments' as name
from dual

This query returns only one row but we need more to list all departments in “Departments” table.
To achieve this, we have two different methods:

  • Using single datasource which we defined in the tree XML
  • Using separate datasources while populating new nodes

In this tutorial, we will use the first method. Later, we will use separate datasources in order to populate new nodes.

Using Single Datasource

Tree component has different nodes in different levels. For instance, there is a “Departments” root node in our example and we want to list all departments under this root node. These are two different queries. First one is our previous query:

select 'DEPARTMENTS' as id, 'Departments' as name
from dual

And second one is:

select DEPARTMENT_ID as id, DEPARTMENT_NAME as name
from DEPARTMENTS

In using single datasource method, we have only one query so we need to combine these queries in one query.

select 'DEPARTMENTS' as id, 'Departments' as name
from dual
union all
select to_char(DEPARTMENT_ID) as id, DEPARTMENT_NAME as name
from DEPARTMENTS

Now, we need to distinguish the results of queries from each other for different levels. To achieve this we will create a bind variable to “treeDSD” Datasource Definition.

The Bind Variables

Click “Bind Variables” node in DSD navigation tree and then click “New BindVar” button. New empty row will be created in grid, enter “nodeLevel_nr” as Name and select “DefaultNumber” from Domain combobox. Go back to the Query screen by clicking “Query” node in DSD navigation tree.

The Query

Now we are ready to write the query. We need to add “nodeLevel_nr” bind variable to each select statement and order by clause to sort the results. Replace current query with the following one and click the OK button. Notice that in order to declare “nodeLevel_nr” as bind variable, we have to use prefix “:”, same syntax as PL/SQL bind variables for convenience.

select 'DEPARTMENTS' as id, 'Departments' as name
from dual
where :nodeLevel_nr is null
union all
select to_char(DEPARTMENT_ID) as id, DEPARTMENT_NAME as name
from DEPARTMENTS
where :nodeLevel_nr = 1
order by name

When the bind variable (nodeLevel_nr) is null (at the beginning of the application), query returns only “Departments” and when it is 1, query returns list of all department names.

Adding An Event

First connect to Datasource Schema (If you are using Formspider Online please get your Datasource schema name under Tools>User Settings menu, otherwise we already set it as “HR” while creating the application) with your favorite PL/SQL editor (for instance SQL Developer, TOAD, or SQL Navigator) and create a new procedure as “expandNode”. And Copy following code to procedure.

PROCEDURE EXPANDNODE is
  v_treeName_tx varchar2(255) := 'mainPanel.tree1';
  v_nodekey_tx varchar2(255);
BEGIN
  -- get expanded nodekey
  v_nodekey_tx := api_tree.getexpandednodekey(v_treeName_tx);

  -- if it is "DEPARTMENTS" set variable as 1
  if v_nodekey_tx = 'DEPARTMENTS' then

    api_datasource.setbindvar(in_datasourcedotbindvar_tx=>'treeDSD1.nodeLevel_nr',
   in_value_nr=>1);

    -- populate children of the node.
    -- We are using Single Datasource method to populate new nodes so we do not need to
    -- specify datasource name as parameter. The Formspider will use default one which is
    -- defined in panel XML.
    api_treenode.populatechildren(v_treeName_tx, v_nodekey_tx);
  end if;
END;

Then return to the Formspider IDE create a new Action which will later be executed in the tree1 “expanded” event. Open “Actions” navigation tree and click the “Actions” node and then click the “+” button above the navigation tree or right click the node and click the “New” from the popup menu. Enter “expandNode” as the name of the action and enter “expandNode” as the procedure name and click OK button to create the Action. Now we need to add “expanded” event to tree, open panel XML again and add the event. Here is the last state of the panel XML.

  <panel>
    <tableLayout>
      <row>
        <cell hAlign="Full" vAlign="Full">
          <tree name="tree1" dataSource="treeDSD1"  displayColumn="NAME" nodeKeyColumn="ID">
            <events>
              <expanded action="expandNode"/>
            </events>
          </tree>
        </cell>
      </row>
  </tableLayout>
</panel>

We are ready to run the application. Click the Run button or press F7 key. Here is the preview of the application.

Preview of the applications