Tabular forms are very effective when it comes to mutating (table) data. Unfortunately APEX allows you to use only one tabular form per page. Lucky for us, there are ways to get more than one tab form on a page. In my demo application I have one example of two iFrames in one page; each iFrame contains a tabular form page. This is about the simplest way to get two tab forms on a page. Sadly this way you will have a set of buttons for each page. A more advanced way is to create the tabular forms yourself, instead of using the APEX generated tab form. There are a number of examples on how to do that, see amongst others Denes Kubicicek and Martin D'Souza.
In this post I would like to add to the stack, an example of how you can easily create tabular forms yourself. In the example I will use the apex_item package to create input fields for each report column. On top I'll use jQuery to add rows and in preparation of the delete process. The use jQuery for adding rows to the form shows a bit more of what apex is doing in the background when you generate an apex_item field. And, best of all, one tabular form is going to be an interactive report, with sorting and filtering options.
To start we create an interactive report on the EMP table, but instead of doing a normal "select * from EMP", we need to call each column with apex_item functions. The query will look as follows: The number in the apex_item that precedes the column name, refers to the f_array the column will be mapped to. Make sure that each column is set to 'Standard Report Column' in the report attributes section. We also add a static ID to the report, this is needed later on when we create the add row function. Our report on EMP is now ready.
Next, we create the DEPT report. This is done the same way we created the EMP report, only now we choose a standard report (APEX allows you to create no more than one interactive report per page). Again, make sure each column is set as 'Standard Report Column' and provide a static ID to distinguish your report. The query will look like this: Note that we already used f_01 to f_09 for our EMP report, so we have to choose another range of numbers for the apex_items of our DEPT report. Here I used 11 to 14.
Now that we have two reports with input fields, we need to create a procedure to handle the inserts and updates on the EMP and DEPT tables. You could do this in one process, but here I made one insert/update process for EMP, and one for DEPT. That way it's easier to see what's going on. First we create a submit button. Then we create a page process that fires on submit, after computations and validations. In the process we need to loop over the f_array and map each f_item with the correct table column, so f_02 is EMPNO, f_03 is ENAME, etc. The process for DEPT table is basically the same, the code will be: Note that I've put list tags around the success message. We will use one submit button that triggers both processes, with list tags both success messages will show better. Make sure that you add a button condition to the processes so we only do an update or insert when the button 'submit' is pressed.
The last thing we need to do is make create a delete process. We want to delete all rows we've checked with the report checkboxes. The checkboxes of the EMP table and the DEPT table all hold the primary key values of each table row, so it makes sense to use that. We'll create a dynamic action that looks for checked checkboxes and store their corresponding values in a hidden page item. The dynamic action is fired when a checkbox of either table is clicked. 'P17_EMPNOS' and 'P17_DEPTNOS' here are our hidden items. We store our primary key values in these items as colon delimited strings. The delete process is plain simple, now that we have our primary key values. We use apex_util.string_to_table to create a vc_array of pk values and use that for a delete: Now we only need to create a delete button and make sure the delete process only fires when the delete button is pressed.
That was the last step in the way. We now have one page with tow tabular forms. One tabular form is an interactive report. Each report has its own 'add row' button, page the page submit buttons control both tables. As I wrote at the beginning a used jQuery to add a row to a table because it helps to show what APEX is doing when you create apex_items: it simply generates the html for an input field. The f_xx number is used as name attribute for each input field. Note that this ‘add row’ function works well, but since it copies the html of the previous row, it only works if your report has at least one row to begin with. If you create a report on an empty table, there would be no table row to copy the html from. For the interactive report it serves to tell that all input fields are rendered as strings, even the numeric fields. Therefore aggregations on columns -e.g. the sum of salaries per department- won't work. Despite these issues, being able to put more than one tabular form on a page can be a great enrichment for your application. You can check a working example on my demo applicationGood luck!