Two tabular forms, one page

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:

	SELECT apex_item.checkbox2(1, empno) check$01
	, apex_item.hidden(2, empno) empno
	, apex_item.text(3, ename) ename
	, apex_item.text(4, job) job
	, apex_item.text(5, mgr) mgr
	, apex_item.text(6, hiredate) hiredate
	, apex_item.text(7, sal) sal
	, apex_item.text(8, comm) comm
	, apex_item.text(9, deptno) deptno
	, apex_item.md5_checksum(ename,job,mgr,hiredate,sal,comm,deptno) checksum
	FROM emp

view raw apex_item_emp hosted with ❤ by GitHub

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:

	SELECT apex_item.checkbox2(11, deptno) check$01
	, apex_item.hidden(12, deptno) deptno
	, apex_item.text(13, dname) dname
	, apex_item.text(14, loc) loc
	, apex_item.md5_checksum(dname,loc) checksum
	FROM dept

view raw apex_item_dept hosted with ❤ by GitHub

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.

	begin
	for i in 1 .. apex_application.g_f02.count
	loop
	if apex_application.g_f02(i) is null or apex_application.g_f02(i) = ''
	then
	insert into emp
	( ename
	, job
	, mgr
	, hiredate
	, sal
	, comm
	, deptno
	)
	values
	( apex_application.g_f03(i)
	, apex_application.g_f04(i)
	, apex_application.g_f05(i)
	, apex_application.g_f06(i)
	, apex_application.g_f07(i)
	, apex_application.g_f08(i)
	, apex_application.g_f09(i)
	);
	else
	update emp
	set ename = apex_application.g_f03(i)
	, job = apex_application.g_f04(i)
	, mgr = apex_application.g_f05(i)
	, hiredate = apex_application.g_f06(i)
	, sal = apex_application.g_f07(i)
	, comm = apex_application.g_f08(i)
	, deptno = apex_application.g_f09(i)
	where empno = apex_application.g_f02(i)
	;
	end if;
	end loop;
	end;

view raw submit_emp hosted with ❤ by GitHub

The process for DEPT table is basically the same, the code will be:

	begin
	for i in 1 .. apex_application.g_f12.count
	loop
	if apex_application.g_f12(i) is null or apex_application.g_f12(i) = ''
	then
	insert into dept
	( dname
	, loc
	)
	values
	( apex_application.g_f13(i)
	, apex_application.g_f14(i)
	);
	else
	update dept
	set dname = apex_application.g_f13(i)
	, loc = apex_application.g_f14(i)
	where deptno = apex_application.g_f12(i)
	;
	end if;
	end loop;
	end;

view raw submit_dept hosted with ❤ by GitHub

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.

At this point we can do updates on both tab forms. The process for inserting records is there as well, so now is a good time to create our 'add row' function. The function will basically copy the last row of a table, empty all data and paste the row at the end of the table. Since this is a generic function that can be used on more than one page in your application, you should put the function in a JavaScript file that you include in your application. Here I simply put the function in the page header.

	<script type="text/javascript">
	function addRow(tableId) {
	/* declare some variables. */
	var thisTable = $('#'+tableId) // the table that will be affected
	, newRow = $(thisTable).find('tbody tr:last').html() // new row that we're going to add
	$(thisTable).find('tr:last').after('<tr>'+newRow+'</tr>') // place the new row after the last one in the table
	$('#'+tableId+' tbody tr:last input').each(function(){$(this).val('')}) // clear all values from the new row
	}
	</script>

view raw addRow hosted with ❤ by GitHub

The function expects a table ID as input. We gave our reports static ID's, but unfortunately APEX tables are wrapped in a number of div's and tables, so we need to add an ID to the exact table as well. This is done by a page load dynamic action.

	$('#empForm table table').attr('id','empTable')
	$('#deptForm table table').attr('id','deptTable')

view raw addIDs hosted with ❤ by GitHub

The dynamic action adds an ID to the table within a table within the table that we gave our static ID. With ID's added to the correct tables we can now create the dynamic actions that call the addRow function. To call the function we need two 'add row' buttons, one for each report. For simplicity I've created a dynamic action for each add row button, this could of course be done with one function as well, but then you need to determine which button called the function and thus to which table a row needs to be added. The dynamic actions will fire on click of the add row button and it will call the addRow function with the appropriate table ID. By now we can add rows to each report, so we can update and insert records.

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.

	var empNos = $('#empTable input[name="f01"]:checked').map(function(){
	return $(this).val()
	}).get().join(':')
	var deptNos = $('#deptTable input[name="f11"]:checked').map(function(){
	return $(this).val()
	}).get().join(':')
	$('#P17_EMPNOS').val(empNos)
	$('#P17_DEPTNOS').val(deptNos)

view raw mapPK hosted with ❤ by GitHub

'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:

	declare
	l_emp apex_application_global.vc_arr2 := apex_util.string_to_table(:p17_empnos);
	l_dept apex_application_global.vc_arr2 := apex_util.string_to_table(:p17_deptnos);
	begin
	for i in 1 .. l_emp.count
	loop
	delete from emp where empno = l_emp(i);
	end loop;
	for i in 1 .. l_dept.count
	loop
	delete from dept where deptno = l_dept(i);
	end loop;
	end;

view raw deleteProc hosted with ❤ by GitHub

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 application

Good luck!