Wednesday, March 20, 2013

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:




14 comments:

  1. Hi,

    First, congratulations for your post. Very good.
    I did all the steps, but I didn´t understand where I need to put the last step. Can you help me?

    Thanks

    bsalvador

    ReplyDelete
  2. Thanks!
    The last step (setting the value of Pn_SELECTED_NODE) is done on the Branch "Go To Page n".

    ReplyDelete
    Replies
    1. Hi Ino, I have another question. I put the item Pn_SELECTED_NODE visible, only to test if it´s receiving de value when I click on the tree, but it isn´t updating. It´s showing always the first one node. Javascript should update it right?

      About the Branch "Go to Page n", I didn´t understand the meaning of P1_SELECTED_NODE. I thought I had to put Pn_SELECTED_NODE to "set items" and id to "set values". Could you explain it to me?

      Thanks!

      Delete
  3. I hope you understand that in "Pn", n is the page number. In your code, you have to put your actual page number instead of n. For me, n was 1.

    ReplyDelete
  4. Hi,
    I have an issue in getting the html tags or javascript function calls parsed. They just get displayed as plain text in the tree report. Could you please help if there is something that I can do to make the html tags or javascript calls work

    ReplyDelete
  5. Hi Ino,

    I am attempting to duplicate what you have done here but can't seem to get it working. When I click on an employee node I can see the department number and employee number being placed into the P3_SELECTED_NODE ( I made it a text item to test with). Unfortunately the report is not refreshing.

    I have confirmed that the Enable PPR is set to "Yes".
    The tabular form query was cut and pasted into the query box and the Pn was changed to P3.

    Do you have any thoughts on what I might look at next?

    Tom

    ReplyDelete
  6. Hi Tom,

    The issue may be this:
    ... add a hidden page item Pn_SELECTED_NODE to the page. It is important is to set Value Protected to No for this item.

    Check the "Value Protected" property.

    ReplyDelete
    Replies
    1. I've got that all setup already. I even went and found Scott Wesley's blog and tried setting one up based on it too. I've got to be missing something but for the life of me I can't figure out what.

      Tom

      Delete
    2. As with my post below for Kenneth, look at the session state.
      Also, check if you see the correct Javascript call when you hover the cursor over an employee name.

      You can look at my page here:
      apex.oracle.com/pls/apex/f?p=73615:LOGIN_DESKTOP
      Login with demo/demo

      Delete
  7. I have the same issue done everything mentioned here. Still have not be able to get the tabular form to populate. One think I found pecular was that when I put the following code in the tabular form. the entire table displayed instead of the single row.
    select
    "ROWID",
    "EMPNO",
    "ENAME",
    "JOB",
    "MGR",
    "HIREDATE",
    "SAL",
    "COMM",
    "DEPTNO"
    from "#OWNER#"."EMP"
    where "EMPNO" = :P12_SELECTED_NODE or :P12_SELECTED_NODE IS NULL

    I can see that the refresh is working, and I can see the data in P12_selected_node is there, but when the query fires P12_SELECTED_NODE appears to be null.

    ReplyDelete
    Replies
    1. Kenneth

      Because of "or :P12_SELECTED_NODE IS NULL" it will retrieve all rows if this condition is TRUE.

      I don't know what you are missing. If you click on "Session" in the bottom toolbar when running the page, you can see the session state of P12_SELECTED_NODE. It should show you a value. If not, you are not properly setting the session state.

      Delete
  8. Figured it out. on the tabular form you will need to put Pn_Selected_Node in the "Page Items to Submit" found just under the Region Source. the problem was that a page item is only held in memory and therefore once rendered the value in memory is gone. in order for the value to persist it needed to be submitted. Hope it helps.

    ReplyDelete
    Replies
    1. Thanks for the update Kenneth. I have included an extra screen shot to show this.

      Delete