Checkboxes in Tabular Forms – The easy way! | Inside Oracle APEX by Patrick Wolf

Checkboxes in Tabular Forms – The easy way!

5. February 2009

As you may know, Tabular Forms are sometimes let’s say “a little bit tricky”. They are missing some common features which are available for Page Items. One of them is the declarative support for Checkboxes. With the following step by step instruction you will be able to add checkboxes in Tabular Forms without loosing the existing wizard generated functionality.

Why is it so important to keep the generated ApplyMRU/… processes?

Because this default processes, which are generated by the Tabular Form wizard are doing a lot of stuff in the background. They take care of “Lost update detection”, locking and finally the insert/update/delete without having to write a single line of code. The goal for a developer should always be to keep as much default functionality of Oracle APEX as possible and not try to redevelop that kind of functionality!

You will find several solutions out there, like the one from Denes Kubicek which will work fine, but which doesn’t accomplish the above goal. You have to do a lot of coding and it’s missing the “Lost update detection” and locking feature. Which is very important in my opinion. That’s why I came up with a different solution which doesn’t use the APEX_Item.Checkbox function.

The solution

Create a regular Tabular Form (New Page\Form\Tabular Form). I will use the DEMO_USERS table which comes with the Sample Application.
Change the order and the “Display As” of the columns how you want to have them.
Wizard generated Tabular Form after column changes.
For the column with the flag, in our example that’s ADMIN_USER set the property “Display As” to “Hidden”

Change the SQL Statement of the Tabular Form Region (Edit Region\Region Definition\Region Source) to include a new column called ADMIN_USER_CHECKBOX

SELECT USER_ID
     , USER_ID AS USER_ID_DISPLAY
     , USER_NAME
     , ADMIN_USER
     , CASE
         WHEN ADMIN_USER = 'Y' THEN 'checked="checked"'
       END AS ADMIN_USER_CHECKBOX
     , QUOTA
     , EXPIRES_ON
     , CREATED_ON
  FROM DEMO_USERS

For the new column ADMIN_USER_CHECKBOX, set the property HTML Expression (Column Attributes\Column Formatting) to
```
<input type="checkbox" #ADMIN_USER_CHECKBOX# value="#ROWNUM#" name="f40" id="f40_#ROWNUM#"/>
```
#ADMIN_USER_CHECKBOX# has to be replaced by the column name of the newly added column.

Create a Page Processing process with the following settings:

Type: PL/SQL
Name: Set ADMIN_USER flag
Sequence: 0 (Important: Has to be before all other processes)
Point: On Submit – Before Computation and Validations

Enter PL/SQL Page Process:

BEGIN
    -- Reset the hidden ADMIN_USER flag for all visible records to N
    -- Note: g_f04 maps to the hidden ADMIN_USER column
    FOR ii IN 1 .. APEX_Application.g_f04.COUNT
    LOOP
        APEX_Application.g_f04(ii) := 'N';
    END LOOP;

    -- Set the hidden ADMIN_USER flag for those records where the
    -- checkbox has been set by the user to Y
    -- Note: g_f40 is the checkbox column ADMIN_USER_CHECKBOX
    FOR ii IN 1 .. APEX_Application.g_f40.COUNT
    LOOP
        APEX_Application.g_f04(APEX_Application.g_f40(ii)) := 'Y';
    END LOOP;
END;

Note: See the explanation to find out how to get the correct number for the APEX_Application.g_fxx array access.

That’s it. The default Oracle APEX Tabular Form processes are still working. You can try out the working example on apex.oracle.com
Tabular Form with checkbox

The Explanation

Steps 1 – 3 are just regular tabular form creation steps.
Step 4 adds the new column ADMIN_USER_CHECKBOX, which contains an interesting CASE statement.
```
CASE
  WHEN ADMIN_USER = 'Y' THEN 'checked="checked"'
END AS ADMIN_USER_CHECKBOX
```
This CASE statement will return checked=”checked” if ADMIN_USER is set to Y(es). This kind of value is needed by the INPUT HTML element which we create in step 5.
Step 5 adds the HTML tag to render a checkbox.
```
<input type="checkbox" #ADMIN_USER_CHECKBOX# value="#ROWNUM#" name="f40" id="f40_#ROWNUM#"/>
```
It uses the #COLUMN_NAME# replacement syntax, in our case #ADMIN_USER_CHECKBOX# to get the current value of the column. That’s necessary for the checkbox tag to know if it should be rendered checked or unchecked.

