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!
Hi, Vincent.
ReplyDeleteThank you for posting this. Your custom function to update an entire tabular form in one go is really useful (and cool).
I tried your example and it works great. I realize that the point of your article is to demonstate updating tabular form in one go. However, for those wanting to use your technique in a production app, I would suggest adding a second dynamic action that fires upon changes to the SAL column and then stores the changed SAL value in its own variable. This way, if users happen to change the SAL value *after* checking the checkbox, then it will be the most recently modified SAL value that is updated by your PLSQL process. Currently, it is only the SAL value *at the time users select the checkbox* that is updated.
Another option is to make the SAL column read only so users are prohibited from modifying it.
As mentioned, your neat article is not intended to present an entire solution set. I just thought it might help readers understand how to incorporate your technique in a production app.
Thank you so much for sharing this technique. I loved it.
Elie
Hi Elie,
ReplyDeleteThanks for your reply and thank you for your suggestions.
I like your idea of adding an extra dynamic action to track changes in the SAL column. If doing so, you'd have to do keep in mind that users can also change salary of people who don't get a raise. So, you'd have to submit all changes to the SAL column prior to running the raise_sal procedure. Setting the SAL column read only would be a much simpler solution.
There are more shortcomings in this example. note that on paginating to the next set of rows, you lose all checked items, so effectively you only update those salaries of checked employees on the current page.
My idea was, as you mentioned to provide a simple example of a mechanism to manipulate your tabular form. The emp-table example I provided was in fact the most stripped down running version I could come up with. It should provide a starting point for more complex and robust table handling. I myself have used it, for example, in a transaction system, where I delete prospected transaction from one table, update them and insert them in another table.
I hope more people, like you, will be inspired by this blog and hopefully some will share their insights here as well. Again thank you for your insights.
Kind regards,
Vincent
Hi, Vincent.
DeleteI'm glad you liked my suggestions.
If I may add yet another ...
With respect to using your technique while paginating through multiple pages, I've successfully used Apex collections to store all tabular form rows on each page. As users go forward/backward through a tabular form, I save all of the current page's rows in the tabular form to my collection, marking newly added, updated, and deleted rows with the row status "NEW", "UPDATED", and "DELETED", respectively.
Finally, when users click a "SAVE ALL CHANGES" button on the page, I use a PLSQL procedure to process these rows: inserting/updating/deleting rows in the target table based on the collection row status.
Apex collections are quite useful structures when tabular forms are involved. Indeed, I've used them to create multiple tabular forms on a single page, something that even the most recent Apex version (4.2.1) cannot do.
Take care.
Elie