I’m sure many of you are like me and are anxiously awaiting the production release of APEX 5.1 that includes the new data grid control. The ability to effectively create pages with multiple tabular forms to handle multiple child tables and/or master-detail-detail relationships is a necessity for me and in the meantime I’ve had to get creative and come up with a workaround.
One approach is to use reports on your page to display the multiple detail tables and then launch edit pop-ups from each of the reports to edit the detail records. This works great in some instances but for a particular project the users wanted to be able to directly enter the data for the parent and all of the detail records on the same page without having to invoke multiple pop-ups.
Another approach would be to have multiple regions where the region source is a PL/SQL block calling a database function that returns a SQL statement to generate a manual tabular form (see my series on Developing Dynamic Tabular Forms for more information on how to do this).
The approach I took, while not 100% perfect, does the trick. First, you will need to set the Embed in Frames attribute in your application’s Security settings to “Allow from same origin” for this to work.
For each child table, I created a separate page with a tabular form. From my main page where the data entry is to occur, I created iframe regions that call the tabular form pages. In the src of the iframe I passed the values for the foreign keys. Each tabular form has its own set of Submit buttons, but I wanted users to also be able to click on just one Submit button to save everything. To do this, I created a javascript function that is called by the main Submit button. This function will cycle through all of the iframe regions on the page and submit them. Then the main page is submitted. Since I used the SaveBeforeExit plug-in on my page, I had to manually disable the warning before submitting the main page. If you don’t use SaveBeforeExit, you’ll need to remove this part from the setTimeout function:
$(document).apex_save_before_exit('disableWarning');
The setTimeout function is used to ensure that all of the iframes have had time to submit before submitting the main page, otherwise the iframes would not finish submitting before the main page was submitted which caused issues. This function would need to be added to the HTML Header of your page enclosed in script tags:
function submitAll(pRequest)
{
var frames = document.getElementsByTagName('iframe');
var count = frames.length;
for (i = 0; i < count; i++){
top.frames[i].apex.submit('SUBMIT');
}
setTimeout(function(){$(document).apex_save_before_exit('disableWarning');apex.submit(pRequest)}, 3000);
}
Then in your Submit button set the Action to Redirect to URL and enter this as your URL Target:
javascript:submitAll('SAVE');
You can access the demo application here (credentials are guest/demo): Multiple Tabular Forms Demo
At some point in the future after 5.1 is released, I will probably look into redoing this page using the data grid control. In the meantime this gets the job done.