Manipulate multiple tables from one tabular form

A tabular form enables you to process a number of rows in one go. On page submit all changed records of the tabular form will be inserted, updated or deleted from the subjected table.

Up until now there is a restriction, though: you can put only one tabular form on a page (apparently Apex 5.0 will let you use more than one tabular form on a single page). The reason for this restriction has to do with the way Apex processes tabular forms. There are however, various workarounds to this restriction. One such workaround is to use one tabular form to manipulate more than one table. In this post I will demonstrate how to create such a tabular form.

We will manipulate more than on table, for this we need to create a view that contains all the columns of the tables that we want to manipulate. This itself is straight forward and enables us to query records from multiple tables. However, it doesn’t let us perform inserts, updates or deletes. For that we need “instead-of-triggers” . Instead of triggers enable us to do inserts, updates and deletes on a view, i.e. to perform dml on the underlying tables.

Now we have multiple tables, joined in one view, which we can manipulate with one tabular form. Unfortunately, all columns of the view are visible in the tabular form, while they don’t belong to the same tables. To make sure that when running the page you only see the right –the for that time appropriate- columns, we need to give the columns a conditional display. You can set the conditional display with ‘edit page item’ > ‘conditions’. Which columns should appear at what time is of course entirely dependent of what your page should be showing at any given time.

This is all you need for a simple workaround to manipulate more than one table from a single tabular form. Note however that tabular forms rely heavily on rowids. This can be a problem, especially with views. To secure your page from processing, you could remove the Apex-generated page processes and replace them with your own procedures.

Good luck!