Tuesday, 25 December 2007

Add / Delete a row from a SQL based Tabular Form (Static ID)

The current application I am working on involves a re-write to a 12 screen wizard that was written 18 months ago. Several of the screens make use of manually built tabular forms (SQL report regions) and collections to hold the values entered. Some of the screens in the wizard have multiple tabular forms on them as well.

Currently all tabular forms have 15 lines which cannot be added to or deleted from. In the new version, we removed this limit and allow the user to add as many rows as he / she needs. Furthermore, specific rows can now be removed from the Tabular form. Since all entered data is written into collections, we wanted to avoid "line by line" processing i.e. submitting the form for each time, updating the collection and branching back to the page. By utilising some simple JavaScript and the new "Static ID" of the Reports Region new to APEX 3.0, all requirements could be met.

The Static ID attribute of the reports region allow us to add our own (unique) ID to a report region. From here we can simpley navigate down the DoM, clone a row in the form using cloneNode and append it to the table using appendChild.

The JavaScript will work even if you have multiple report regions on the same page providing each report region has a unique Static ID value.

Method:
  • Create a new region on your page using the following details:
  1. Region Type: Report
  2. Report Implementation: SQL Report
  3. Title: Add Row to Report
  4. Region Template: Reports Region
  5. SQL Query: view
  6. Report Template: Standard
  • Add the following JavaScript to your Page Header: view
  • Copy the region template: Reports Region and name it Reports Region (Static ID)
  • Edit the region template: Reports Region (Static ID) and replace the Substitution String #REGION_ID# with #REGION_STATIC_ID# in the Definition section

  • Edit the region: Add Rows to Report and insert the value: REPORT1 into the Static ID textbox found in the Identification seciton. Note that the values entered into the Static ID textbox must be unique to the page if using multiple report regions where you are specifying a Static ID. Then change the template of the region to use the newley created Reports Region (Static ID) template

  • Copy the report template: Standard and name it Standard (Static ID)

  • Edit the report template: Standard (Static ID) and replace the text: id="#REGION_ID#" with: id="datatable_#REGION_ID#" in the before rows section.

  • Edit the report attributes and change the report template to use the newley created: Standard (Static ID)

  • Add a button to the page using the following details:
  1. Select a Region for the Button: Add Rows to Report
  2. Position: Create a button in a region position
  3. Button Name: ADD_ROW
  4. Label: Add Row
  5. Button Type: HTML Button
  6. Action: Redirect to URL without submitting page
  7. Target is a: URL
  8. URL Target: javascript:addRow('REPORT1');

Please note that REPORT1 refers to the Static ID of the region you want to add your row to

  • Test your Add and Delete a row functionality

An example with all the source code can be seen here

18 comments:

Patrick Wolf said...

Duncan,

really nice tip and very useful!

BTW, have you tried to add a

style="cursor:pointer;"

to the IMG tag to get a better visual feedback that the "X" icon is clickable?

Greetings
Patrick

Duncan Mein said...

Hi Patrick

Adding that style to the image is a lot better now. Thanks for pointing that out.

I have updated the demo on apex.oracle.com as well

Regards

Duncan

Anonymous said...

Hi Duncan

Is it possible to base your solution on an updatable form?
Can be rows added based on your idea inserted in a table?

Best regards
Adrian

Anonymous said...

Hello Duncan,

This is a very nice solution.

But how do you integrate this with collections? How do you insert the added rows into the collection?

If you answer this, you'll save my day ;)

Jason B.

runcsmeduncs said...

Hi Jason

Its very simple to use a collection based on the example. Assuming your collection will hold 2 values (f01 and f02 from the form) you simpley create a process that:

1. Initiates a collection using:
APEX_COLLETION.create_or_truncate_collection ('COL1');

2. Populate this collection by looping through the f01 or f02 elements:

FOR i in 1..apex_application.g_f01.count
LOOP
apex_collection.add_member(
p_collection_name=>'COL1', p_c001=>apex_APPLICATION.g_f01(i),
p_c002=>apex_APPLICATION.g_f02(i));
END LOOP;

Hope that helps

Duncs

Anonymous said...

Hi Duncan,

That helped.

Not so difficult as I initially imagined :)

Thanks,
Jason

shunt said...

Nice solution Dunc. However, it doesn't work with Popups, but I expect you have that in hand. This is a much better than the Apex howto on Tab Forms. Cheers Shunt

shunt said...

Dunc, I have got this working with a MERGE statement.

http://simonhunt.blogspot.com/2009/02/merge-into-duncs-tabular-form.html

Shunt

Anonymous said...

Hi, it gives me an error message:
table is undefined
at line:
var newRow = table.tBodies[0].rows[1].cloneNode(true);

Valentin Tabacaru said...

In Apex 3.2 you will have to make a small modification to the JavaScript:
var allDivs = document.getElementById(theRegionId).getElementsByTagName("div");

for(var i =0 ; i < allDivs.length ; i++)
{
if (allDivs[i].id.indexOf("datatable") >= 0)
{
myTableID = allDivs[i].id.slice(10);
table = document.getElementById(myTableID);
break;
}
}

rduht said...

I think wow gold and world of warcraft gold

Yasen ® said...

Thank you very much for your post. This is exactly what I wanted. Regarding this solution I have a few questions for you: seems like "delete row" function is not working for me and another thing is: do you know how to use LOV (select list from existing table) instead static values from "SPORT" column? P.S. I am running APEX 3.2

Thanks

Yasen ® said...

I just figured that out, I had to add APEX_ITEM.SELECT_LIST_FROM_LOV(15,'','CATEGORY',NULL,'YES',NULL,'-Select Category-',null,null,'NO') CATEGORY
to Regian defenition...but delete button still doesnt work, coould you please help me with that....I am new to APEX.
Here is my post on forum:
http://forums.oracle.com/forums/thread.jspa?threadID=944968&tstart=0

Yasen ® said...

Hi Duncan,
I did update my post on forum with more detailed info about "delete" button error.
http://forums.oracle.com/forums/thread.jspa?messageID=3711895&#3711895

thank you for checking this

Yasen ® said...

OK, I figured this out...please see my thread....
http://forums.oracle.com/forums/message.jspa?messageID=3715776#371577

ae_houston said...

Duncan,

I tested your sample code and works great but, if for some reason I delete all the rows and then hit the Add Row button, it didn't do anything.

Please help!

Andres

Michael Li said...

Hi Duncan,

This is the following question to Jason's(how to store new value to collections), I know your answer works if I have one collection-based form, my question is how can I store them into collection if I have two tabular form(both are collection based). I have a request (kind of master- multiple detail stuff), I have created the page with two tabular form manually but I'm having trouble to store them back into collections..

Thanks

Mike

Thanks

Kevin said...

Hi Duncan,

thanks for the soln. However I am having a small issue with it. My SQL Query is based on a table and it pulls values from the table. I have followed you soln to the letter.

if I have 2 rows displayed and hit the add row button I get 2 rows to add.

Anyway around this?