Game, Graphics and Web Developer

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:

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:

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:

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:

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.

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.

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:
    e.g.) apexTreeCheckbox.init(‘#EMPLOYEE_TREE’, ‘#P100_EMPLOYEE_CHECKBOX’);