Oracle APEX Interactive Grid Select Rows

Abstract

There two elements of working with the row selector in Oracle APEX Interactive Grids. Numerous articles have been written about how to process data from a row selected by the user. Few articles (as of 06JAN2023) discuss how to pre-select or select rows in an interactive grid.

If your post-processing effort for an Interactive Grid involves removing data that has not been selected by the user, then you run the risk of having a page process remove data the users care about.

There are three elements involved with this process, a process that resembles an Oracle APEX shuttle box. The elements are:

1. Identify the previously selected rows (typically a primary key)
2. Use a dynamic action on page load to select rows within the Interactive Grid
3. Use a dynamic action to process the rows selected by the user

1. Preselect Rows for an Interactive Grid

You will need a page item. The attributes must be hidden and Settings: Value Protected “no” as shows in the illustration.

The because we are jazzy and cool developers, we’ll use a native feature of an APEX page item where we use the source feature to generate a colon separated list. We used to do this with good old listagg, but now, here it is. Use the Source with Type of “SQL Query (return colon separated value):

This feature is great to use with shuttle box page items as well. Note that while your primary keys may be numeric, because we are delimited (separating) the values with a colon (:), the page item must be a varchar2 as shown in the illustration. Yes, we tend to call our primary key fields “PK”. When the field, which is normally numeric and singular may store 0, 1 or more primary keys we communicate that by using the suffix _PKS just to clue in the next teammate.

2. Preselect Rows for Interactive Grid

I found one reference to this trick and that took a lot of searching. Let me tip my hat in appreciation to romeuBraga (https://stackoverflow.com/users/9458495/romeubraga) for a contribution made on Stackoverflow. Here are the steps, and yes, they worked for me today on APEX 22.2 in January of 2023.

• Go to your Interactive Grid and give it a static id. I recommend lower case and dash or underbar as separators (region properties > Advance > Static ID. In this case, I called it order_contacts.

• Create a dynamic action (DA) on the page with your interactive grid
• The DA will fire on “page load”

• Create a true action that executes JavaScript Code.

// link: https://stackoverflow.com/questions/53554648/oracle-apex-ig-check-row-selector-on-page-load-based-on-db-table
// cmoore 06jan2023
//put in gridID the id of your IG
var gridID = "order_contacts";
var ig$ = apex.region(gridID).widget();
var grid = ig$.interactiveGrid("getViews","grid");

//the item "P3551_CONTACT_PKS" contains all the IDs separated by ":"
//change "P3551_CONTACT_PKS" for the correct name of your item
var ids = apex.item('P3551_CONTACT_PKS').getValue();
var aIds = ids.split(':');

//create an array of records
var aRecords = []
for(var i = 0; i < aIds.length; i++) {
    aRecords.push(grid.model.getRecord(aIds[i]));
}

//Set the selected records for your array of records
grid.setSelectedRecords(aRecords)

Note that I did modify the delimiter from the original post to accommodate the more frequently used colon (:) character.

3. Use a dynamic action to process the rows selected by the user

There are several videos and a LOT of articles about this process. Again, I take zero credit for the how-to on this. I am pasting the solution here so you, dear reader, can have a full solution set on one page.

• Create a dynamic action. In the properties, set When > Event to “Section Change [Interactive Grid], Selection Type: Region and select your region. For me, in this illustration, it is “contacts grid”

• Create a true action with the Action of “Execute JavaScript Code”

var i;
var i_contact_pks = ':';
var model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
  i_contact_pks += model.getValue( this.data.selectedRecords[i], "CONTACT_PK") + ':';
}

apex.item( "P3551_CONTACT_PKS" ).setValue (i_contact_pks);

What’s Next

You now have a colon-separated list of the primary keys that were selected by the user in this session and/or in a prior session. We run a server-side process with PL/SQL to update the data set. The call is just as boring as this:

sls_order_pkg.order_contact (
 P_ORDER_PK => :P3551_ORDER_PK,
 P_CONTACT_PKS => :P3551_CONTACT_PKS
);

We have the primary key of the parent table and the 0,1, or more primary keys for the child table. There you go.

More?

Jump into my podcast that follows a software development team working through projects. Join me here:

The Soul of an Internet Machine

• Category: APEX
• Tag: apex, Interactive Grid, oracle, oracle apex, plsql, software development, sql