Interactive Grid - Bulk Operation on Selected Rows

What's the Problem?

Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is 

Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server.

So, is there any work around? 

Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook" or JavaScript API Reference documentation.

If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can write Dynamic Action (D.A.) as below.

• Create D.A. and give it a proper name e.g. "Bulk Update Commission"
• In "When" Section, select
• Event: Click
• Selection Type: Button
• Button: UPDATE_COMISSION (In this demo, button name is UPDATE_COMISSION, you may need to change it depending on your button name)
• True Action
• Action: Execute JavaScript Code
• Code: Refer below code

JS Code:

// Clear previous selections, if any

$s("P5_SELECTED_ROWS", "");
// IG static id is defined as emp
var gridView = apex.region("emp").widget().interactiveGrid("getViews").grid;
var records = gridView.getSelectedRecords();
var empNo;

// define empty JSON object to hold selected rows
var selRecords = {
	"rows": []
};

var selRecordsJSON;

if (records.length > 0) {
	if (confirm("Do you want to update commission for selected employee(s)? Please confirm.")) {
		$.each(records, function(i, r) {
			// employee number defined as PK for IG
			empNo = gridView.model.getRecordId(r);
			// you can also get other columns values using below code
			// ENAME is column name in IG query
			//empName = gridView.model.getValue(r, "ENAME");
			// Push selected rows into an array
			selRecords.rows.push({
				"empNo": empNo
			});
		});
		// convert JSON into string
		selRecordsJSON = JSON.stringify(selRecords);
		console.log(selRecordsJSON);
		apex.page.submit({
			request: "UPDATE_COMMISSION",
			set: {
				"P5_SELECTED_ROWS": selRecordsJSON
			},
			showWait: true
		});
	}
} else {
	alert("Please select atleast one employee.");
}

Note: When creating IG region, ensure correct column is selected as "Primary Key" column. If you have selected "ROWID" as Primary Key column, then above JavaScript code getRecordId will return ROWID value, not the primary key column value. In such cases, you need to modify server side PL/SQL (discussed below) accordingly, i.e. update variable to use ROWID type, change UPDATE statement to use ROWID column etc. If you use ROWID as Primary Key and use below PL/SQL code as is, then you will get "ORA-06502: PL/SQL: numeric or value error: character to number conversion error "

This code loops through selected rows and extracts Primary Key (PK) value for each row and pushes this value into an array. At the end, all these PK values are converted into JSON and it's assigned to hidden page item. Then, the page is submitted.

As we want to execute above D.A. when "Update Commission" button is clicked, we should set button's action (Button > Properties > Behavior > Action) to "Defined by Dynamic Action".

And finally, we can create regular PL/SQL page process at "Page Processing > Processing" section which execute below code. Make this process conditional based on request value used in above JavaScript code.

PL/SQL Code:  

DECLARE
    l_empno     emp.empno%type;
    l_values    apex_json.t_values;
    l_row_count PLS_INTEGER;
BEGIN
    apex_debug.message('inside process Bulk Update Commission');
    -- parse text to JSON
    apex_json.parse(p_values => l_values, p_source => :P5_SELECTED_ROWS);
    -- get number of rows selected
    l_row_count := apex_json.get_count(p_path => 'rows', p_values => l_values);
    -- loop through the selected rows
    FOR i IN 1..l_row_count LOOP
        -- get Primary Key value from JSON data
        l_empno := null;
        -- if primary key column type is not a number type, then remove TO_NUMBER part
        l_empno := TO_NUMBER(apex_json.get_varchar2(p_path => 'rows[%d].empNo', p0 => i, p_values => l_values));
        apex_debug.message('l_empno' || l_empno);
        -- update employee commission
        UPDATE emp
        SET comm = sal*TO_NUMBER(:P5_COMMISSION)/100
        WHERE empno = l_empno;
    END LOOP;
END;

In this example, we have updated the salary for selected employees. Once we get list of records selected in the IG, then we can do what ever we want based on our requirement. Other typical use case would be to add selected records into APEX Collection, so that we can process these records later. In such cases, we can use APEX_COLLECTION APIs to add selected records into a collection.

Here is the link to working example.

Please note, this works well with pagination type Scroll. But, if you are using pagination type as Page, then getSelectedRecords gives rows selected from current page alone. In these cases, we need to set persistSelection to true. To do this, go to "IG > JavaScript Initialization Code" and put following code.

function(config) {
    config.defaultGridViewOptions = {
        persistSelection: true
    };
    return config;
}

Thank you!