Oracle Apex: Interactive Grid Get Selected Rows Example

This Oracle Apex tutorial shows you how to get selected rows of the interactive grid in Oracle Apex.

To demonstrate this example in Oracle Apex, I will create a blank page and then will add the following regions:

  • Employees (Interactive Grid)
  • Selection (Interactive Report)
  • Current Selection (Static Content)

The following EMPLOYEES table used in this tutorial, you can create it in your schema and insert some data to practice this example:

CREATE TABLE  "EMPLOYEES" 
   (	"EMPLOYEE_ID" NUMBER(6,0), 
	"FIRST_NAME" VARCHAR2(20), 
	"LAST_NAME" VARCHAR2(25), 
	"EMAIL" VARCHAR2(25), 
	"PHONE_NUMBER" VARCHAR2(20), 
	"HIRE_DATE" DATE, 
	"JOB_ID" VARCHAR2(10), 
	"SALARY" NUMBER(8,2), 
	"COMMISSION_PCT" NUMBER(2,2), 
	"MANAGER_ID" NUMBER(6,0), 
	"DEPARTMENT_ID" NUMBER(4,0)
   )
/

Step-1 Create an Interactive Grid Region

  • Title: Employees
  • Type: Interactive Grid
  • SQL Query:

Add the following SQL query in it:

select EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       EMAIL,
       PHONE_NUMBER,
       HIRE_DATE,
       JOB_ID,
       SALARY,
       COMMISSION_PCT,
       MANAGER_ID,
       DEPARTMENT_ID
  from EMPLOYEES;

Step-2 Create a Hidden Page Item for Region Employees created above.

  • Name: P9_EMPIDS
  • Type: Hidden
  • Value Protected: No

Step-3 Create a Region for Interactive Report

This report is to show the selected employee ids:

  • Title: Selection
  • Type: Interactive Report
  • Location: Local Database
  • Type: SQL Query (Enter the following SQL query in it)
SELECT t.Column_Value AS employee_id
      FROM TABLE(Apex_String.Split(RTRIM(LTRIM(:P9_EMPIDS, ':'), ':'),
                                   ':')) t
  • Page Items to Submit: P9_EMPIDS

Step-4 Create a Static Region to Show Last Selected Employee ID

This region is to show the most current (last) selected employee id.

  • Title: Current Selection
  • Type: Static Content

Step-5 Create a Page Item in the above Region (Current Selection)

  • Name: P9_CURRENT_EMPID
  • Type: Text Field
  • Label: Last Selected Employee ID

Step-6 Create a Dynamic Action for the Region Employees to get selected rows

Create a dynamic action for the region Employees created in step-1.

  • Name: da_select
  • Event: Selection Change [Interactive Grid]
  • Selection Type: Region
  • Region: Employees

True Action:

  • Action: Execute JavaScript Code
  • Code: Enter the following JavaScript Code:
var i, i_empids = ":", i_empid,

model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
    
    i_empid = model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID");
    
    i_empids += model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID") + ":";
    
}

apex.item( "P9_EMPIDS" ).setValue (i_empids);
apex.item( "P9_CURRENT_EMPID" ).setValue (i_empid);

The above JavaScript code will set the value for P9_EMPIDS in this format (:101:102:106:103:) for multiple employee ids. And will set the single employee id for the item P9_CURRENT_EMPID.

Step-7 Create another True action in the above dynamic action (da_select) as follows:

  • Action: Refresh
  • Selection Type: Region
  • Region: Selection (created in step-3)

Finally, you will have the regions and page items as shown in the following screenshot:

Oracle Apex - Page designer

Now save the changes and run the page. You will have the output as shown in the below image:

Oracle Apex - Interactive Grid get selected rows

See also:

Leave a Reply

