Checkboxes in Tabular Forms – The easy way!

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

  1. Create a regular Tabular Form (New Page\Form\Tabular Form). I will use the DEMO_USERS table which comes with the Sample Application.
  2. Change the order and the “Display As” of the columns how you want to have them.

    Wizard generated Tabular Form after column changes.

    Wizard generated Tabular Form after column changes.

  3. For the column with the flag, in our example that’s ADMIN_USER set the property “Display As” to “Hidden”
  4. 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
  5. 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.

  6. 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.

  7. 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

    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.

49 thoughts on “Checkboxes in Tabular Forms – The easy way!

  1. 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!

  2. 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…”???

  3. 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

  4. 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

  5. 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

  6. 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

  7. Hi Patrick,

    I totally agree. Looking forward to the next release :-)

    Greetings,
    Chris

  8. Patrick,

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

    Denes

  9. 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??

  10. 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

  11. 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

  12. 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

  13. 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

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

    Patrick

  15. 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)

  16. 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

  17. 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

  18. 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.

  19. 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

  20. 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.

  21. 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

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

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

  24. 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.

  25. 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

  26. 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 …..

  27. 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…)

  28. 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.

  29. Very nice. Thanks. Just a suggestion; why not name the checkbox to the highest available array index (f50), then you’ll only get in trouble if you use the maximum number of columns.

  30. Hi Patric,
    I am creating a tabular form in which each row has about 15 columns in which 10 are start_date and end_dates of five years so my requirement is to make those 10 columns in to 5 columns each column with two rows which are start_date and end_date….I know its a bit confusing for you..so my question is can we wrap those two columns in to one column with two rows in a tabular form..
    Thanks,
    Mahesh

  31. I don’t speak english, but i must tell: “Thank you very march”.

  32. Hi Patrick,

    Great great post !!!

    I have just one little question. If I have 4 checkboxes, how can I have less then 8 loops(two for each checkbox) to get this work done ?

  33. Good work Patrick, one of the cleanest solutions around. Similar to something I remember thinking about one day, but never attempted to action it.

    Martin – I’ve just implemented with 2 checkboxes. I think it’s save to have the first loop catering for all hidden checkboxes, as all array elements will be present.

    I found the second loop needed to be separate, as it will be as sparse as your checkboxes are, thanks to HTML behaviour. Otherwise you will fight no_data_found

    ScottWE.

  34. Hi Volker,

    in APEX 4.0 you have now a native checkbox type. Have you already tried that one?

    Regards
    Patrick

  35. Patrick,

    Thanks for your insight, I am new to Apex and I have specific requirement of having a Checkbox for Price column – if the user clicks on Paid he should be directed to a specific table report ( which is not a big problem ) , the problem comes when user needs the above report from a specific database ( 2 different databases involved )
    so 2 set of checkboxes
    Cost – FREE
    PAID

    Database – DB1
    DB2

    The requirement is when user select FREE or PAID or BOTH and select a DB1 or DB2 they should show related report from specific table ( which are FREE_DB1 or FREE_DB2 or PAID_DB1 or PAID_DB2 or PAID_FREE_DB1 OR PAID_FREE_DB2 )

    May be its a easy solution but I am failing to build the logic and accordingly make APEX3.1 work for the same .

    Please help

  36. Good day MR Patric
    I new in apex, and speak spanish I follow your intruccion for checkbox in tabularform but keep getting :
    CHECKED=”CHECHED” IN BOX =’Y’ OR NULL IN BOX=’N’
    THANKS FOR YOUR ATTENCION

  37. I am using MRU to update my table. No delete or insert is allow on this table. How do I reference the row and set the last_update_date on the table to the SYSDATE whenever the record is changed?

  38. I am getting the error “ORA-20001: Current version of data in database has changed since user initiated update process. ” when i try to add a new row. how do i resolve this?

  39. Hi,

    I have 3 Checkboxes in Tabular Forms to set. I tried yours solutions but doesn’t work.
    what is important to do?

    repeat same step for all checkbox?

    thanks

  40. hi patrick

    i have been working on this solution and i was a loser until i find ur solution. Thanks very much for that

    but i have question
    i have used a button instead of check box i need to write procedure of onclick but i cant figure out how should i do it

    if u can help me with this that would be gr8

    Thanks & Regards
    Sheshi

  41. Hi Patrick Wolf S
    This is my code
    for i in 1..htmldb_application.g_f02.count
    loop
    insert into nam values(htmldb_application.g_f02(i));
    end loop;

    Here i’m trying to insert the selected things but its taking all the records whatever is displaying in the report
    Kindly help me to get rid of the issue
    And guide me how can i insert only checked in records

Comments are closed.