APEX-AT-WORK no image

Update an APEX tree dynamically

Von Tobias Arnhold 12.01.2013
I was asked by an APEX developer if it is possible to dynamically update an APEX tree by changing an APEX item.

Example select:
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       ENAME as title,
       NULL  as icon,
       EMPNO as value,
       ENAME as tooltip,
       NULL  as link
from EMP
start with (:P1_MGR is null AND MGR is null OR :P1_MGR is not null and MGR = :P1_MGR)
connect by prior EMPNO = MGR
order siblings by ENAME

As far as I know APEX has no out of the box function for that. The dynamic action to refresh reports doesn't work with trees. Or am I wrong here?
You can follow different workarounds no one of them will fulfill all your hopes:

1. Wait for APEX 5.0 maybe the APEX team will include a dynamic tree update functionality?
2. Build some custom JS code to dynamically change the tree.
3. Don't make it dynamically. Submit the page after you have changed the APEX item.
4. Use a third party solution for example: dhtmlx tree
5. Build your own tree solution (plug-in).

I would tend to solution 3 and would wait for the next version of APEX. If the customer doesn't accept it and would pay the more efforts I would tend to a third party solution.  

Update 03.12.2013:
Take a look at Tom's blog he made a couple of really good tree based blog posts.

4 Comments " Update an APEX tree dynamically "

Tom 02 December, 2013 12:53

It's a bit strange though. When you create a tree in apex with that query it'll not work, even when using page submit. It'll work as long as you enter leaf node ids, but nothing will be shown when you start filtering out parent nodes. The reason is probably simple enough: the json data is being built level by level, and works on the level column of the statement. The where clause does not alter the level statement as it is executed after the connect by has occured. So suddenly everything is messed up.
This raises the question however as to what you would've expected. Is adding the where clause an attempt at reducing the data? Would you expect the tree to have (a) different root node(s) according with what you filter out? Eg one moment you have "King" as root, but as you filter him out his "child" nodes will be the new root nodes? Or would you still expect the correct tree structure to be shown irregardless of whether something is filtered? Because what if you want to use the where clause to "search" the tree? Is the structure of the tree not relevant?
I am also working an an apex plugin which offers ajax functionality with a tree component, but it's not simple. You have to work from the connect by statement, and thus you'll work with the level column, isleaf and connectbypath columns. A where clause generally messes up those plans big time. Coding around it would probably make it very complex, or maybe even having to ditch connect by in favour of iterating over each qualifying node manually. This'll be difficult for a third-party solution aswell, as you will somehow have to pass it the correct data (and it's not really a matter of jstree or dhtmlx tree or any other library).

Scott 02 December, 2013 16:42

Have a look at this post: http://tpetrus.blogspot.be/2013/01/working-with-tree-in-apex.html

- Scott -

Scott 02 December, 2013 16:42

Have a look at this post: http://tpetrus.blogspot.be/2013/01/working-with-tree-in-apex.html

- Scott -

Tobias Arnhold 02 December, 2013 20:23

Hi Tom, Hi Scott,

I guess Scott mentioned your blog Tom. Haven't seen this one yet. Great work.

About my select statement. You are right the select is a bad example I will redesign it. Did not put much work into it because I just wanted to visualise the customer request.

Cheers Tobias