This Post Has 56 Comments

  1. Madhusudhan Rao 9 Aug 2020 Reply

    Best Blog for Oracle Apex , this works great as expected ….. Thank you

  2. Anver 6 Sep 2020 Reply

    Great Information.. But i found an issue in this code.
    The current Employee Id shows only the largest Employee Id Selected, rather than the last selected one.

    Test Scenario :
    Select Employee Id : 107, then select Employee Id 100 or any id less than 107. Current Employee Id will still be 107. It will only change if we select Employee Id greater than 107.

    1. Vinish Kapoor 7 Sep 2020

      For the current selected employee, you have to click on the grid not on the checkbox or set multi-select property off for the grid.

      For multi-select, it will populate the P9_EMPIDS correctly.

  3. Marc 9 Sep 2020 Reply

    Hello,

    it works excellent. Just one question. I saved ids of the selected rows to a database and later I would like to preselect rows on my IG on a basis of the saved ids, is it possible something like this?
    Thanks

    1. Vinish Kapoor 9 Sep 2020

      You can do it. Follow this example:

      Create a hidden item for example, P3_SIDS and set the default value as the SQL query and add the following SQL query in it:

      select '[' || LISTAGG('"' || COLUMN_VALUE || '"' ,',') WITHIN GROUP (ORDER BY 1) || ']' as c from (
Select yourSIDScolumn a from yourtable where PKID = :P3_PKID), TABLE(APEX_STRING.split(a,':'))

      Now create a DA on Page Load to Execute JavaScript code and add the following JS code in it:

      var objt= JSON.parse($v("P3_SIDS"));
var ig$ = apex.region("YourIGStaticID").widget();
ig$.interactiveGrid("setSelectedRecords",objt);

      This is the tested code, works correctly.

    2. Marc 9 Sep 2020

      Thanks

  4. Raveena 16 Sep 2020 Reply

    Hi,

    This works great Thank you

    how to capture the selected entire row (EMP ID, FIRST NAME, LAST NAME , etc.) and pass it to the apex collection.

    1. Vinish Kapoor 16 Sep 2020

      To add the selected row to a collection follow these steps:

      Click on the process tab and create an Ajax callback process something like below:

      Declare
  n_empno Number;
Begin
  If Not apex_collection.collection_exists('EMP_COLLECTION') Then
    apex_collection.create_collection('EMP_COLLECTION');
  End If;

  Select
    Count(1)
  Into n_empno
  From
    apex_collections
  Where
    collection_name = 'EMP_COLLECTION'
    And c001 = :p25_empno;

  If n_empno = 0 Then
    apex_collection.add_member(p_collection_name => 'EMP_COLLECTION', 
    p_c001 => :p25_empno, 
    p_c002 => :p25_ename, 
    p_c003 => :p25_esal);
  End If;

End;

      Also, create 3 hidden items, P25_EMPNO, P25_ENAME, P25_SAL used in the above code.

      You can give the process name as populate_collection.

      Now change the on selection change dynamic action code as below:

      var i, i_empids = ":", i_empid, i_ename, i_sal,

model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
   
  i_empid = model.getValue( this.data.selectedRecords[i], "EMPNO");
  i_ename = model.getValue( this.data.selectedRecords[i], "ENAME");
  i_sal = model.getValue( this.data.selectedRecords[i], "SAL");

  apex.item( "P25_EMPNO" ).setValue (i_empid);
  apex.item( "P25_ENAME" ).setValue (i_ename);
  apex.item( "P25_ESAL" ).setValue (i_sal);
   
  apex.server.process('populate_collection',
{
  pageItems : '#P25_EMPNO,#P25_ENAME,#P25_ESAL'
}
,
{
  dataType : 'text', success : function(data)
  {
   if(data != 'SUCCESS') ;
  }
}
);
   
  i_empids += model.getValue( this.data.selectedRecords[i], "EMPNO") + ":";
   
}

