Apex tree and form one one page

I had an idea to combine a tree and a form in one page. Actually, just because I never used a tree before (yes, really), and also because I couldn't find an example on internet. The Oracle Sample Trees application shows how you can use a tree node with a link to another form page. That is a waste of a lot of space on the right side of the page. What I wanted is this:

Basically, you need to have a tree, a form and a dynamic action that refreshes the form when you click on an employee node. A lot of it I picked from Scott Wesley's blog.

Create the tree region

The tree uses this query

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status,
       level,
       label||' '||name as title,
       null as icon,
       id as value,
       name as tooltip,
       'javascript:pageItemValue('''||id||''')' as link
from
select 'D' item_type,
       null label,
       to_char(d.deptno) id,
       null parent,
       d.dname name,
       null tooltip,
       null link
from dept d
union all
select 'E' item_type,
       null label,
       to_char(e.deptno)||'_'||to_char(e.empno) id,
       to_char(e.deptno) parent,
       e.ename name,
       null tooltip,
       null link
from emp e
)
start with parent is null
connect by prior id = parent
order siblings by name

Nothing special here as you can see (I included an item_type in the query for possible use later on).

Add the form region

Scott uses a classic report report region to refresh when you click on a node, where I needed a form region. My first attempt was to create a "Form on a Table or View" region, but I soon got stuck here. This kind of form uses a After Header process to fetch a row, and therefore only works on a page submit. I needed a partial page refresh. (Maybe it is possible to use this process in an Ajax call too, but I have no idea how to do that). So, I used a Tabular Form instead, based on EMP, where I would query just one record. After the region is created using the wizard (choosing Update functionality only in this case), slightly modify the query by adding a WHERE clause

select 
"ROWID",
"EMPNO",
"ENAME",
"JOB",
"SAL",
"COMM"
from "#OWNER#"."EMP"
where "EMPNO"= to_number(substr(:Pn_SELECTED_NODE,4))

(I will get back on Pn_SELECTED_NODE. Include this item in the "Page items to Submit").

I've mainly used Apex 4.1, so with the new templates of Apex 4.2, I was a bit confused on how to get the form region to the right of the tree. You just have to set New Column = Yes in the Grid Layout of the form region.

Add a hidden item and some Javascript

As In Scott's example, add a hidden page item Pn_SELECTED_NODE to the page. It is important is to set Value Protected to No for this item.

Add the Javascript to to page header:

function pageItemValue(node)

{

  $s('Pn_SELECTED_NODE', node);

}

Create a dynamic action

Add the dynamic action to Pn_SELECTED_NODE, so that the form refreshes when you click on a node.

For this refresh to work, you have to to set Enable Partial Page Refresh to Yes in the Report Attributes for the tabular form region.

Get back to the same node after submitting the page

Now, this was actually the most frustrating part. On the tree, you have to set Selected Node Page Item to Pn_SELECTED_NODE to come back to the same node you selected before.

This just wouldn't work at all. I still have no idea why, though. I could see the correct value using the Session link when running the page, but after submitting the page (saving the updates in the form), the previously selected node just wouldn't highlight again.

At this point you just start to try about anything to get it to work. And voilà, the solution: on the branch back to the page, set Pn_SELECTED_NODE: