Oracle APEX: Making a Checkbox Tree

Let me start by saying I love APEX Interactive Reports. It is the one aspect of APEX that allows me to rapidly churn out various reports that can then be modified by end users. However, there are end users, and then there are end users. Not every user can be expected to pick up all the nuances of the Interactive report grouping and filtering features. With that in mind I was tasked with creating a more easily configurable report, and one of the fields that needed to be filtered on was Departments. But Departments are further organized into Divisions, and we needed the ability to easily pick all or some departments in a particular Division.

Creating a Tree Region

There are a couple of multi-select items built into APEX. These are okay, but we have over 50 “Departments” so the list can be rather overwhelming and take up a lot of space, or necessitate a lot of scrolling. I fooled around a bit and came across the “Tree” Region. This seemed promising. I liked the fact that you can expand and collapse each section, which seemed to work for what I was going for. The first trick was figuring out the ridiculous sql for the query. I wanted a root level tree item for all Departments/Divisions, separate tree items for each Division, and then single tree entries for each Department. After a bit of googling I came up with the following 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,  
       null as link  
from  
(
select 'SCHOOL' item_type,  
       null label,  
       'S' id,  
       null parent,  
       'All Departments/Orgs' name,  
       null tooltip,  
       null link  
from dual
union all
select distinct 'DIVISION' item_type,  
       null label,  
       'DIV_' || to_char(division.division_code) id,  
       'S' parent,  
       division.division_descr name,  
       null tooltip,  
       null link  
from division
union all  
select distinct 'DEPARTMENT' item_type,  
       null label,  
       department.dept_code id,  
       'DIV_' || to_char(department.division_code) parent,  
       department.dept_descr name,  
       null tooltip,  
       null link  
from department
)  
start with parent is null  
connect by prior id = parent  
order siblings by name

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,

null as link

from

(

select 'SCHOOL' item_type,

null label,

'S' id,

null parent,

'All Departments/Orgs' name,

null tooltip,

null link

from dual

union all

select distinct 'DIVISION' item_type,

null label,

'DIV_' || to_char(division.division_code) id,

'S' parent,

division.division_descr name,

null tooltip,

null link

from division

union all

select distinct 'DEPARTMENT' item_type,

null label,

department.dept_code id,

'DIV_' || to_char(department.division_code) parent,

department.dept_descr name,

null tooltip,

null link

from department

)

start with parent is null

connect by prior id = parent

order siblings by name

The trick here is to just do a bunch of unions and make sure that each child is hooked up correctly to its parent by setting the parent id. For instance if I wanted to travel down to the employee level I would just add another union query like the following:

union all  
select distinct 'EMPLOYEE' item_type,  
       null label,  
       employee.employee_id id,  
       to_char(employee.department_code) parent,  
       employee.employee_name name,  
       null tooltip,  
       null link  
from employee

union all

select distinct 'EMPLOYEE' item_type,

null label,

employee.employee_id id,

to_char(employee.department_code) parent,

employee.employee_name name,

null tooltip,

null link

from employee

So after getting my Department tree query all squared away this was the end result:

Adding Tree Checkbox Logic

So this looked nice and it functioned just how I wanted it to. The next step was to add some checkboxes. So I gave the tree region a static id in APEX (passed in as treeDomSelector) and then started in with the guerilla javascript:

//create all the checkboxes for each tree item
$(treeDomSelector + ' li').each(function () {
    var $this = $(this),
        $checkbox = $('<input type="checkbox" class="apexTreeCheckbox">');
    $checkbox.attr('data-tree_id', $this.attr('id'));
    $this.prepend($checkbox);
});

//create all the checkboxes for each tree item

$(treeDomSelector + ' li').each(function () {

var $this = $(this),

$checkbox = $('<input type="checkbox" class="apexTreeCheckbox">');

$checkbox.attr('data-tree_id', $this.attr('id'));

$this.prepend($checkbox);

});

So now I had checkboxes!

The next thing to do was to make sure that clicking on each checkbox would have the proper effect in the tree hierarchy. This really involved three checks:

If the parent checkbox is changed the the child checkboxes match
If a child is unselected than set any parents (and grandparents) to unselected
If all of a parent’s children are selected then set the parent as selected

So to do this I just added a click event on the checkboxes with the following:

//setup click event on the tree item checkboxes
$(treeDomSelector).on('click', '.apexTreeCheckbox', function () {
    var $this = $(this),
        $children = $this.parent().find('input.apexTreeCheckbox');

    //make all children checkboxes match the parent
    $children.prop('checked', $this.prop('checked'));

    //if unselecting then make sure parent checkboxes are unselected as well
    if ($this.prop('checked') === false) {
        $this.parentsUntil(treeDomSelector, 'li')
            .children('input.apexTreeCheckbox').prop('checked', false);
    }

    //if all children tree items are checked then check the parent
    $(treeDomSelector + ' li:not(.leaf)>input.apexTreeCheckbox').each(function () {
        var $this = $(this);
        if ($this.parent().find('li.leaf>input.apexTreeCheckbox:not(:checked)').size() === 0) {
            $this.prop('checked', true);
        }
    });
});

//setup click event on the tree item checkboxes

$(treeDomSelector).on('click', '.apexTreeCheckbox', function () {

var $this = $(this),

$children = $this.parent().find('input.apexTreeCheckbox');

//make all children checkboxes match the parent

$children.prop('checked', $this.prop('checked'));

//if unselecting then make sure parent checkboxes are unselected as well

if ($this.prop('checked') === false) {

$this.parentsUntil(treeDomSelector, 'li')

.children('input.apexTreeCheckbox').prop('checked', false);

}

//if all children tree items are checked then check the parent

$(treeDomSelector + ' li:not(.leaf)>input.apexTreeCheckbox').each(function () {

var $this = $(this);

if ($this.parent().find('li.leaf>input.apexTreeCheckbox:not(:checked)').size() === 0) {

$this.prop('checked', true);

}

});

A tricky part about this code was to make sure that I was at the “leaf” level (e.g. innermost item which has “leaf” class) when checking if all children had been clicked.

Linking Tree Region to Checkbox Page Item

So my tree was working great! Now I just needed to save it somewhere. In developing in APEX I have learned that you never want to stray too far from the standard way of doing things. So I decided to go with a hidden page item in order to save the state to the sission.

Now I needed to link the Hidden Page Item and the Tree region, so I added the following to the Tree Checkbox click event (listed above) to make sure that when the tree checkboxes changed, the Checkbox Page Item checkboxes would be updated.

var ids = [];
//loop through each check box and see if it is checked
$(treeDomSelector + ' li.leaf>input.apexTreeCheckbox:checked').each(function (index) {
    ids[index] = $(this).attr('data-tree_id');
});
$(itemDomSelector).val(ids.join(":"));

var ids = [];

//loop through each check box and see if it is checked

$(treeDomSelector + ' li.leaf>input.apexTreeCheckbox:checked').each(function (index) {

ids[index] = $(this).attr('data-tree_id');

});

$(itemDomSelector).val(ids.join(":"));

The last thing I needed was to initialize the Tree Region Checkboxes with the values that exist in the Hidden Page Item when the page loads.

 var selectedItems = $(itemDomSelector).val().split(":");
    
//loop through each check box and see if it is checked
$(treeDomSelector + ' input.apexTreeCheckbox').each(function () {
    var $this = $(this);
    if (selectedItems.indexOf($this.attr('data-tree_id')) >= 0) {
        $this.prop('checked', true);
    }
    else {
        $this.prop('checked', false);
    }
});

var selectedItems = $(itemDomSelector).val().split(":");

//loop through each check box and see if it is checked

$(treeDomSelector + ' input.apexTreeCheckbox').each(function () {

var $this = $(this);

if (selectedItems.indexOf($this.attr('data-tree_id')) >= 0) {

$this.prop('checked', true);

}

else {

$this.prop('checked', false);

}

});

Voila! I had my Checkbox Tree!

Reusing the APEX Checkbox Tree

Since I will probably need to use this again, I went ahead and packaged up this code so that it could be reusable both for me and for others. So instead of writing all this code in each page you can just include the file and after the page loads run something like apexTreeCheckbox('#DEPARTMENT_TREE', '#P100_DEPARTMENT_CHECKBOX') . To get the latest version of the packaged code just view the gist on my github account here.

The quick summary of using it in a page is the following:

Include the code in your page by editing the page and including it in the “Javascript Function and Global Variable Declaration” or linking to it in the “Javascript File URLs”ne”.
Create a new “Tree” region and make sure the defining query returns the same uniquely identifying id for the innermost tree items
Assign a static_id in the Tree Region “Attributes” section (e.g. EMPLOYEE_TREE)
Create a hidden item (e.g. P100_EMPLOYEE_CHECKBOX)
Link the Tree and Item by adding the following to the page javascript “Execute when Page Loads” or a dynamic action set to run when the page loads:
apexTreeCheckbox.init(‘#[TREE_STATIC_ID]’,’#[ITEM_ID]’)
e.g.) apexTreeCheckbox.init(‘#EMPLOYEE_TREE’, ‘#P100_EMPLOYEE_CHECKBOX’);