Multiple tabular forms on one Apex page

As you probably know, you cannot have multiple tabular forms on one Apex page. One way around this limitation is to create your own reports and processes. This is described in Denes Kubicek's multiple tabular forms example.

But you can also trick your application to make it look like two tabular forms are on the same page.



This page actually consists of two pages. The form at the bottom is defined on another page. By using an iframe, this 2nd page is displayed on the same page as the form at the top.

Start by creating a normal tabular form page. Nothing special there.

Create a second page as a new blank page.
Change the template to Printer friendly. This way nothing extra will be shown on the page.

Now you can add the tabular form region as you would normally do. Make sure that the submit and cancel buttons branch back to the page you are now creating. This is important. There should be no way to go to any other page from this page. Otherwise, you are going to be stuck on the wrong page in the iframe.

Go back to the first page and create a HTML region on this page. Change the Region source to something like this:

Password item in a tabular Apex form

In a tabular form it is not possible to choose password as the display type. This means that a tabular form would look like this:


This post by Patrick Wolf shows how you can add a check box to tabular forms. That gave me an idea to try the same thing for password items. It is actually much simpler than in Patrick's example!

All you have to do is set the HTML expression of the item to:

As explained by Patrick you have to find out which id you have to use. In my example the id is f06.
The easiest way to achieve this is using Firebug to inspect the unmodified element.




After these modifications the form looks like this:

Create an Audit Region based on a PL/SQL Region

In my APEX application I wanted to have a small region in all screens which would enable the user to see the audit columns CREATED, CREATEDBY, UPDATED and UPDATEDBY which are present in all tables. Like the screenshot below shows, in the report I added a dummy column “Audit” with a link. When the user clicks on the audit link in a record, the Audit window in the upper right hand corner appears with the audit columns of the record the user selected.

This region can be created manually with items, but instead of manually creating this region including the items on each page, I developed a small procedure which shows the audit data automatically, based on a simple configuration.

The only condition is that each table contains a one-column primary key which also has to be present in the reports in the screens.

Once you have created the PL/SQL region in one screen in your application, you can copy the region to all the other screens, and it requires minimum configuration to make it work in all the other screens.

See below the steps to create the Audit region and make it work. First you need to create a PL/SQL region that contains a hidden item “P??_AUDIT_ID”.

1 Create a Region.
1.1 Type: PL/SQL Dynamic Content.
1.2 Region Details :








1.3 PL/SQL Source:
apex_custom_utils.show_audit(:APP_ID,:APP_PAGE_ID, ‘TABLENAME’, :P45_AUDIT_ID );


Replace TABLENAME with the tablename of your report, and change P45 to your page number.

The code of the procedure show_audit will follow further below.






1.4 Set the condition of the region (Only show the region is P??_AUDIT_ID is not null)















2 Create an item P??_AUDIT_ID in the newly created region:
2.1 Item Type:





2.2 Item Display Position and Name:








2.3 Item Attributes:








2.4 Source:












2.5 Press button “Create Item”

3 Add dummy column “Audit” to Report Region:
3.1 Add the following column to the query: ‘Audit’ as AUDIT_BUTTON









4 Edit the dummy column:
4.1 Set the heading blank (& nbsp;)





4.2 Set the column link:









The target page is the page itself.
The item P??_AUDIT_ID will receive the value of the primary key column of the report, in this case ALT_ID.

5 Create a procedure in the database. In this case I created the following procedure in a package:
--------------------------------------------------------------------------------------------
PROCEDURE show_audit( p_app_id IN NUMBER
, p_app_page_id IN NUMBER
, p_tablename IN VARCHAR2
, p_id IN NUMBER ) IS
--
CURSOR c_get_pkcol( c_tablename VARCHAR2 ) IS
SELECT col.column_name
FROM user_cons_columns col, user_constraints cons
WHERE cons.constraint_type = 'P'
AND cons.table_name = c_tablename
AND col.constraint_name = cons.constraint_name;
--
cpagecomment VARCHAR2(4000);
ctablename VARCHAR2(100);
cpkcolumn VARCHAR2(100);
cstatement VARCHAR2(2000);
cursor1 INTEGER;
rows_processed INTEGER;
dcreated DATE;
ccreatedby VARCHAR2(50);
dupdated DATE;
cupdatedby VARCHAR2(50);
BEGIN
IF p_app_id IS NOT NULL AND p_app_page_id IS NOT NULL
AND p_tablename IS NOT NULL AND p_id IS NOT NULL THEN
open c_get_pkcol( p_tablename );
fetch c_get_pkcol into cpkcolumn;
close c_get_pkcol;
--
cstatement := 'SELECT created, createdby, updated, updatedby'||
'FROM '||p_tablename||
' WHERE '||cpkcolumn||' = '||TO_CHAR(p_id);
--
cursor1 := dbms_sql.open_cursor;
dbms_sql.parse( cursor1, cstatement, 2 );
dbms_sql.define_column( cursor1, 1, dcreated );
dbms_sql.define_column( cursor1, 2, ccreatedby, 30 );
dbms_sql.define_column( cursor1, 3, dupdated );
dbms_sql.define_column( cursor1, 4, cupdatedby, 30 );
rows_processed := dbms_sql.execute( cursor1 );
IF dbms_sql.fetch_rows( cursor1 )>0 THEN
dbms_sql.column_value( cursor1, 1, dcreated );
dbms_sql.column_value( cursor1, 2, ccreatedby );
dbms_sql.column_value( cursor1, 3, dupdated );
dbms_sql.column_value( cursor1, 4, cupdatedby );
END IF;
dbms_sql.close_cursor( cursor1 );
--
htp.prn('<table class="formlayout">');
htp.prn('<tr><td nowrap align="right">Created :'||
'</td><td nowrap align="left">'||
TO_CHAR(dcreated,'DD/MON/YYYY HH24:MI:SS')||'</td>< /tr>');
htp.prn('<tr><td nowrap align="right">Created by :'||
'</td><td nowrap align="left">'||
ccreatedby||'</td></tr>');
htp.prn('<tr><td nowrap align="right">Updated :'||
'</td><td nowrap align="left">'||
TO_CHAR(dupdated,'DD/MON/YYYY HH24:MI:SS')||'</td>< /tr>');
htp.prn('<tr><td nowrap align="right">Updated by :'||
'</td><td nowrap align="left">'
||cupdatedby||'</td></tr>');
END IF;
EXCEPTION WHEN others THEN
htp.prn('Error trying to display audit values : '||SQLERRM||'<br>');
END show_audit;
--------------------------------------------------------------------------------------------

6 - Done ! When you click on the Audit link in the report, the Audit region should appear in the upper right hand corner with the audit data.

7 To make it a bit more “neat”, make sure to clear the cache of this page when you navigate to another page. So that when you navigate back to the page, the audit region doesn’t already appear without actually haven clicked on any records.

8 To deploy the Audit Region to other pages in your application, simply copy the Region including items to other pages. The only things you have to configure after copying the region are:
- The tablename in the PL/SQL source procedure call
- Make sure there is a dummy column “Audit” with a link in the report region which fills the P??_AUDIT_ID item.