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:

IF inserting or updating THEN
:NEW.modified_by := apex_application.g_user;
END IF;

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

SELECT apex_item.checkbox(1,empno,'UNCHECKED') " ",
empno,
ename,
modified_by,
ticked
FROM emp


4. Run the page and you should see that the results of the report are displayed along with a checkbox beside each row.
5. 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”.
6. 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.
7. 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.
8. Enter the following as the PL/SQL Page Process:

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;

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

This entry was posted on Friday, June 27th, 2008 at 8:15 am and is filed under Oracle APEX, Tutorials. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.