If a checkbox is checked by the user, value=”#ROWNUM#” will tell us which row is effected. The #ROWNUM# placeholder is replaced with the row number of the visible record in the tabular form. It will always start with 1 and has nothing to do with the pseudo column ROWNUM which can be use in a SQL statement!

name=”f40″ tells Oracle APEX to map the checkbox column to the APEX_Application.g_f40 array. I use a very high number so that the checkbox doesn’t conflict with one of the “save state” columns handled by APEX. A high number makes also sense, because in case you add a new column to your tabular form, you do not have to change the code where you access the APEX_Application.g_f40 array.
Step 6 creates the process which synchronizes the hidden ADMIN_USER flag column with the checked checkbox values of ADMIN_USER_CHECKBOX. What does it do?First of all you have to find out to which APEX_Application.g_fxx array your hidden flag column maps to. See my posting Which Tabular Form Column is mapped to which Apex_Application.g_fxx array? . In our example it’s g_f04.
The first FOR LOOP resets the hidden ADMIN_USER flag for all visible records to N(o).
```
FOR ii IN 1 .. APEX_Application.g_f04.COUNT
LOOP
    APEX_Application.g_f04(ii) := 'N';
END LOOP;
```
In the second FOR LOOP it will set the Y(es) flag for all those records where we now have a checked checkbox.
```
FOR ii IN 1 .. APEX_Application.g_f40.COUNT
LOOP
    APEX_Application.g_f04(APEX_Application.g_f40(ii)) := 'Y';
END LOOP;
```
At first hand this statement looks a little bit complicated… For checkboxes you have to remember several things.
- The array which is mapped to a checkbox just contains as many records as you have checked rows. For example: If you have 10 visible checkboxes and only the checkbox for row 8 and 10 are checked, the g_f40 array will just contain 2 entries with the index positions 1 and 2!!! A normal column like the QUOTA always returns an array with 10 entries where the index position always maps to the row number, independent if a value has been entered or not.
- That’s why it’s so important to define value=”#ROWNUM#” in step 5. It tells us which checkbox has been checked. That value of the actual row is contained in the g_f40 array and is used to get the index position to update the g_f04 array of the ADMIN_USER column.
Because we have synchronized the hidden ADMIN_USER column array with the checkbox selection, the standard ApplyMRU process will consider the values as if the user had manually entered them in a normal text field.
The ADMIN_USER_CHECKBOX column is not considered by the ApplyMRU process, because for Oracle APEX it is rendered as “Standard Report Column”. This type of “Display As” is not treated as “saves state”. That’s why the ApplyMRU ignores it. Actually it wouldn’t even know to which array the checkbox is mapped to.
Another advantage of this synchronization technique is that the g_f04 array, which is mapped to the flag column ADMIN_USER has the same array indexing as the normal columns. It allows to write simpler validations code, because all values of the same row are located on the same index position in all the g_fxx arrays!

Final Note

Why don’t I just use the APEX_Item.Checkbox function in the SQL statement, instead of using my hand written HTML code in the HTML Expression?

Simple reason: The AddRows process of APEX will not work. Because APEX_Item.Checkbox is just called for existing records and not for the new record. That’s why the solutions which use APEX_Item.Checkbox have to to redevelop that kind of behavior by doing an UNION against DUAL where the WHERE clause restricts to the current request ADD_ROWS. But be aware of problems with ordering, … when you do that.

As I already said in my initial statement. Don’t reinvent the wheel, use as much functionality of Oracle APEX as possible!

I would be interested in your thought! Do you have ideas for improvement or a better solution? Add your remarks in the comment section!

This posting was originally posted on Der Oracle APEX Experten Blog.

Enjoyed this posting? There are more. Check out the overview page!
Stay updated and get future updates sent to you for free! Join by RSS or Email.

34 Responses to “Checkboxes in Tabular Forms – The easy way!”

Sujay Says:
February 5th, 2009 at 20:29

Nice! I will definitely be using this

Todd Arave Says:
February 6th, 2009 at 01:44

I too like using the wizard generated MRU for all the reasons you mention. Good job on explaining how to implement the checkbox. This implementation of checkbox is a great tool for the toolbox.
Thanks!

Stew Says:
February 6th, 2009 at 14:11

I haven’t finished reading yet, but thought you might want to correct this typo? “…without loosing the exiting wizard generated functionality…” should be “…existing…”???