apex.item( "P25_EMPID" ).setValue (i_empids);

      The above JavaScript code will call the ajax callback process populate_collection and it will populate the collection EMP_COLLECTION.

      To test you can create an interactive report on the same page with the following SQL query:

      select 
  c001,
  c002,
  c003
 from apex_collections
 where collection_name = 'EMP_COLLECTION'

      This is just a quick example, you can enhance it more.

    2. Raveena 16 Sep 2020

      Thank you for quick response, but here i am facing an issue

      the issue is the collection is displaying the old entries as well ( like first time i have selected first two records from EMP table and then next time last two records selected then the collection is displaying the four records rather than the last two recs selected)

    3. Vinish Kapoor 16 Sep 2020

      You said, that the next time you selected more two records and these records added to the collection.

      Do you mean after loading the page again?

      Or if you want that collection should be truncated after each page load, then you can write a dynamic action on page load to execute PL/SQL code and add the following code in it:

       if apex_collection.collection_exists('EMP_COLLECTION') then 
  apex_collection.truncate_collection('EMP_COLLECTION');
 end if;
    4. Raveena 16 Sep 2020

      yes i tried by adding plsql Code to truncate the Collection before it go for JS. but with this its only inserting the latest record into collection ( for example if i select rec1 and rec2 then its only inserting rec2 into collection not the both)

    5. Vinish Kapoor 16 Sep 2020

      You have to find the right event to empty the collection.

      Just notice, what actions you are performing after completing the selection from grid. For example, saving the grid, clicking on a button etc.

      Then for that event, you should write that code.

    6. Raveena 16 Sep 2020

      ok thank you 🙂
      Basically my action is to redirect to another page after populating the data into collection

    7. George 21 Sep 2020

      How do you redirect to another page after populating the collection?
      Can I redirect to a modal page?

    8. Vinish Kapoor 22 Sep 2020

      Add the below line to the end of the JavaScript code that is populating the collection:

      apex.submit();

      Click on the process tab, click on the branch node and create a branch to open your dialog page.

      Now after populating the collection the page will submit and the branch will execute to open another page.

    9. George 22 Sep 2020

      I had already done it but the window of the modal page remains in infinite cycle.
      If I try to call the modal page from another button it works fine,
      But I need to call the modal page from the DA with which I populate the collection

    10. George 22 Sep 2020

      Sorry, it worked well.

      I submitted a single item and that’s why it didn’t work

       Apex.page.submit ({
      Request: ‘ANY_REQUEST’,
      Set: {‘P29_SELECTED_ROWS’:  SelRecordsJSON},
      ShowWait: true});

    11. Raveena 24 Sep 2020

      what to pass in Request : ANY_REQUEST?

    12. Raveena 24 Sep 2020

      Hello vinish, i just added the apex.submit(); and then created a branch to GoToPage . But after submit its not redirecting to target page.

      Could you please help me in if i am missing anything.

      Thanks

    13. Vinish Kapoor 24 Sep 2020

      Try what @George did:

      apex.page.submit ({
Request: "",
Set: {'P29_YOURITEM': n_value},
ShowWait: true});
    14. Raveena 24 Sep 2020 
      Request: "",

      so here my code would be like below

      Request: "Target URL", ?
    15. Vinish Kapoor 24 Sep 2020

      Request should be only INSERT, UPDATE or DELETE.

      What is Target URL?

      Just pass the null value to request. Our purpose is to submit page and through the branch navigate to the another page. Try this only:

      apex.page.submit ({
Request: "",
ShowWait: true});
    16. VARSHA 20 Feb 2021

      Hi
      Great information.. but how to remove the row from collection when i deselect the row in the interactive grid

    17. VARSHA 21 Feb 2021

      Any help would be highly appreciated…

  5. George 17 Sep 2020 Reply

    Excellent blog.
    If the grid has several pages (1,2, … n) and I have selected rows from more than one page, does the code process the selected rows from all the pages I have visited or only from the page where I am located?

    1. Vinish Kapoor 17 Sep 2020

      If you have the pagination type scroll, it will work fine. But if you using pagination as page type, then create a dynamic action on page change event of the IG and copy the existing ids to another hidden item.

      Create one more hidden item, for example, P9_EMPIDS_1

      DA to execute JavaScript on page change event:

      apex.item("P9_EMPIDS_1").setValue(apex.item("P9_EMPIDS").getValue());
    2. George 17 Sep 2020

      Ok, thank you

  6. George 19 Sep 2020 Reply

    Hi Vinish,
    Is there any way to enable or disable the ability to select rows
    depending on the value of some column of the IG?

    For example:
    In the IG I show service invoices, some owed and others paid,
    I would need you to allow me to select only those owed and process them in some way.

    1. Vinish Kapoor 19 Sep 2020

      I didnt find the solution to disable it right now, but you can select only if the invoice type owed. For example, in the following code, it will select only employees if invoice type is equal to OWED:

      var i, i_empids = ":", i_empid, inv_type,

