Tuesday, 4 June 2013

Custom multi row processing using tabular form

In this post I will explain how you can perform a custom function or procedure on multiple rows in a tabular form. In APEX, a tabular form lets you do multiple inserts or deletes using the ‘MULTI_ROW_INSERT’- and ‘MULTI_ROW_DELETE’-buttons. You can also do updates on multiple records, but you need to alter each record individually. Doing the same update routine on a number of records, would require you to set each new value by hand, before doing the submit. Wouldn’t it be great if you can do an update on multiple selected rows in one page submit? In the following example I will show you how you can update the salary of a number of selected employees. The example consists of a tabular form on the standard ‘EMP’ table.
On the tabular form page I’ve put a hidden item to store the primary key values of the selected rows, in this case ‘EMPNO’.
To fill the page item with the primary key values of the selected rows, I use a dynamic action. The dynamic action will fire on every change in the tabular form region. This way every time you check or uncheck a checkbox, the dynamic action will evaluate which rows are selected.
Using jQuery, I fetch the selected values. Note that I concatenate  all the values to one string delimited by a colon.
In my example I perform the update routine as a page submit, so I need two more things:
  • a region button to submit the page
  • an after submit page process. 
The button is in this case a standard button that performs a page submit, here I’ve called the button ‘RAISE_SAL’. Last, in the after submit process, I will do the actual update of the employee salaries. The selected employees will get a standard raise of ten percent. In the page process I use the ‘APEX_UTIL.STRING_TO_TABLE’ function to put the concatenated EMPNO’s into an array.  In the loop the ‘SAL’-column of the EMP table is updated for all the EMPNO’s from the array.
And that wraps it up. Now you have a tabular form with which you can update multiple rows with the same update routine in one go. To see a running version of the above example, click here. Enjoy!