Chris Neumueller Says:
February 6th, 2009 at 15:49

Hi Patrick!

With a little CSS and JavaScript, there’s another generic solution (I hope your comment field doesn’t eat the html):

—————————————————–
(1) in the HTML page header (
—————————————————–

.js_checkbox { display:none; }

htmldb_delete_message=’”DELETE_CONFIRM_MSG”‘;

function set_yn(i_checkbox,i_apex_field) {
document.getElementById(i_apex_field).value =
(i_checkbox.checked? “Y” : “N”);
}

$().ready(function() {
$(”.js_checkbox”).each(function() {
$(this).after(”");
});
});

—————————————————–

—————————————————–
(2) Column definition
—————————————————–
Display As: Text Field
Element Attributes: class=”js_checkbox”
—————————————————–

Greetings,
Chris

Chris Neumueller Says:
February 6th, 2009 at 16:05

Grrr, as I expected…

Wait, I’ve got a blog of my own

Greetings,
Chris

Patrick Wolf Says:
February 6th, 2009 at 16:13

Stew,

thanks a lot for the typo, I have fixed it.

Patrick

Patrick Wolf Says:
February 6th, 2009 at 16:37

Hi Chris,

interesting idea to just solve it with Javascript and CSS, because it would be quite easy to integrate it as a generic solution into an application.

But I see a problem with the Partial Page Rendering of a tabular form. When an user paginates to the next data set, it wouldn’t fire the ready event to create the checkboxes. Isn’t there a new feature in jQuery 1.3 to register code which fires when a DOM object is created which matches specific criteria? Anyway, I’m more a person doing as much stuff on the server side as possible, because you never know when the ready event fires if you have a delay transmitting the rest of the page and some part is already rendered but doesn’t look as it should.

But a very interesting solution, thanks for your input!
Patrick

Chris Neumueller Says:
February 7th, 2009 at 14:38

Hi Patrick!

Admitted, I forgot about PPR. It’s not that difficult to support, however. I just got it to work with overwriting init_htmlPPRReport. You already wrote about that here (what a nice repository for tips and tricks that blog is ). You are right, there might be other issues from timing to unreliability of Javascript behaviour across browsers in general but my test case worked out fine for a proof of concept.

About doing as much as possible on the server side, that’s also my preferred way of thinking, as you might remember from our discussions. I was just looking for a declarative solution, as checkbox support should be part of a framework, imho.

Greetings,
Chris

Patrick Wolf Says:
February 7th, 2009 at 16:39

Hi Chris,

thanks for the follow up. Yes it should be part of a framework and the perfect framework would be APEX itself. I’m pretty sure we will see such an enhancement in Oracle APEX 4.0

Greetings
Patrick

Chris Neumueller Says:
February 9th, 2009 at 09:11

Hi Patrick,

I totally agree. Looking forward to the next release

Greetings,
Chris

Denes Says:
February 9th, 2009 at 17:33

Patrick,

sounds great. Hopefully the checkbox handling will be a part of the 4.0 enhancement list.

Denes

niels Says:
February 13th, 2009 at 10:37

I followed the instructions, but keep getting the message:

Error in mru internal routine: ORA-20001: no data found in tabular form
Unable to process update.

Any ideas??

Patrick Wolf Says:
February 13th, 2009 at 17:51

Hi Niels,

can you put your example on apex.oracle.com so that I can have a look? I assume that you have a problem with the apex_application.g_fxx arrays.

Send me a mail with the login credentials.

Patrick

niels Says:
February 18th, 2009 at 09:01

For now, only 1 module doesn’t work; I found out how the apex_application.g_fxx arrays work. That’s fine.
Somehow, if I create a new module, it doesnt include the ADMIN_USER (in my case: VERVALLEN_IND) column. If I ad it myself, I get the failure message
Error in mru internal routine: ORA-20001: no data found in tabular form
Unable to process update.
I am curious to know! So now, I think my problem is: why doesn’t the select-statement include the column VERVALLEN_IND??
I was looking for your emailadres; can you send me it?
Thanks!
Niels

Patrick Wolf Says:
February 18th, 2009 at 09:06

Hi Niels,

my e-mail address can be found on the about page.

Patrick

niels Says:
February 18th, 2009 at 11:48

Hi Patrick,
I found out what it was myself (while ‘re-styling’ other modules):
In Report Attributes: the column with the indication Y/N (in your example ADMIN_USER) should be displayed as ‘Textfield’ in Tabular Form Element and not ‘Standard Report Column’. Also fill in tablename and columnname.
Then it works fine! Sorry for the hassle, but it was very educative!
Ciao
Niels

Stew Stryker Says:
February 20th, 2009 at 21:41

Patrick,

Very nice! I just wish you’d noted up front that one needs to identify the hidden column’s position in the display order and update the Process script to match.

Thanks, I’ve just used this in my application!

- Stew

Patrick Wolf Says:
March 1st, 2009 at 18:47

Stew, thanks a lot for your suggestion. I have added a note to the posting.

Patrick

Fernando Says:
March 6th, 2009 at 13:17

I am using this solution but i have a problem,
because i have 24 checkbox (1 for 1 hours of day) in this case i have 24 APEX_Application.g_f04(ii) and 24 f40.COUNT
If I add others 4 fields (ID, Name,LastName, empno) i have more than 50.
In this case when you try to use APEX_Application.g_f5x you get a error.

Thanks (sorry for my English)

Patrick Wolf Says:
March 6th, 2009 at 13:26

Hi Fernando,

the limit for a maximum of 50 columns is a limitation of APEX. In that case you should go with the solution provided by Chris because it doesn’t require to have two columns for each checkbox.

Patrick

Mahendran Says:
March 28th, 2009 at 17:08

Hi ,
i need a help in displaying the search history (search results) in APEX. can you explain abt the methods to display the history of search results

Sekhar Says:
April 3rd, 2009 at 06:25

Hi Patrick,

Your description for the above example is excellent.

Hoping you will help me, i m writing this…I have a checkbox group with 20 checkboxes and their return values. I created a table with a foreign key (one column), and another 2 columns (Total in 3 columns)to store the values of 2 checkboxes (I have to give the validation so that user can select only 2 checkboxes from the 20 checkboxes).

So using that foreign key value i need to show the checkboxes selected(for whom the values stored in the database columns).

How to achieve all this. Can u show me the way to get.

I you are not clear about my requirement please send me the mail so that i can explain in brief.

Ron Says:
April 28th, 2009 at 14:02

Hi Patrick,

I can’t seem to get this to work, i.e. in my case the new ‘case’-column along with the HTML Expression (#COLUMN_NAME#) doesn’t render a checkbox but instead simply a column rendering value ‘checked=”checked”‘ even if I set Display as ‘Text Field’ and add reference table + column in Tabular Form Element section.
Could it be this solution doesn’t work in Apex 3.2? Otherwise, any idea
what I’m doing wrong here?

Regards,
Ron

Ron Says:
April 28th, 2009 at 14:53

Hi Patrick,

You can ignore my last reply, I simply haven’t read step 5 rightly…
the HTML Expresssion:

wasn’t properly visible to me…

So, thanks for your elegant solution.

Omar Says:
May 21st, 2009 at 10:30

Hi Patrick,
I am not sure as I remember you can’t define two tabular forms on the same page. Do you have a general solution, I mean not to reinvent the where to do so, do you have any ideas that next APEX release will support this?. Please your input is highly appreciated.
thanks

Omar

Den Says:
May 29th, 2009 at 18:10

Checking a box and advancing to another page and then back loses the checked box. Suggestions on how to preserve?

Graceq Says:
August 12th, 2009 at 22:45

I still can’t seem to get tis to work, the colum is showing as ‘checked =checked’. Help !

Bill Says:
October 1st, 2009 at 01:27

Step 5 is pure genius. i’m impressed. I’ve been banging this for hours now on a table that has no keys. What a challenge. Thanks a ton.
Always learn something new …. and so clean – hardly any code.

I had to remove the apex_item.checkbox( p_idx=>20, p_value=> col )
from my code and just call the column as NULL in the query.
Then put the html in the HTML elements in the form attributes and voila works great.
Thanks a million.

David Says:
October 5th, 2009 at 17:37

I’m new to APEX (and Oracle and SQL!). I followed your example for the DEMO_USER’s table and it works wonderfully but when I tried to port the concepts to an app that I’m developing it fails to update! I don’t get any errors or any hint that anything is wrong, it just doesn’t update the column.

Here’s my process
BEGIN
FOR ii IN 1 .. APEX_APPLICATION.G_F14.COUNT
LOOP
APEX_APPLICATION.G_F14(ii) := ‘No’;
END LOOP;
FOR ii IN 1 .. APEX_APPLICATION.G_F40.COUNT
LOOP
APEX_APPLICATION.G_F14(APEX_APPLICATION.G_F40(ii)) := ‘Yes’;
END LOOP;
END;

I’ve checked and rechecked that APEX_APPLICATION.G_F14.COUNT is correct. I’ve run it in debug and here’s its output

0.02: Session State: Save form items and p_arg_values
0.02: Processing point: ON_SUBMIT_BEFORE_COMPUTATION
0.02: …Process “Process and Update ISSUES for Checkboxes”: PLSQL (ON_SUBMIT_BEFORE_COMPUTATION) BEGIN FOR ii IN 1 .. APEX_APPLICATION.G_F14.COUNT LOOP APEX_APPLICATION.G_F14(ii) := ‘No’; END LOOP; FOR ii IN 1 .. APEX_APPLICATION.G_F40.COUNT LOOP APEX_APPLICATION.G_F14(APEX_APPLICATION.G_F40(ii)) := ‘Yes’;
0.02: Branch point: BEFORE_COMPUTATION

David Says:
October 5th, 2009 at 19:33

I found it! Stupid security problem!

Punam Says:
December 15th, 2009 at 14:24

Hi,

I have one tabular form. I want to add browse button column in that form. I cannot add that field directly as tabular form is not supporting blob data. Is there any solution to add browse button column to tabular form and upload files?

I have added one NULL column for upload document. and used html expression

and written process to upload cv as:

BEGIN

FOR I IN 1..APEX_APPLICATION.G_F40.COUNT
LOOP
INSERT INTO document(doc_ID, doc_TYPE, LANGUAGE, doc_DATE, FILE_REF, MIME_TYPE, person_ID )
SELECT doc_SEQ.NEXTVAL,
1,
1,
SYSDATE,
BLOB_CONTENT,
MIME_TYPE,
APEX_APPLICATION.G_F02(I)
FROM WWV_FLOW_FILES WHERE NAME = APEX_APPLICATION.G_F40(I);

DELETE WWV_FLOW_FILES WHERE NAME = APEX_APPLICATION.G_F40(I);
END LOOP;
END;

but still it is not uploading file in the database.

can anyone help me “What is the Problem with above code?”

When we are creating file browse button then it creats

a class=”eLink” tabindex=”999″ href=”javascript:popupURL(’f?p=4000:371:3494102961234719::::P371_ID,FB_FLOW_ID,FB_FLOW_PAGE_ID:1189512577311801,101,40′);” title=”Edit”>

and one hidden field whose value equals to 1189512577311801 (it changes per item)

As I have created Null column for browse button it is not creating as well as not creating hidden item.

That’s why I am not getting file uploaded …. So is there any solution for this problem?…..Please help me on this …..

Alexandra Robin Says:
January 19th, 2010 at 18:08

Thanks! This was exactly what I needed and was extremely easy to follow.

Alexandra Robin Says:
January 21st, 2010 at 20:32

I just recreated this and got this error. Can anyone help this novice?

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01722: invalid number, insert into…(my table and field names and array – b1, b2, etc…)

Gaurav Says:
February 4th, 2010 at 23:08

Hi Pat,

I am using the select query to create a tabular form

SELECT apex_item.hidden(49,ticket_no) || APEX_ITEM.CHECKBOX(1,ticket_no,’UNCHECKED’)”SELECT” ,
t_no, impact,
APEX_ITEM.DATE_POPUP(4,rownum,assigned_time,’DD-Mon-YYYY HH24:MI:SS AM’) assigned_time………………….

Now as u see ,i have set of check boxes at the left of the table and if users checks any check box and edits the APEX_ITEM.DATE_POPUP data,the changes are not happening if some other date is selected.

Eg: If the form loads with say 29-Jan-2010 15:40:53 PM and i want to change it to 31-Jan-2010 15:44:53 PM,the changes don’t reflect as i choose some other date and time form the date picker.

My table stores the dates in a timestamp datatype and i want the timestamp in the date picker.

This works if i only use ‘DD-Mon-YYYY’,but i need the time portion too.

I have tried modifying the date format to different formats,but nothing worked as yet.

Pls advice.

Sujay Says:
February 5th, 2009 at 20:29
Nice! I will definitely be using this
Todd Arave Says:
February 6th, 2009 at 01:44
I too like using the wizard generated MRU for all the reasons you mention. Good job on explaining how to implement the checkbox. This implementation of checkbox is a great tool for the toolbox.
Thanks!
Stew Says:
February 6th, 2009 at 14:11
I haven’t finished reading yet, but thought you might want to correct this typo? “…without loosing the exiting wizard generated functionality…” should be “…existing…”???
Chris Neumueller Says:
February 6th, 2009 at 15:49
Hi Patrick!

With a little CSS and JavaScript, there’s another generic solution (I hope your comment field doesn’t eat the html):

—————————————————–
(1) in the HTML page header (
—————————————————–

.js_checkbox { display:none; }

htmldb_delete_message=’”DELETE_CONFIRM_MSG”‘;

function set_yn(i_checkbox,i_apex_field) {
document.getElementById(i_apex_field).value =
(i_checkbox.checked? “Y” : “N”);
}

$().ready(function() {
$(”.js_checkbox”).each(function() {
$(this).after(”");
});
});

—————————————————–

—————————————————–
(2) Column definition
—————————————————–
Display As: Text Field
Element Attributes: class=”js_checkbox”
—————————————————–

Greetings,
Chris
Chris Neumueller Says:
February 6th, 2009 at 16:05
Grrr, as I expected…

Wait, I’ve got a blog of my own

Greetings,
Chris
Patrick Wolf Says:
February 6th, 2009 at 16:13
Stew,

thanks a lot for the typo, I have fixed it.

Patrick
Patrick Wolf Says:
February 6th, 2009 at 16:37
Hi Chris,

interesting idea to just solve it with Javascript and CSS, because it would be quite easy to integrate it as a generic solution into an application.

But I see a problem with the Partial Page Rendering of a tabular form. When an user paginates to the next data set, it wouldn’t fire the ready event to create the checkboxes. Isn’t there a new feature in jQuery 1.3 to register code which fires when a DOM object is created which matches specific criteria? Anyway, I’m more a person doing as much stuff on the server side as possible, because you never know when the ready event fires if you have a delay transmitting the rest of the page and some part is already rendered but doesn’t look as it should.

But a very interesting solution, thanks for your input!
Patrick
Chris Neumueller Says:
February 7th, 2009 at 14:38
Hi Patrick!

Admitted, I forgot about PPR. It’s not that difficult to support, however. I just got it to work with overwriting init_htmlPPRReport. You already wrote about that here (what a nice repository for tips and tricks that blog is ). You are right, there might be other issues from timing to unreliability of Javascript behaviour across browsers in general but my test case worked out fine for a proof of concept.

About doing as much as possible on the server side, that’s also my preferred way of thinking, as you might remember from our discussions. I was just looking for a declarative solution, as checkbox support should be part of a framework, imho.

Greetings,
Chris
Patrick Wolf Says:
February 7th, 2009 at 16:39
Hi Chris,

thanks for the follow up. Yes it should be part of a framework and the perfect framework would be APEX itself. I’m pretty sure we will see such an enhancement in Oracle APEX 4.0

Greetings
Patrick
Chris Neumueller Says:
February 9th, 2009 at 09:11
Hi Patrick,

I totally agree. Looking forward to the next release

Greetings,
Chris
Denes Says:
February 9th, 2009 at 17:33
Patrick,

sounds great. Hopefully the checkbox handling will be a part of the 4.0 enhancement list.

Denes
niels Says:
February 13th, 2009 at 10:37
I followed the instructions, but keep getting the message:

Error in mru internal routine: ORA-20001: no data found in tabular form
Unable to process update.

Any ideas??
Patrick Wolf Says:
February 13th, 2009 at 17:51
Hi Niels,

can you put your example on apex.oracle.com so that I can have a look? I assume that you have a problem with the apex_application.g_fxx arrays.

Send me a mail with the login credentials.

Patrick
niels Says:
February 18th, 2009 at 09:01
For now, only 1 module doesn’t work; I found out how the apex_application.g_fxx arrays work. That’s fine.
Somehow, if I create a new module, it doesnt include the ADMIN_USER (in my case: VERVALLEN_IND) column. If I ad it myself, I get the failure message
Error in mru internal routine: ORA-20001: no data found in tabular form
Unable to process update.
I am curious to know! So now, I think my problem is: why doesn’t the select-statement include the column VERVALLEN_IND??
I was looking for your emailadres; can you send me it?
Thanks!
Niels
Patrick Wolf Says:
February 18th, 2009 at 09:06
Hi Niels,

my e-mail address can be found on the about page.

Patrick
niels Says:
February 18th, 2009 at 11:48
Hi Patrick,
I found out what it was myself (while ‘re-styling’ other modules):
In Report Attributes: the column with the indication Y/N (in your example ADMIN_USER) should be displayed as ‘Textfield’ in Tabular Form Element and not ‘Standard Report Column’. Also fill in tablename and columnname.
Then it works fine! Sorry for the hassle, but it was very educative!
Ciao
Niels
Stew Stryker Says:
February 20th, 2009 at 21:41
Patrick,

Very nice! I just wish you’d noted up front that one needs to identify the hidden column’s position in the display order and update the Process script to match.

Thanks, I’ve just used this in my application!

- Stew
Patrick Wolf Says:
March 1st, 2009 at 18:47
Stew, thanks a lot for your suggestion. I have added a note to the posting.

Patrick
Fernando Says:
March 6th, 2009 at 13:17
I am using this solution but i have a problem,
because i have 24 checkbox (1 for 1 hours of day) in this case i have 24 APEX_Application.g_f04(ii) and 24 f40.COUNT
If I add others 4 fields (ID, Name,LastName, empno) i have more than 50.
In this case when you try to use APEX_Application.g_f5x you get a error.

Thanks (sorry for my English)
Patrick Wolf Says:
March 6th, 2009 at 13:26
Hi Fernando,

the limit for a maximum of 50 columns is a limitation of APEX. In that case you should go with the solution provided by Chris because it doesn’t require to have two columns for each checkbox.

Patrick
Mahendran Says:
March 28th, 2009 at 17:08
Hi ,
i need a help in displaying the search history (search results) in APEX. can you explain abt the methods to display the history of search results
Sekhar Says:
April 3rd, 2009 at 06:25
Hi Patrick,

Your description for the above example is excellent.

Hoping you will help me, i m writing this…I have a checkbox group with 20 checkboxes and their return values. I created a table with a foreign key (one column), and another 2 columns (Total in 3 columns)to store the values of 2 checkboxes (I have to give the validation so that user can select only 2 checkboxes from the 20 checkboxes).

So using that foreign key value i need to show the checkboxes selected(for whom the values stored in the database columns).

How to achieve all this. Can u show me the way to get.

I you are not clear about my requirement please send me the mail so that i can explain in brief.
Ron Says:
April 28th, 2009 at 14:02
Hi Patrick,

I can’t seem to get this to work, i.e. in my case the new ‘case’-column along with the HTML Expression (#COLUMN_NAME#) doesn’t render a checkbox but instead simply a column rendering value ‘checked=”checked”‘ even if I set Display as ‘Text Field’ and add reference table + column in Tabular Form Element section.
Could it be this solution doesn’t work in Apex 3.2? Otherwise, any idea
what I’m doing wrong here?

Regards,
Ron
Ron Says:
April 28th, 2009 at 14:53
Hi Patrick,

You can ignore my last reply, I simply haven’t read step 5 rightly…
the HTML Expresssion:

wasn’t properly visible to me…

So, thanks for your elegant solution.
Omar Says:
May 21st, 2009 at 10:30
Hi Patrick,
I am not sure as I remember you can’t define two tabular forms on the same page. Do you have a general solution, I mean not to reinvent the where to do so, do you have any ideas that next APEX release will support this?. Please your input is highly appreciated.
thanks

Omar
Den Says:
May 29th, 2009 at 18:10
Checking a box and advancing to another page and then back loses the checked box. Suggestions on how to preserve?
Graceq Says:
August 12th, 2009 at 22:45
I still can’t seem to get tis to work, the colum is showing as ‘checked =checked’. Help !
Bill Says:
October 1st, 2009 at 01:27
Step 5 is pure genius. i’m impressed. I’ve been banging this for hours now on a table that has no keys. What a challenge. Thanks a ton.
Always learn something new …. and so clean – hardly any code.

I had to remove the apex_item.checkbox( p_idx=>20, p_value=> col )
from my code and just call the column as NULL in the query.
Then put the html in the HTML elements in the form attributes and voila works great.
Thanks a million.
David Says:
October 5th, 2009 at 17:37
I’m new to APEX (and Oracle and SQL!). I followed your example for the DEMO_USER’s table and it works wonderfully but when I tried to port the concepts to an app that I’m developing it fails to update! I don’t get any errors or any hint that anything is wrong, it just doesn’t update the column.

Here’s my process
BEGIN
FOR ii IN 1 .. APEX_APPLICATION.G_F14.COUNT
LOOP
APEX_APPLICATION.G_F14(ii) := ‘No’;
END LOOP;
FOR ii IN 1 .. APEX_APPLICATION.G_F40.COUNT
LOOP
APEX_APPLICATION.G_F14(APEX_APPLICATION.G_F40(ii)) := ‘Yes’;
END LOOP;
END;

I’ve checked and rechecked that APEX_APPLICATION.G_F14.COUNT is correct. I’ve run it in debug and here’s its output

0.02: Session State: Save form items and p_arg_values
0.02: Processing point: ON_SUBMIT_BEFORE_COMPUTATION
0.02: …Process “Process and Update ISSUES for Checkboxes”: PLSQL (ON_SUBMIT_BEFORE_COMPUTATION) BEGIN FOR ii IN 1 .. APEX_APPLICATION.G_F14.COUNT LOOP APEX_APPLICATION.G_F14(ii) := ‘No’; END LOOP; FOR ii IN 1 .. APEX_APPLICATION.G_F40.COUNT LOOP APEX_APPLICATION.G_F14(APEX_APPLICATION.G_F40(ii)) := ‘Yes’;
0.02: Branch point: BEFORE_COMPUTATION
David Says:
October 5th, 2009 at 19:33
I found it! Stupid security problem!
Punam Says:
December 15th, 2009 at 14:24
Hi,

I have one tabular form. I want to add browse button column in that form. I cannot add that field directly as tabular form is not supporting blob data. Is there any solution to add browse button column to tabular form and upload files?

I have added one NULL column for upload document. and used html expression

and written process to upload cv as:

BEGIN

FOR I IN 1..APEX_APPLICATION.G_F40.COUNT
LOOP
INSERT INTO document(doc_ID, doc_TYPE, LANGUAGE, doc_DATE, FILE_REF, MIME_TYPE, person_ID )
SELECT doc_SEQ.NEXTVAL,
1,
1,
SYSDATE,
BLOB_CONTENT,
MIME_TYPE,
APEX_APPLICATION.G_F02(I)
FROM WWV_FLOW_FILES WHERE NAME = APEX_APPLICATION.G_F40(I);

DELETE WWV_FLOW_FILES WHERE NAME = APEX_APPLICATION.G_F40(I);
END LOOP;
END;

but still it is not uploading file in the database.

can anyone help me “What is the Problem with above code?”

When we are creating file browse button then it creats

a class=”eLink” tabindex=”999″ href=”javascript:popupURL(’f?p=4000:371:3494102961234719::::P371_ID,FB_FLOW_ID,FB_FLOW_PAGE_ID:1189512577311801,101,40′);” title=”Edit”>

and one hidden field whose value equals to 1189512577311801 (it changes per item)

As I have created Null column for browse button it is not creating as well as not creating hidden item.

That’s why I am not getting file uploaded …. So is there any solution for this problem?…..Please help me on this …..
Alexandra Robin Says:
January 19th, 2010 at 18:08
Thanks! This was exactly what I needed and was extremely easy to follow.
Alexandra Robin Says:
January 21st, 2010 at 20:32
I just recreated this and got this error. Can anyone help this novice?

Error in mru internal routine: ORA-20001: Error in MRU: row= 1, ORA-01722: invalid number, insert into…(my table and field names and array – b1, b2, etc…)
Gaurav Says:
February 4th, 2010 at 23:08
Hi Pat,

I am using the select query to create a tabular form

SELECT apex_item.hidden(49,ticket_no) || APEX_ITEM.CHECKBOX(1,ticket_no,’UNCHECKED’)”SELECT” ,
t_no, impact,
APEX_ITEM.DATE_POPUP(4,rownum,assigned_time,’DD-Mon-YYYY HH24:MI:SS AM’) assigned_time………………….

Now as u see ,i have set of check boxes at the left of the table and if users checks any check box and edits the APEX_ITEM.DATE_POPUP data,the changes are not happening if some other date is selected.

Eg: If the form loads with say 29-Jan-2010 15:40:53 PM and i want to change it to 31-Jan-2010 15:44:53 PM,the changes don’t reflect as i choose some other date and time form the date picker.

My table stores the dates in a timestamp datatype and i want the timestamp in the date picker.

This works if i only use ‘DD-Mon-YYYY’,but i need the time portion too.

I have tried modifying the date format to different formats,but nothing worked as yet.

Pls advice.

Click here to cancel reply.

Inside Oracle APEX by Patrick Wolf