Friday 17 May 2013

APEX Tabular Form - Instant Update

Yesterday an interesting question regarding tabular forms, collections and instant updates was asked in the Oracle APEX Forum. This example in my Demo Application shows how you can create a tabular form based on a collection and update this collection instantly. The whole example consists of three main parts:

1. save changes instantly
2. add rows and
3. delete rows

The whole code and the steps required to get it working are explained in the Code section.

Enjoy.


21 comments:

Marko Gorički said...

I have nice plugins (DA and Process) for this but never had time to publish them. Hope I'll manage to do this soon... :)

dlsykes said...

Hi Denes, I have done lots of tabular forms in APEX with different documented approaches. I plan on adopting your approach (page 294 in demo app) for my next project but cannot get this to work with pop-up LOV. It errors out on the set value DA. I am using APEX 4.2.0. Is there a work around or is this a bug in APEX?

Denes Kubicek said...

A popup key lov will need two arrays. Also, the id will start for the first row differently than for a normal select list. I think you can make it work for popup but you need some modifications.

Regards,

Denes

DS123 said...

Hi,

I am using APEX_ITEM.TEXT in the tabular report. It needs to have a default value(This value is obtained by calling a database function.) Later, when user edits the value in the textbox, I need to retain it after page submit. Can you please help?

Dona said...

Hi Denes,
I'm facing a problem in APEX. Please see if you can help. I have posted this in your blog as well.

I have a manual tabular report, with two editable fields(a select list and a text box) and all other columns are apex_item.display_and_save.

On change of the editable fields, these display-only columns's value need to change.[They are being calculated from database functions].

Problem is: When I am setting the values on change of the 2 fields, I am doing so through javascript, using getElementById. But when I am trying to insert these values in database in a Save process, I am doing so through APEX_APPLICATION.G_Fxx (i). And the values are diffrent. APEX_APPLICATION.G_Fxx (i) has the default value stored, while getElementById gives the span id which stores the new value. I want to insert the new value into the table. How can I do this?

I'm not sure if I can use collections here, since in the 'value' attribute of APEX_ITEM.DISPLAY_AND_SAVE, I am calling a database function, which brings the default value for the report column. So I can't put a collection member in there.

Your help will be much appreciated. Thanks!

Denes Kubicek said...

Why don't you create an example? I will try to debug.

Regards,

Denes

Unknown said...

Hello Denes.
My question will be a bit strange. I have a select list from which the user can select a value. Based on that value a tabular form appear showing records that are compatible with the selected value. I need to check if any of the records from tab form is selected when user press submit button. If no selection is made then the user should get an error. How do i do that?
Thank you.
Gabriel

Denes Kubicek said...

Gabriel,

isn't that something like this:

DECLARE
v_error VARCHAR2 (4000);
BEGIN
IF apex_application.g_f01.COUNT = 0
THEN
v_error := 'No records selected.';
END IF;

RETURN v_error;
END;

?

Denes

mrpele said...

Hi Denes,
I have a problem. The problem is that delete link is not triggering dynamic action although it have set its link attributes as 'id="9" class="delete"'.
So i am confused about 15 and 16 th steps in your code. Can you check these steps?

Denes Kubicek said...

There was an error in the code I posted. I did the correction and you should read it again. The right code is

id="#SEQ_ID#" class="delete"

Regards,

Denes Kubicek

mrpele said...
This comment has been removed by the author.
mirela said...

Hi Denes,

As usual one good solution from you. One question: I'm using APEX 4.0 (I know... not my choice) and I notice that after update and refresh I lose the focus. I can manually set the focus on the next element, but it is not always the user's will. Your application works fine, so any suggestions?

Denes Kubicek said...

So, how do you know what the user wants? Where exactly do you want to focus the cursor?

Denes Kubicek

Mirela said...

Hi Denes,
On the change event is the region refreshed. In APEX 4.0 is the cursor just not focused anymore. I would expect that if the user the "Tab" has clicked that the cursor would be in the next input field. But if he used the mouse, I would expect the cursor to be in that field.
Thanks,
Mirela

Promeet said...

hi Denes
On Step 7 the below code is incorrect
$('#'+'f01_'+$('#P294_ID').val().substring(4)).val();

In order to obtain the rownum we can
$('#P294_ID').substring(4) and not
$('#P294_ID').val().substring(4)

Thus the correct jQuery will be
$('#'+'f01_'+$('#P294_ID').substring(4)).val();

https://apex.oracle.com/pls/apex/f?p=31517:294:13209892118709:::::

APEX Developer In the USA said...

Quick question, when you implement this, have you tried adding pagination to your tabular form?

Why I am asking is, we have a similar page in an application, and when we add rows to the collection, the pagination resets to page 1.

What we want is to stay on the page that we inserted a row..

Denes Kubicek said...

That is an interesting question though. In my example it jumps to the page 1. In the classic solution it stays on the current page since there is no report refresh. Eventually I could make it working as you describe but it could be quite time consuming and a lot of work. Currently not possible for me to have a look at it. Means, you will need to do it yourself.

Regards,

Denes

APEX Developer In the USA said...

I think I have found a kludge solution.. It involves adding a step to my dynamic action that I use to add a new row to the form. I had to make an adjustment to inset the row RIGHT after the row the user clicked on. After that I am running a refresh which is the culprit that is sending my report back to page 1.

What I going to do is, add a javascript call that is similar to the pagination scheme I am using, with the pagination set to re-paginate the page I was on before the refresh, thus it will send me back to whence I came..

A klude, but considering in time this will be ported over to Jari's routine using an interactive report as a tabular form.. (Just need the time to code the cascading lov solution that he introduced for the lov's on this page)

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Anonymous said...

Hi Denes,
Thanks for blogging about this, it is really helpful. I have your example working, but then have added a "Simple Checkbox" to the mix. It doesn't seem that the JQuery Selector on the dynamic action can pick up the change in value. Any advice to get a "Simple Checkbox" working with your example? or is it just not going to happen?

Thank You in advance.

adityatatavarty said...
This comment has been removed by the author.
adityatatavarty said...

I am developing an app that captures the every day resource utilization of a person in a team.

I completed every thing and stuck up at a point i.e

Please find the below image
1.jpg
As shown in the above image I created a Tabular form for this where month is a drop down list and year is a text box, the days 1,2,3,....31 are text boxes to capture utilized time.

My requirement is to make the days greyed out and lock (not to allow the user to enter data) for the days which are not in that month.

ex: when Month : Feb then 29,30,31 : greyed out and locked
I have searched in many websites for the solution, But I couldn't get one. Please help me


for full details plz refer

https://community.oracle.com/thread/3698459