model = this.data.model;

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
   
  inv_type = model.getValue( this.data.selectedRecords[i], "INV_TYPE");

  if (inv_type == "OWED") {
    i_empids += model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID") + ":";
  }
}

apex.item( "P9_EMPIDS" ).setValue (i_empids);

      Now if even the user will select the paid invoice, but it will not select the employee here.

    2. George 20 Sep 2020

      thank you

  7. BRUNO SILVA 25 Sep 2020 Reply

    I have a grid where I do data manipulation using a sql pl, when I update the registry I use the command
    var model = apex.region (“event”). widget (). interactiveGrid (“getViews”). grid.model;
    model.fetchRecords (model._data);
    so that the focus remains on the record, however when inserting the record I did not find a way for the focus to fall on the released record, can you help me?

    1. Vinish Kapoor 26 Sep 2020

      You mean, you want to focus on record recently added?

    2. BRUNO SILVA 26 Sep 2020

      exactly. for example, I have a grid with 200 records, divided into 10 pages, suppose the user goes to page 5 and includes a record based on a record on this page, today, I update the grid, automatically the system launches me to the first page of the grid, with that I totally lose the record launched, it is spread out in the middle of the ordering of my grid. What I want is to simply stay on the grid page and focus on that inserted record.

    3. Vinish Kapoor 26 Sep 2020

      I have tested and it is working fine for me.

      I have a grid with lot of dummy employees, I clicked on the 4th page and added a record then saved using the Grid save button and the record focus remained there only.

      Below is the screenshot:

    4. BRUNO SILVA 26 Sep 2020

      right. Directly through the grid it works. Look at the image of my screen format. The grid I only use for navigation of the records. For inclusion, deletion and updating I use a form. When I do an update I use the command mentioned there at the beginning, and it works very well for me, keeping the record updated on the screen, without having to give an update on the complete grid, that is, it only makes me on the record. In the inclusion I have not found a solution, so far I have made a general update on the grid.

    5. BRUNO SILVA 26 Sep 2020

      right. Directly through the grid it works. Look at the image of my screen format. The grid I only use for navigation of the records. For inclusion, deletion and updating I use a form. When I do an update I use the command mentioned there at the beginning, and it works very well for me, keeping the record updated on the screen, without having to give an update on the complete grid, that is, it only makes me on the record. In the inclusion I have not found a solution, so far I have made a general update on the grid.

    6. Vinish Kapoor 26 Sep 2020

      Ok. Try this:

      On selection change DA add the following JavaScript code:

      var gridID = "yourIGID";
var ig$ = apex.region(gridID).widget();
var grid = ig$.interactiveGrid("getViews","grid");
var selectedRecord = grid.getSelectedRecords();
console.log(selectedRecord);
localStorage.setItem('lastSelectedRecord', JSON.stringify(selectedRecord));

      On page load DA:

      var gridID = "yourIGID";
var ig$ = apex.region(gridID).widget();
var grid = ig$.interactiveGrid("getViews","grid");

