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:
- Database procedure to add a new value
- Database procedure to register the changed values
- Page process to call the first procedure (add values)
- Page process to call the second procedure (changed values)
- Before Page submit process to fill an APEX collection with the checked values
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 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:
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.
Tree with Checkboxes: the first blogpost.
Tree with Checkboxes - Pre-select the data: the second blogpost