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:
Now save the changes and run the page. You will have the output as shown in the below image:
Best Blog for Oracle Apex , this works great as expected ..... Thank you
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.
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.
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
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.
Thanks
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.
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:
This is just a quick example, you can enhance it more.
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)
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;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)
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.
ok thank you 🙂
Basically my action is to redirect to another page after populating the data into collection
How do you redirect to another page after populating the collection?
Can I redirect to a modal page?
Add the below line to the end of the JavaScript code that is populating the collection:
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.
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
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});
what to pass in Request : ANY_REQUEST?
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
Try what @George did:
apex.page.submit ({ Request: "", Set: {'P29_YOURITEM': n_value}, ShowWait: true});so here my code would be like below
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});Hi
Great information.. but how to remove the row from collection when i deselect the row in the interactive grid
Any help would be highly appreciated...
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?
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());Ok, thank you
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.
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.
thank you
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?
You mean, you want to focus on record recently added?
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.
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:
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.
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.
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.
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
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..
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);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:
-----
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.
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.
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.
One more thing, if I replace the item with a value in my SQL query like this:
-----
where c.id = '1'
it works fine.
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
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
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 idHi,
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
Here is the answer to your question https://vinish.ai/2019/10/oracle-apex-interactive-grid-get-selected-rows-example.html#comment-1292
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
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]/[email protected]:
How do I retrieve the actual value of the list?
Thank you.
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"},[email protected],...
// 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);
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.
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.
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.
How the checkbox is automatically coming to you in the interactive grid ? or do we need to add APEX_ITEM.checkbox2 ?
This is a great article. It is working for me EXCEPT when I do shift-click in the interactive grid to select several rows. Maybe the calls happen too fast, but in this scenario, the calls to the Ajax callback process (to add the items to a collection) consistently skip entries, maybe because it's happening too fast? So for example if I highlight 10 records, usually only 9 or 8 make it to the collection, maybe because of the asynchronous calls. When I switch it to async=false, it works correctly but that is deprecated.
And of course, if I select rows individually, everything works fine.