Adding a Checkbox to Your Report – APEX_ITEM Tutorial

OK, so you’ve got a report, but you want something more. You want to add functionality to your report. You want to be able to tick things and have it do stuff. Luckily there’s an easy way and it’s called APEX_ITEM.

This tutorial will talk you through adding a checkbox to your report and adding functionality to your page to identify records that are ticked and processing them.

This tutorial assumes you have a workspace with the Sample Application installed.

I’ve used checkboxes in reports in a number of different scenarios in my applications but the most useful has been as part of a confirmation screen. When a user has selected to make a change that has an effect on a number of different records, like removing a task from a schedule, then a page is displayed listing all the records that will be changed. Each record has a checkbox and only those that are ticked will be updated. This allows the user to check what the application will do before it does it and to select records to exclude from the processing.

This tutorial will show you how to add the checkboxes and then add processing that only fires for records that are ticked. This uses the APEX_ITEM API which allows a number of different items to be added to pages in a similar way. This API has infinite uses and I recommend looking in the APEX help pages for more information.

In the Object Browser:

NB: If you have worked through the Using Built-in Substitution Strings Tutorial – Part 1 on this blog then skip to step 3.

  1. Add new column varchar2(50) called “modified_by” on the EMP table.
  2. Add new before row trigger to the EMP table called “emp_btrg” with the following code:
  3. IF inserting or updating THEN
    :NEW.modified_by := apex_application.g_user;
    END IF;

  4. Add new column varchar2(1) called “ticked” on the EMP table.

In the Application Builder:

  1. Add a new blank page to your application with a name of “Tutorial”.
  2. Select the third option regarding tabs to Use an existing tabset and create a new tab within this tabset. Click Next twice, name the new tab “Tutorial”, click Next and then Finish.
  3. On this page create a new SQL Report region with a title of “Tutorial”. When prompted in the wizard use the following SQL Query:
  4. SELECT apex_item.checkbox(1,empno,'UNCHECKED') " ",
    empno,
    ename,
    modified_by,
    ticked
    FROM emp

  5. Run the page and you should see that the results of the report are displayed along with a checkbox beside each row.
  6. Edit the page and add a new button. This should be in the Tutorial region, in a Region Position. Give the button a name of “Submit”.
  7. Click Next until prompted to enter a page to branch to. Enter the name of the page you are adding the button to then click Create Button.
  8. Now click to Add Processes and select PL/SQL. Enter the name as “Submit” and ensure the point is On Submit – After Computations and Validations, before clicking Next.
  9. Enter the following as the PL/SQL Page Process:
  10. BEGIN
    FOR i in 1..APEX_APPLICATION.G_F01.COUNT LOOP
    UPDATE emp
    SET ticked = 'Y'
    WHERE empno = APEX_APPLICATION.G_F01(i);
    END LOOP;
    COMMIT;
    END;

  11. Then click Create Process.

Now run your page. If you tick a number of records and click Submit you’ll see that the Modified By and Ticked columns are updated for these records only.

Let’s look a bit closer at how we did this. The first bit of important code is:

apex_item.checkbox(1,empno,'UNCHECKED')

The first parameter is the id that is later used when calling APEX_APPLICATION. Had this been 12 then the second piece of code in the process would have referred to APEX_APPLICATION.G_F12 instead of G_F01. The second parameter is the value that we want to give the checkbox and the third parameter is the default status of the checkbox. Had we wanted all the checkboxes to be ticked by default this parameter would be “CHECKED”.

The second piece of important code is the references to APEX_APPLICATION:

FOR i in 1..APEX_APPLICATION.G_F01.COUNT LOOP

Here we are looping round all the records in the array that relate to the checkbox. APEX_APPLICATION stores the value of any checked checkboxes in an array with the id used by the checkbox. This allows us to then issue the UPDATE statement referencing the value in APEX_APPLICATION.G_F01 as earlier we told APEX_ITEM that this value should be the empno.

Hopefully this tutorial has given you an overview of how APEX_ITEM along with APEX_APPLICATION can be used to create sophisticated pages in Oracle Application Express.

Sara

Sara Blair is a freelance Oracle developer with over 10 years Oracle experience. She has been working with Oracle APEX for nearly 4 years since htmldb 1.5, and is available to hire through Silvercore Solutions.

