17 September 2013

Tree with Checkboxes - Pre-Select Data with JSON

After you have modified the standard APEX tree to a tree with checkboxes (see this blogpost) you will probably need to find a way to load data from the database and show the values previously checked by the user.
In this blogpost I will show you how to load the data from the database using an AJAX-call and check the appropriate checkboxes in the tree.
The method described here retrieves the data uses the APEX_UTIL.JSON_FROM_SQL procedure and an AJAX call. This method can be used for many more things besides populating a Tree with checkboxes :)
For the rest of this blogpost, I will make the assumption that you have created a page where a Tree is present which are made up of checkboxes instead of folder-icons.
For my example I used the good-ol' DEPT and EMP table. For the demo-page I created a Form with a Report Based on the DEPT table. On the form-page I created an APEX tree using the region wizard. This tree was modified as per the method described in "the other blogpost".
This example will only check the boxes for the employees who work for the currently selected department. I know this is a contrived example, but hey...
There will be a follow-up blogpost to describe how to store the values in the database.

Step 1: The AJAX Call

For this to work, we will need two things:
  1. AJAX and the Callback
  2. On Demand Process
Let' start with number 1.
For this you will need create a little bit of javascript which is to be executed when the page loads. You can either do this in a Dynamic Action with where the event is "Page Load" or you can do this at Page Level in the Javascript section: "Execute when Page Loads". Which one you choose is up to you.
For a Dynamic Action: right click in the page rendering section, labelled "Dynamic Actions" and choose "Create".

Follow the wizard:
  1. Name: Check Department Emps
  2. Event: Page Load
  3. Action: Execute Javascript Code
The javascript that you will need is as follows:
var ajaxRequest = new htmldb_Get(null,&APP_ID., 'APPLICATION_PROCESS=GET_EMPS',&APP_PAGE_ID.);
ajaxRequest.addParam('x01',$v('P16_DEPTNO'));
gReturn = ajaxRequest.get();
if(gReturn){
    var obj = jQuery.parseJSON(gReturn);
    for (var i=0,cnt=obj.row.length; i < cnt; i++) {
      $('#'+obj.row[i].EMPNO+' a:first').removeClass('unchecked').addClass ('checked');
    }
};
get = null;
Line 1 creates the ajaxrequest object which references the Application Process called "GET_EMPS". This application process is defined on the page where the tree is, hence the last argument &APP_PAGE_ID.
On line 2 we add an argument to be used when we do the actual AJAX call. The value of the item P16_DEPTNO will be placed in argument "x01". When we make the AJAX call and the PL/SQL procedure is executed, the value of the argument can be found in APEX_COLLECTION.G_X01. (see the code below how we will use this information).
Line 3 makes the actual AJAX-call.
When the AJAX-call on line 3 is successful, lines 5 through 8 are executed. Because the PL/SQL procedure (GET_EMPS) will return a JSON object, it is parsed as such (line 5). The FOR Loop will find the appropriate employee in the tree and change the class associated with it.
The second part that you need to do is create an On-Demand process.
Right click on the "Ajax Callback" in the page processing section of the page. Make sure to name the Ajax Call "GET_EMP", otherwise the javascript can't find it. Follow the wizard and choose PL/SQL block.
Follow the wizard and enter the following data:
  1. Category of Process: PL/SQL
  2. Name: GET_EMPS
  3. Point: On Demand: Run this process when requested by AJAX
Enter the following code in the appropriate section:
declare
   l_sql varchar2(32000);
begin
   l_sql := '
select empno
  from emp
 where deptno = ' ||
            dbms_assert.enquote_literal(apex_application.g_x01);
   apex_util.json_from_sql(l_sql);
exception
   when no_data_found then   
       htp.p('{"row":[]}');
end;
On line 9 the packaged procedure APEX_UTIL.JSON_FROM_SQL is used to transform the result of a SQL statement to a JSON Object. Unfortunately this procedure takes the SQL statement as a string. Guarding against SQL Injection it is a good idea to use the DBMS_ASSERT pacakage to sanitize the user input.
The argument which is being passed down from the JavaScript code (x01) can be found in the APEX_APPLICATION.G_X01 collection.
When an exception occurs
Note that the Javascript code is not really fancy: when some of the child records are checked, the parent checkbox doesn't show the "square" indicating that only part of the children are checked. The javascript code provided in this article simply shows a check mark in the checkbox where there should be one.