grid.setSelectedRecords(JSON.parse(localStorage.getItem('lastSelectedRecord')));

      It is using local storage to save the previous selection.

      Please try this and let me know. Thanks.

    7. BRUNO SILVA 12 Oct 2020

      in my case it didn’t work, but I used the commands

      apex.region (“document”). widget (). interactiveGrid (“addFilter”, {
        type: ‘column’,
        columnType: ‘column’,
        columnName: ‘SEQUENTIAL’,
        operator: ‘EQ’,
        value: $ v2 (‘P18_SEQUENCIAL’),
        isCaseSensitive: false
      });

      to take the inserted record and present

  8. VARSHA 23 Feb 2021 Reply

    Great work…
    1.I tried with adding entire row and pass it to collection..working fine but when i deselect the row it is not refreshing the collection..
    i created dynamic action ‘refresh region’ after javascript code.
    2.I created ‘ page load’ dynamic action to truncate collection on every page load but it is truncating when i do page load two times.
    Any help would be highly appreciated..

    1. Vinish Kapoor 23 Feb 2021

      You need to truncate the collection before loop. Below is an example:

      var i, i_empids = ":", i_empid,

model = this.data.model;

// below line will clear the item and will take the effect on select/deselect.
// you can clear your collection at this point.
apex.item( "P9_EMPIDS" ).setValue ('');

for ( i = 0; i < this.data.selectedRecords.length; i++ ) {
    
    i_empid = model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID");
    
    i_empids += model.getValue( this.data.selectedRecords[i], "EMPLOYEE_ID") + ":";
    
}

