I created a tabular report on a table and needed to write some custom code that does some DML based on which rows in the form are checked.
In this example I have a table based on the fifty states:
I want to update that table and set the BEEN_THERE column to YES or NO depending on whether I was there. So on the page I created a radio button group with a Yes and a No button.
In order to update the table, I want to set the radio button to Yes, and then check the states that I have visited.
To do this, I needed to create a page process that loops through the checked rows and executes an update statement for each, setting the BEEN_THERE column to whatever was selected in the radio button.
To loop through the report you can use the following syntax:
FOR i IN 1 .. apex_application.g_f01.count LOOP
… some code…
Then for each loop iteration, I need to update the current row.
SET been_there = :p21_yes_no
WHERE st = [the state value of the current g_f01 value]
You have to note that looping through the tabular report in this manner only loops through those rows that are checked! So if you checked the 5th and 7th row in the report, the first apex_application.g_f01() value will be 5, and the second will be 7.
So, apex_application_f01 holds the row number as it is displayed on the screen.
In the were clause I need to get the value of the ST column of where the loop currently is. The apex_application.g_fxx column variables are referred to positionally. Since the row selector check box is in position one, and the ST column in position two. I refer to the value of the ST column with apex_application.g_f02(). In order to get the ST value of the row where the loop currently is I need to use:
apex_application.g_f02( apex_application.g_f01(i) );
I know this is a little tricky, but it makes sense once you work through it. 🙂
So the code for the application process looks like this:
So that’s it.
Please leave a comment and subscribe.