As this blogpost is long overdue, the first article describing the transformation from a regular APEX tree to one with checkboxes was written in July, J. Eberhard has written a blog describing a different method of loading and saving data using an APEX Tree with Checkboxes in the mean time. You can find that blogpost here.
DBMS_ASSERT: Oracle Base on DBMS_ASSERT

12 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Alex,

    This is a great series of articles. I think this concept needs to be incorporated in APEX itself.
    I tried to use it in an application I am creating but the luck of javascript knowledge gives me a lot of headaches. Any help would be highly appreciated by me (and all those guys who do not know javascript as well)

    The problem I have is:

    I want a customer to sign up to as many product categories as he likes to receive updates by email when a change happens to any of those products
    The customer must be able to select as many categories as he likes at any node or at leaf level.
    When he wants to revisit to add or remove categories, I need to load all his selections on the tree so he can see at any time what is his current selections.

    The product table is using a tree structure of up to 4 levels deep defined with field "CODE_LEVEL" (Its about 25K English records which will be going up as we add more and more language translations and categories)


    CREATE TABLE "APP_PRODUCT_CATEGORIES"
    ( "CHAPTER" VARCHAR2(2),
    "SCHEDULEB" VARCHAR2(30),
    "COMMODITY_DESCRIPTION" VARCHAR2(1000),
    "UNIT_OF_QUANTITY" VARCHAR2(30),
    "CODE_LEVEL" NUMBER,
    "LANG" VARCHAR2(2),
    "SCHEDULEB_LANG" VARCHAR2(40) GENERATED ALWAYS AS ("SCHEDULEB"||'-'||"LANG") VIRTUAL VISIBLE ,
    "CODELEVEL" NUMBER,
    "PRODUCT_PARENT_CATEGORY" VARCHAR2(30),
    CONSTRAINT "APP_PRODUCT_CATEGORIES_PK" PRIMARY KEY ("SCHEDULEB_LANG") ENABLE
    )
    /

    CREATE INDEX "APP_PRODUCT_CATEGORIES_IDX1" ON "APP_PRODUCT_CATEGORIES" ("SCHEDULEB")
    /

    CREATE INDEX "APP_PRODUCT_CATEG_PRT_IDX1" ON "APP_PRODUCT_CATEGORIES" ("PRODUCT_PARENT_CATEGORY")
    /


    Then I have the table where I need to store the selections from the above table per customer (CREATED_BY)

    CREATE TABLE "APP_EMAIL_UPDATE_RULES"
    ( "ID" NUMBER,
    "SCHEDULEB" VARCHAR2(30),
    "CREATED_ON" TIMESTAMP (6) NOT NULL ENABLE,
    "CREATED_BY" VARCHAR2(255) NOT NULL ENABLE,
    "LANG" VARCHAR2(2),
    "SCHEDULEB_LANG" VARCHAR2(40) GENERATED ALWAYS AS ("SCHEDULEB"||'-'||"LANG") VIRTUAL VISIBLE ,
    CONSTRAINT "EMAIL_UPDATES_PK" PRIMARY KEY ("ID") ENABLE,
    CONSTRAINT "APP_EMAIL_UPDATE_RULES_UQ" UNIQUE ("SCHEDULEB", "CREATED_BY") ENABLE
    )
    /
    ALTER TABLE "APP_EMAIL_UPDATE_RULES" ADD CONSTRAINT "APP_EMAIL_UPDATE_RULES_FK" FOREIGN KEY ("SCHEDULEB_LANG")
    REFERENCES "APP_PRODUCT_CATEGORIES" ("SCHEDULEB_LANG") ENABLE
    /
    ALTER TABLE "APP_EMAIL_UPDATE_RULES" ADD CONSTRAINT "EMAIL_UPDATES_FK" FOREIGN KEY ("CREATED_BY")
    REFERENCES "APP_USERS" ("USER_ID") ENABLE
    /
    continued...

    ReplyDelete

  3. I created in APEX a tree (no language selection is implemented yet) using the following code:

    select case when connect_by_isleaf = 1 then 0
    when level = 1 then 1
    else -1
    end as status,
    level,
    "SCHEDULEB" || ' - ' || COMMODITY_DESCRIPTION as title,
    null as icon,
    "SCHEDULEB" as value,
    "COMMODITY_DESCRIPTION" as tooltip,
    'f?p=&APP_ID.:25:'||:APP_SESSION||'::::P25_SCHEDULEB:'||"SCHEDULEB" as link
    from "#OWNER#"."APP_PRODUCT_CATEGORIES"
    start with "PRODUCT_PARENT_CATEGORY" is null
    connect by prior "SCHEDULEB" = "PRODUCT_PARENT_CATEGORY"
    order siblings by "SCHEDULEB"



    I was able to apply your 1st article (http://nuijten.blogspot.nl/2013/07/apex-tree-with-checkboxes.html) to create checkboxes on the tree.

    How do I implement the Pre-Select Data with JSON (http://nuijten.blogspot.nl/2013/09/tree-with-checkboxes-pre-select-data.html)

    and how to implement the saving of the selections (http://nuijten.blogspot.nl/2013/11/tree-with-checkboxes-save-data-js-array.html)

    Thank you

    ReplyDelete
  4. Not sure why it says unknown as my username. Although I use my Google account!

    Thank you

    Yannis

    ReplyDelete
    Replies
    1. Hi Yannis,
      Thank you for your comments.
      From what I understand you have managed to create the tree with the checkboxes (from the first blogpost) and you want to implement the other two parts (loading and saving the data).
      Loading the data is described (step by step) in this blogpost. Just continue on the page where you created the Tree with checkboxes and follow the steps.
      Saving the data is described in http://nuijten.blogspot.nl/2013/11/tree-with-checkboxes-save-data-js-array.html
      Continue on the same page and follow the steps. Of course you will also need to create the database procedures, but that is also described in the last blogpost.

      Alex

      Delete
  5. Excellant article.
    But on attempting to run the example APEX pages at

    https://apex.oracle.com/pls/apex/f?p=47888:15:0::NO:::

    the check boxes do not appear - only folders.
    Am I missing something?

    ReplyDelete
    Replies
    1. You did click on one of the pencils to go to page 16, right?
      Which browser are you using? I'm using Chrome and just checked that my demo page still works, and it does.

      Delete
    2. I am using IE 9.0.31, as provided with our SOE.
      FireFox 32.0.3 works okay.

      But wrt this example, I am having difficulties with the alignment of your instructions, and the APEX 4.2.1.00.08 environment. In particular the settings for the 'Dynamic Actions' and AJX Callbacks. I am incrementally adding the tree-with-checkboxes-pre-select with static data (select '123' as EMP from Dual;) before using my real situation. But I cannot get the check boxes enabled.

      Any suggestions or web links for the best way to debug? I am only just learning APEX and javascript. All the other components for my simple application have used the OOTB APEX capabilities.

      Delete
    3. Alex - just been checking the versions of software, and the SOE is
      .../widget.tree.min.js?v=4.2.1.00.08;

      your version is .../widget.tree.min.js?v=4.2.5.00.08

      So I will check in my later, local version of APEX before going forward.

      Delete
    4. Further update - looks like IE9 is okay - the error may be associated with the values I have used for the Tree - mine had spaces :-)
      So I have created unique, non-spaced identifiers. I will continue working with your example and provide feedback (on Monday)...

      Delete
  6. Alex,
    Will this above method of pre-select-data will work with "checkboxes with LOV"??

    Suppose say, Item P1_CHECKBOX is created using ("SELECT ENAME, EMP FROM EMP") which gives multiple checkboxes.

    ReplyDelete
    Replies
    1. The method described is specific to the Tree component, I haven't tried this with other components.

      Delete