Sunday, 1 December 2013

Overflow region in Tabular Form

A while ago I wrote a blog describing how you can add a detail section to a table row. The idea consists of a standard or interactive report on one page and a detail report or form on another page. An iframe is used to display the detail section in line with the master report.

This works for standard or interactive reports. For a tabular form on the other hand, this is not quite suitable, as the data from the master and the overflow details will be submitted separately. In this post I present an alternative solution to add an overflow with details in a tabular form. The general setup consists of a view that selects all the columns we need to include, both for the master tabular form as well as the overflow details. For the insert/update/delete operations we need to create instead of triggers on our view. Next we need a dynamic action that will place all the details columns in an overflow row, and last we need a dynamic action to show and hide the overflow rows.

As an example we’ll create a tabular form on the good old EMP table and place the accompanying DEPT data in an overflow row. For this we first need to create a view on EMP and DEPT:

We now have a view on both the EMP and the DEPT table. To allow for DML on the view we need to create instead of triggers. We’ll create three separate triggers: one for inserts, one for updates, and one for deletes.

At this point we are able to create a tabular form on EMP_DEPT_V which will show all columns of the view and will allow to add-, change-, and remove rows. Our next step will be placing the DEPT columns in a separate row under the EMP columns. For this we need a piece of JavaScript that will look for the DEPT columns in each table row, pick them up and put them in a separate row, and place that row after the original table row:

We want the JavaScript to run as soon as the page loads, so we create a page load dynamic action of type JavaScript and place the above code in that dynamic action. The last thing we need to do is create a dynamic action that will show and hide the overflow details. We’d want the details to show when the checkbox of that row is checked, and hide the details when the checkbox is not checked. We can create a dynamic action that responds on a click on a table row and checks if the checkbox of that row is actually checked:

Dynamic action to create overflow for tabular form

In this dynamic action we can add a true action that will show the overflow row when the checkbox is checked:

In the same style we can create a false action that will hide the overflow row when the checkbox is not checked:

Since we want the overflow to be hidden initially, we need to check the “Fire on page load” attribute in the false action (but not in the true action!):

Dynamic action false action

With this last step our page is ready and should be working. As always, you can watch an example in my demo application . When implementing this setup, keep a few things in mind: this setup is limited to one detail row per master row, or analogue to our example, we can show the department details for an employee, but we can’t show all employee details for each department. Also the JavaScript used to build the overflow row is not easy to reuse, all columns are handled separately. This is to better show what the JavaScript is actually doing. A better solution would be to pass all desired columns as an array to a function that builds a row based on the column names specified in the array. The great benefit on the other hand is that this rather simple set up will only relocate the detail columns and leave everthing else in place. you can, for example, still reference the overflow columns using the their fxx_ array.

For more information on instead of triggers you can consult the Oracle documentation.

Enjoy!