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:
See also:
- Oracle Apex: Highlight Row on Link Click of Interactive Report
- Oracle Apex: Set Page Items Value Using JavaScript on the Click of a Link in Interactive Report
- Oracle Apex – Add Interactive Grid into a Form
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:
Now create a DA on Page Load to Execute JavaScript code and add the following JS code in it:
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:
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:
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:
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:
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:
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:
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:
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:
On page load DA:
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: