Sunday, 8 September 2013

Oracle APEX 4.2 - Saving a tree's state

One of the questions raised in a recent Oracle APEX beginner's course I was running, concerned the tree structure. We had just created two regions on a page - a tree region based on the EMP table and a form based on the same table. The idea was that when the user clicked on an employee's name the form automatically populated with that employee's details - giving me an opportunity not only to talk about trees but also the LINK option.  So we all ended up with a page similar to the one below.

That all worked fine...and then the question came...."when I click on an employee, the form populates but the tree refreshes and all my tree nodes are collapsed except for one. How can I save or cache the tree so that when the page refreshes the tree doesn't alter?"

Now in older versions of APEX (ie anything before 4.0 if I remember rightly) we used to have to write a lot of code to do this. However we are now in the days of APEX 4.0 (or higher) and the tree structure uses jsTree, a JavaScript-based tree component that features, amongst other things, optional state saving and it is sooooooooo easy to implement.

All you need to do is edit your tree. Go to the 'Tree Attributes' page and set the 'Selected Node Page Item' to a page or application item that you wish to hold the selected node value e.g. P20_EMPNO as in my example below. Now when you select a node, and return to the tree page, the tree will expand to that last selected node.

The reason I picked that particular item is because it already exists on my page  - as a hidden item that is part of the form. It holds the primary key coulmn value so is already in the LINK column in my SQL query ie

Hey presto - a happy delegate :)

1 comment:

  1. This is exactly what I am looking to do but I seem to be failing somewhere.

    My tree structure code is as follows...

    select case when connect_by_isleaf = 1 then 0
    when level = 1 then 1
    else -1
    end as status,
    "NAME" as title,
    null as icon,
    "EMAIL" as value,
    'Click to select ' || "NAME" as tooltip,
    'f?p=&APP_ID.:5:&APP_SESSION.::::P5_MANAGER_HIDDEN:' ||EMAIL as link
    from "#OWNER#"."ULA_USERS"
    start with "EMAIL" = (select nvl(proxy_as, V('APP_USER')) from ula_users where email = lower(V('APP_USER')))
    connect by prior "EMAIL" = "MANAGER_EMAIL"
    order siblings by "NAME"

    Basically giving a tree structure of your direct reports and their direct reports.
    The link between click and report showing the values works fine but it won't save the state of the last click.

    Another thing I've noticed is that I can't use the "Expand All"/"Collapse All" buttons.

    Any ideas?

    My hidden item is in another region than my report and my tree.