APEX - Can a Tabular Form with its MRU functionality have filtering functionality like an Interactive Report?

Question

What I really need is a Tabular form that allows me to update multiple rows at a time while being filterable like an Interactive report. Any chance this is even remotely possible?

I would also like to hijack the row checkboxes on a tabular form that appear when the 'delete' functionality is activated and use them to select which rows get assigned a to a group based on a common attribute. (i.e. My table contains parts in my inventory and I need to be able to assign parts with common attributes to a group for processing)

Perhaps a group-by function that creates a new row in a 'Group' table with the group as the PK and the parts assigned to that group as a list or something...?

Thoughts? I am kind of at a loss...

Answer 1

It's really not that hard :) You can easily transform an IR into a pseudo-tabular form. And even though there are always more stylish and elegant solutions, those usually involve lots of javascript and/or plugins. They're nice, but not always what you want or need of course.

So how to manipulate your output? Use the APEX_ITEM api!

Quick example. I have an ir built on emp. I added a checkbox and textbox.

select empno, ename, deptno, 
       apex_item.checkbox(1, empno) empno_selected, 
       apex_item.text(2, ename, 10, 10) ename_edit
from emp

Don't forget: in the column attributes, set Display text as to Standard Report Column. If you don't, the output will be in plain text. Since apex_item generates html code, you don't want the plain text of course :)

Now, to be able to perform DML based on the actions you do in those generated fields, you will need a process. Let me start off by pointing out though that the generated items are stored in application variables, namely in arrays in APEX_APPLICATION. Take note of the behaviour of checkboxes: only ticked boxes will have their value stored in the array!

As an example, i've made this small On Submit process (also adding a SUBMIT button on the form to actually perform the submit...)

for i in 1..apex_application.g_f01.count
loop
insert into empselected(empno, selectiondate, ename_changed)
values(apex_application.g_f01(i), sysdate, apex_application.g_f02(i));
end loop;

This will loop over the records with the checkboxes ticked, and insert them into some table. For example, i ticked the box with KING and edited the textfield. See record nr 2 (1 is from a previous action ;))

It's not all the way there yet though. You still miss out on the functionality of a tabular form and it's processes, and things like optimistic locking. If you want to stay with tabular forms, you can also, for example, check out this link. There have also been some questions here about writing your own mru processes etc, like this one ;)

Answer 2

It is possisble, using the same tabular form.

1. Create an item (text item) in the tabular form region.
2. Create a submit button (Create a button displayed among this region's items)
3. Modify the where clause in the tabular form region source

For Example, you need search by customer name:

WHERE lower(CUSTOMER_NAME) LIKE '%'||lower(nvl(:PXX_SEARCH,CUSOTOMER_NAME))||'%'

If you need to search for other field only add other condition to the where clause

WHERE (
     lower(CUSTOMER_NAME) LIKE '%'||lower(nvl(:PXX_SEARCH,CUSOTOMER_NAME))||'%'
     or lower(CUSTOMER_address) LIKE '%'||lower(nvl(:PXX_SEARCH,CUSOTOMER_NAME))||'%'
)

Simple and use the same tabular form.