apex.item( "P9_EMPIDS" ).setValue (i_empids);
apex.item( "P9_CURRENT_EMPID" ).setValue (i_empid);
  9. Evans 5 Apr 2021 Reply

    Hey Vinish,
    This is great work. I have followed these instructions and managed to create a page item with the designed value selected from the IG row. I can display the value dynamically, all working fine. Now I’m trying to make use of this item in a SQL query or list on the same page, and that’s where things are failing. For the Sql query I’m using the page item int he WHERE clause like so:
    —–

    where c.id = :P9_CURRENT_EMPID

    What could I be doing wrong?

    Also when I try to use the item in a marque list, it doesn’t work. Again, I can display the item value quite fine. However, if I try to use a static page item int he marque list it works fine. I’m baffled. I’ve spent some long hours on this now. I’ll appreciate your help.

    1. Vinish Kapoor 7 Apr 2021

      When using any page item for SQL or PL/SQL then you should submit it to make it work. You will find a setting/property Submit Items, specify this page item there and it will work.

    2. Evans 9 Apr 2021

      Thank you! This sorted out my SQL issue. I appreciate you help.

      I’m still scratching my head about the List which still isn’t working.

  10. Evans 5 Apr 2021 Reply

    One more thing, if I replace the item with a value in my SQL query like this:
    —–
    where c.id = ‘1’

    it works fine.

  11. REG 17 Apr 2021 Reply

    Hello, i followed the same steps but i didn’t get the result, all the region are empty : (selection and curent_empid) I don’t know why!! i’m using apex5.1.4. Thank you

  12. Dipling 28 Apr 2021 Reply

    Hello.
    Great solution. One question. Is is possible to select all records on page load by default and have all the selected row values? So by default all rews would be selected. Then the user would deselect rows he doesn’t want. And the save selected or process them.

    BR,
    Dip

    1. Vinish Kapoor 29 Apr 2021

      On page load, create a dynamic action to execute JS code and add the following code in it:

      apex.region("IGStatic").widget().interactiveGrid("getViews", grid").view$.grid("selectAll");
// change IGStatic with your IG static id
  13. RRR 4 Jun 2021 Reply

    Hi,
    I am following your post and got results as expected, Thanks.
    I am facing 1 issue ,I am putting all the selected rows of IG into apex collection and then using collection I am inserting into my database table. Only issue is when I deselect a record after selecting it, it is still getting inserted into collection and deselection isnt removing which is obvious as I havn’t coded it anywhere. what code should I add to ensure only selected records gets inserted into collection.
    Thanks in advance for your help

    2. RRR 28 Jun 2021

      Thanks again for your help.
      I am given an additional requirement to not only work on the selected records but also when a record is selected, user needs to input a field(relation type for each selected record,which i have given as a drop down.)
      I am stuck how to pass the relation type to database.
      Selected records i was able to pass via collections to database.

      Appreciate your help and suggestions.Thanks
      b

  14. Phyllis Butler 24 Jun 2021 Reply

    This was helpful and works when the key is the field itself, not a lookup (select list). I have a file whose PK is LIST, EMAIL. On the form, for the LIST field, I’m using a select list from shared components.

    var i, i_ids = “:”, i_id, i_email, i_list,
    model = this.data.model;
    for ( i = 0; i < this.data.selectedRecords.length; i++ ) {    
        i_email= model.getValue( this.data.selectedRecords[i], “EMAIL”);    
        i_list = model.getValue( this.data.selectedRecords[i], “LIST”);   
        i_ids +=i_list + “/” + i_email + “:”;    
    }
    apex.item( “P9_KEYS” ).setValue (i_ids);

    So the value of P9_KEYS is appearing as :[object Object]/aaa@xyz.com:

    How do I retrieve the actual value of the list?

    Thank you.

  15. Phyllis Butler 24 Jun 2021 Reply

    I figured this out.

    var i, i_ids = “:”, i_id, i_email, i_list
    var model = this.data.model;
    var obj;
     
    for ( i = 0; i < this.data.selectedRecords.length; i++ ) {    
        i_email= model.getValue( this.data.selectedRecords[i], “GZRATDL_EMAIL_ADDRESS”);    
        obj = this.data.selectedRecords[i]; // [{“v”:”DL_ADM_IT”,”d”:”List of ADM_IT Users”},aaa@xyz.com,…
    //    i_list = model.getValue( this.data.selectedRecords[i], “GZRATDL_ATDL_NAME”); //would work if not using select list  
        i_list = obj[0].v; //0th in array, v element   
        i_ids +=i_list + “/” + i_email + “:”;    
    }
    apex.item( “P9_KEYS” ).setValue (i_ids);

    1. Phyllis Butler 1 Jul 2021

      For some reason, this is not working now, although when I wrote the above, it was working. Now when I click on the checkbox, it is not showing me the v field that I expected, but another v value in the select list. At some point, I changed the Processing from IG-Interactive Row Processing (DML) to Execute Code, then changed it back.

    2. Phyllis Butler 2 Jul 2021

      My file had a primary key of list+email. In the IG, I had set “primary_key” on for each field. That was causing a problem. Once I unchecked those, selecting the records with the checkbox works correctly.

  16. Krish 16 Jul 2021 Reply

    Hi Folks,
    Please suggest the way to achieve my requirement

    I have an Editable Interactive Grid and a Classic Report in my page.

    Requirement :
    A classic report region should be shown when the compliance_code value is selected as “ESCALATION” in the Interactive Grid.

    Note: Unless and until the compliance_code value = “ESCALATION” exists in the Interactive Grid list, the Classic Report region should be visible. 

    Once it is changed or not in list, the Classic Report region should be hidden.

    On clicking a Add Row button a new row will be displayed with 4 columns.(compliance_code(Select List value), Reason, Notes, Remarks)
    By selecting the below values,
    Row 1. Compliance_code = “Testing”
    Row 2. Compliance_code = “ESCALATION”
    Now the classic report region gets displayed. On clicking Add Row for the next record,
    Row 3. Compliance_code = “Performance”
    The region should still be displayed.

    On changing the Row 2 (Compliance_code = “ESCALATION”) value to some other value 
    The region should be hidden.

  17. RADSAN 20 Jul 2021 Reply

    How the checkbox is automatically coming to you in the interactive grid ? or do we need to add APEX_ITEM.checkbox2 ?