15 November 2013

Tree with Checkboxes: Save the Data - JS Array to PL/SQL

In two previous blogposts I described a method to transform a regular APEX Tree component to a Tree component with checkboxes. The second blogpost described a way to retrieve the data from the database using JSON and AJAX and check the appropriate checkboxes. In this last part of the series on Tree with Checkboxes I will describe a method to save the checked values to the database.
In this blogpost I will assume that you created the APEX tree as described in the other two blogposts (the links to these blogpost are at the bottom).
The method that I used will involve the following:
  1. Database procedure to add a new value
  2. Database procedure to register the changed values
  3. Page process to call the first procedure (add values)
  4. Page process to call the second procedure (changed values)
  5. Before Page submit process to fill an APEX collection with the checked values

Database Procedures


Implementing the database procedures will not be part of this blogpost. The basic idea behind the stored procedures is to insert and update the data in the table.
For this blogpost I will assume you have created the procedures as required. The signature of the procedure that I used are as follows:
   procedure add_department(p_deptname      in varchar2
                           ,p_location      in varchar2
                           ,p_emp_arrayname in varchar2);
   procedure modify_department(p_deptno        in number
                              ,p_deptname      in varchar2
                              ,p_location      in varchar2
                              ,p_emp_arrayname in varchar2);
The values that are checked in the APEX Tree are saved in an APEX collection which is then used to insert or update the appropriate values in the table.
In the stored procedures the name of the APEX collection is passed in (the last argument) and the content of the APEX collection is retrieved using the available APEX views.
If this sounds like gibberish, leave a comment and I will address this in a later blogpost (no promises on when ;) )

Dynamic Action: Before Page Submit


Create a Dynamic Action with the event set to "Before Page Submit". The action that you want to perform is "Execute Javascript code". Add the following javascript code in the code section:
var lArray = [];
$('#catalog-tree div.tree')
   .find ('.checked,.undetermined')
   .parent()
   .each(
      function(){
          lArray.push(this.id);
});
apex.server.process('SAVE_EMPS',
                    {"f01":lArray}
                   );
The first part of the code (lines 1-8) will find all the checked checkboxes in the tree. In order to find the correct tree, the static ID of the tree is use. In this case the ID is "catalog-tree" (line 2). It will not only find the checked checkboxes, but also the ones that have the class "undetermined". Those are the ones with the filled checkbox, but not the checkmark. All the ID's are stored in a javascript array.
The second part of the code (lines 9-11) calls an AJAX process called "SAVE_EMPS" and passes in the javascript array of the checked checkboxes.

AJAX Callback


Create an AJAX Callback (in the processing section of the page, at the bottom) of type "PL/SQL". Choose "On Demand: Run this process when requested by AJAX" for the point of execution.
Enter the following PL/SQL code when prompted to do so:
begin
   if (apex_collection.collection_exists('P21_SELECTED_EMP'))
   then
     apex_collection.delete_collection('P21_SELECTED_EMP');
   end if;
   apex_collection.create_or_truncate_collection('P21_SELECTED_EMP');

   for i in 1 .. apex_application.g_f01.count() loop
      apex_collection.add_member(p_collection_name => 'P21_SELECTED_EMP'
                                ,p_c001 => apex_application.g_f01(i)
                                );
   end loop;
end;
The code above will be called from the dynamic action that we created earlier. The javascript array is "transformed" and stored in an APEX collection named "P21_SELECTED_EMP".

Page Process


The last part of the puzzle is to call the actual database procedure which writes the APEX collection to the database tables. This is a "regular" process (On Submit - After Computations and Validations) in the Page Processing section of the page.
begin
   department_pkg.modify_department(p_deptno        => :P21_DEPTNO
                                   ,p_deptname      => :P21_DNAME
                                   ,p_location      => :P21_LOC
                                   ,p_emp_arrayname => 'P21_SELECTED_EMP'
                                   );
end;

Bug in my example


Yes, there is a bug in my example. Not in the actual code or anything, but in the functionality... King, the president in the EMP table cannot be assigned to a department separately without all his subordinates being assigned to a different department.
The demo page does not use the actual EMP and DEPT table, so other examples on my demo pages do not reflect the changed emp-dept relations.

Links


Tree with Checkboxes: the first blogpost.
Tree with Checkboxes - Pre-select the data: the second blogpost
Demo

11 comments:

  1. Hi, Alex, your work is excellent. I followed your instruction and successfully created my page, even though I have no clue how java works. However, I have one problem with the update. I believe up to the AJAX Callback, everything is fine. But the Page Process seem to be unable to get the right stuffs from the collection. Look like the On-Submit page process is happening before the Before-Submit AJAX Callback, or the collection data is not properly populated. My solution is to remove the Page Process and move the update codes into the AJAX Callback PLSQL in the loop. And it works fine for me. Thank you very much. -- William

    ReplyDelete
  2. "The second part of the code (lines 9-11) calls an AJAX process called "SAVE_EMPS" and passes in the javascript array of the checked checkboxes. " <-- where can I find definition of "SAVE_EMPS" ?

    Andrew.

    ReplyDelete
    Replies
    1. Good point - that wasn't really clear in the post.

      The way to create SAVE_EMPS is described in the section labelled "AJAX Callback".

      Delete
  3. Thanks Alex,
    of course I also tried earlier to use the code described in section labelled "AJAX Callback", but without success.

    What helped in my case was to change the line:
    apex.server.process('SAVE_EMPS', {"f01":lArray});

    to below lines:

    var ajaxRequest = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=SAVE_EMPS', $v('pFlowStepId'));
    ajaxRequest.addParam('f01', lArray);
    var gReturn = ajaxRequest.get();

    Besides mentioned issue your articles were very helpful, thanks! :)

    Regards,
    Andrew.

    ReplyDelete
    Replies
    1. Thanks for the compliment :)

      Which version of APEX are you using? The htmldb_get-method is the "old" way of making AJAX calls.

      Delete
  4. Alex - I have been able to present all the pre-selected data. No spaces in the value for the records allows the correct dispaly.
    As the last piece in the puzzle, can you give an example of the PL/SQL procedure, modify_department, that updates the table for the selected check-boxes? Or point me to a suitable reference site?
    Thanks again

    ReplyDelete
    Replies
    1. sure, drop me an email and I'll send it to you: alexnuijten (at) gmail (dot) com

      Delete
  5. No, the button does a Page Submit. Because the Dynamic Action (DA) is "Before Page Submit", it will be triggered automatically. In this DA the AJAX process "SAVE_EMPS" is called, this will transfer the selected values to a Collection.
    The last piece of the puzzle is the Page Process which calls the database procedures based using the Collection.

    ReplyDelete
  6. Hi Alex Nuijten, it is posible to execute the PL-SQL when the checkbox is selected or unselected?

    Thanks, Salvador.

    ReplyDelete
    Replies
    1. Yes, you can but the jsTree is deprecated as of APEX 5.1 (see Release Notes)
      So I would change to the FancyTree plugin (FancyTree Plugin)

      Delete
    2. Hi Alex, Thanks for the references and fast response.

      Well, we have APEX 4.2 and there is no plan to migrate in this moment(maybe 1 or 2 years). I will apply your example. :)

      Delete