Skip to content
Home / Database / How to Select Multiple Records in Oracle Apps Forms ( Multi-Select Check Box Tutorial)

How to Select Multiple Records in Oracle Apps Forms ( Multi-Select Check Box Tutorial)

In Oracle forms, you would have seen a multi-record block with a checkbox. When you click,  Select all – when the master checkbox is checked then all child checkboxes on the form should be checked. Unselect All – when the master checkbox is unchecked then all child checkboxes on the form should be unchecked.

This functionality is helpful to select and process multiple records at a time. This is how it should work,

  • Uncheck Master Check Box – All child checkbox should get unchecked
  • Check Master Check Box – All child checkbox should get checked
  • Uncheck child checkbox – Master Check box should get unchecked.

select-all-oracle-form-working

 In Oracle forms, you can use GO_BLOCK, GO_RECORD built-in, and for loop to achieve this. Oracle Apps has a built-in wrapper APP_RECORD.FOR_ALL_RECORDS on go_block, go_records which you can use to achieve it.

Let’s see how to do it.

APP_RECORD.FOR_ALL_RECORDS

This procedure executes the specified trigger for every record of the current block or the specified block. If you specify a block, the GO_BLOCK built-in fires. When finished, the cursor returns to the original record and item.

If the trigger fails, FORM_TRIGGER_FAILURE is raised and the cursor is left in the record in which the failure occurred. You can pass arguments to the specified trigger using global variables simply by setting the global variables before calling this routine. APP_RECORD.FOR_ALL_RECORDS fires once when there are no queried records.

Syntax

procedure APP_RECORD.FOR_ALL_RECORDS(block_name varchar2, 
                                     trigger_name varchar2);

procedure APP_RECORD.FOR_ALL_RECORDS(trigger_name varchar2);

Arguments (input)

block_name – The name of the block to navigate to
trigger_name – Name of the trigger to execute

Source: https://docs.oracle.com/cd/B40089_10/current/acrobat/120devg.pdf

1. Create a custom Oracle form

Create a custom form as shown below on any data source. I am using the DBA_OBJECTS table to create a form. This is a multi-record block with checkboxes.

Below is the layout of the form.

form-layout

There are two data blocks. Master which just has a check box and child which is a multi-record block. Let me show you the object navigator view.

object-navigator-view

2. Code and Logic

Master Block –

The master is a non-database block with a checkbox MASTER_CHECK_BOX.

Name Type Description
MASTER_CHECK_BOX CHECK BOX Non-Database Checkbox with Value Y and N.

Add a trigger WHEN-CHECKBOX-CHANGED trigger to MASTER_CHECK_BOX and add the below code. This trigger fires when the checkbox is checked or uncheck.

APP_RECORD.FOR_ALL_RECORDS('CHILD','CHILD_CBOX_HANDLE');

It is just going to the Block CHILD and firing the CHILD_CBOX_HANDLE trigger.

Child Block

The child block is a database block based on DBA_OBJECTS with the below items. All are the database item except the check-box.

Item Name Type Description
CHILD_CHECK_BOX Check Box Non-Database Checkbox with Value Y and N.
OWNER Text Item Database Item
OBJECT_NAME Text Item Database Item
SUBOBJECT_NAME Text Item Database Item
OBJECT_TYPE Text Item Database Item
STATUS Text Item Database Item

*IMP*- This procedure works on all the items fetched on forms irrespective of the displayed item. Restrict the number of the item to 10 by setting Maximum Record Fetched to 10 at the block level. This will only fetch 10 records; else it will take a lot of time as DBA_OBJECTS may have thousands of records.

Add CHILD_CBOX_HANDLE a user-defined trigger at the CHILD block level and below code.

:CHILD.CHILD_CHECK_BOX := :MASTER.MASTER_CHECK_BOX;

This code simply copies the value of MASTER_CHECK_BOX to CHILD_CHECK_BOX i.e if the master checkbox is check, check all child boxes.

Now, create a standard trigger WHEN-CHECKBOX_CHANGED to the CHILD_CHECK_BOX check box and below code.

IF :CHILD.CHILD_CHECK_BOX ='N' THEN
 :MASTER.MASTER_CHECK_BOX := 'N';
 END IF;

If either of the child box value is changed to unchecked, copy the same value to the master check box to uncheck it.

3. FTP and Compile form

FTP form to respective custom_top and compile using frmcmp_batch command. Register form with any Responsibility using Application Developer Responsibility.

That’s it and you have designed a master checkbox to control child checkbox