13 Responses to “Adding a Checkbox to Your Report – APEX_ITEM Tutorial”

  • TM says:

    Thanks for the info – nice work – this site provided not only the way to create(render) a page with apex_item BUT also the way to process the apex_item array after a page submit. THANKS!

  • lwu says:

    This tutorial give a good example. In this case, the title for the check box column should be a space. But how can we make the column heading also as a check box and when we click the heading checkbox, all check boxes are checked. Thanks.

  • bustiuci says:

    I’m beginner in Oracle Application Express Platform.

    This article was very, very useful for me to build a report with few columns which contain type of data coming to be edited using checkboxes.

    Thank you.

  • Ravi says:

    Thanks for such valuable information.

    But, I’m facing problem, when we have selected from list of some values in report region and we go to next value of that report region then previous value become UNSELECTED.

    I’m not getting how to get rid of this issue.

    Can any one of you help me out.

    Thanks & Regards,
    Ravi Goyal

    • Sara says:

      Hi Ravi

      I’m not sure I fully understand what you are doing. Are you saying that you make a selection then refresh the page or go to another page and you selection is lost? If so, the default value for apex_item is to be unchecked. You would need to tell it if you wanted it to be checked. So if you make a selection and go to the next page, you would need to remember your selection on the first page using apex_application and then use the values stored here to structure your query to have the selection checked.

      I hope this answers your query, if you need further information please provide more specific details on what you are trying to achieve and I’ll try my best.

      Regards
      Sara

  • Chris Phillips says:

    Hi,
    The issue of preserving ticks is covered in :

    http://joelkallman.blogspot.com/2008/03/preserving-checked-checkboxes-in-report.html

    My question about the example you give here is whether there is an easy way to ‘untick’ the checkbox and make that update the column on the underlying table back from ‘Y’ to ‘N’. I don’t think so since the checkbox array is sparse, but am open to ideas.

    Thanks,
    Chris

    • Sara Blair says:

      Hi Chris

      As I’m sure you’re aware the checkbox value is only held in the apex_application array if the value is ticked. The easiest way I’ve found is to set all the records in the select list to N then loop round those values in the array setting them to Y. Alternatively, you could check each record that is set to Y to see if it is in the array and if it isn’t set it to N.

      Unless you have functionality that fires from database triggers when the record is updated to N, I’d go with the first option.

      Hope this helps. If you find a better solution, I’d be interested to hear it.

      Regards
      Sara

  • mohan says:

    hi sara,

    the problem you understood is correct. Im having the same problem. after selecting few check boxes in one page, when go to next page and come back to previous page, the selected items in previous page appearing as unchecked.
    How to resolve this. can u explain with detailed steps.

    Mohan

  • Sherry says:

    It is very helpful!

  • VJ says:

    Hi Sara,

    The solution provided here is for sql report, but when I try this for the interactive report it’s not working. Is there anyway I can have a checkbox column in an interactive report?

    VJ

    • Sara Blair says:

      Hi VJ

      You haven’t said in what way it isn’t working, so I’ve assumed it’s either displaying the html text rather than a checkbox or it’s not displaying the column at all.

      If you’ve added this column, after creating an Interactive Report then when you run the report you will have to select that this column is displayed using the action menu.

      If the report is displaying the column as HTML rather than a checkbox, then check your Interactive Report attributes and ensure that this column is set to display as “Standard Report Column”.

      Hopefully this will resolve your problem.

      Regards
      Sara

  • Jimmy says:

    This tutorial give a good example. In this case, the title for the check box column should be a space. But how can we make the column heading also as a check box and when we click the heading checkbox, all check boxes are checked. Thanks

    • Sara Blair says:

      Hi Jimmy

      Yes this certainly possible. To have a checkbox as the heading you will need to set the heading to be the HTML code to display a checkbox, e.g. . This will replace the   heading in the report attributes, and when the report is run will be converted to a checkbox.

      The more complex part is adding the code to flag all the checkboxes to be ticked. You will need to write some JavaScript code for this and add it to the checkbox’s onclick event. Unfortunately I haven’t the time to write all the code now, but I will try to write a full blog post on this in the next few days. If you can’t wait then I suggest looking at jQuery to help write the code required.

      Regards
      Sara

Comments can no longer be submitted.