Using apex_item.checkbox2 with multiple identifiers
Normally, in a report you can add a checkbox to select records like this:
select apex_item.checkbox2(1,x.id) as sel ,x.mycol ,... from mytable x
And process the selected records using a process like this:
for i in 1..apex_application.g_f01.count loop l_id := apex_application.g_f01(i); -- ... process ... end loop;
Since we have set the value of the checkbox to the record ID we can just get that ID from the resulting
g_f01 array. What if we need multiple columns in our processing? There are a few approaches we could use:
Option 1. Re-query the table to get the corresponding data for the record ID
This is possible as long as the record ID is a unique identifier for the results in the report. A downside is that this involves running an extra query to get the corresponding data which might add a performance problem. An advantage is that the query can bring back as much data as we need – so if we need more than, say, 6 or 7 columns, this would be a reasonable approach.
Option 2. Concatenate the extra data into the checkbox value
select apex_item.checkbox2(1, x.col1 || ':' || x.col2) as sel ,x.mycol ,... from mytable x
This requires parsing the value in the process, e.g.:
for i in 1..apex_application.g_f01.count loop l_buf := apex_application.g_f01(i); l_col1 := substr(l_buf, 1, instr(l_buf,':')-1); l_col2 := substr(l_buf, instr(l_buf,':')+1); -- ... process ... end loop;
Option 3. Add extra hidden items to hold the data
select apex_item.checkbox2(1,rownum) || apex_item.hidden(2,rownum) || apex_item.hidden(3,col1) || apex_item.hidden(4,col2) as sel ,x.mycol ,... from mytable x
Note: using “rownum” like this allows selecting the data from the row in the report, even if the underlying view for the report has no unique values that might be used.
Processing involves getting the selected rownums from the checkbox, then searching the hidden item (#2) for the corresponding rownum. This is because the
g_f01 array (being based on a checkbox) will only contain elements for the selected records, whereas the
g_f03, etc. arrays will contain all elements from all records that were visible on the page.
for i in 1..apex_application.g_f01.count loop for j in 1..apex_application.g_f02.count loop -- compare the ROWNUMs if apex_application.g_f02(j) = apex_application.g_f01(i) then l_col1 := apex_application.g_f03(j); l_col2 := apex_application.g_f04(j); -- ... process ... end if; end loop; end loop;
This approach helped when I had a report based on a “full outer join” view, which involved multiple record IDs which were not always present on each report row, and I had multiple processes that needed to process based on different record IDs and other data, which would have been too clumsy to concatenate and parse (as per option #2 above).
17 March 2021 - 11:48 pm
Nice one Jeffrey. This was very helpful