Press "Enter" to skip to content

Oracle Apex: Loop Through Interactive Grid Records

The following are some examples to show you how to loop through interactive grid records in Oracle Apex.

To get interactive grid records using the JavaScript, first, we need to specify the Static ID for the interactive grid report. To do this, click on your interactive grid and in the Advanced section, specify the Static ID as shown in the below image, I defined “igemp“:

Oracle Apex - specify static id for the interactive grid.

Now you have to choose the event on which you want to read all rows by looping through the interactive grid records. For example, you can create a dynamic action on any of the columns of an interactive grid and select the event Get Focus or Lose Focus, etc.

To demonstrate this example, I have used a button and created a dynamic action on a button click event to execute JavaScript code. And on click of the button, I will loop through all the records of the interactive grid and will print the console log.

The following is an example in which I will loop through all the records of the interactive grid and will print the column value of FRIST_NAME:

Example-1: Loop Through All Records and Print a Column Value

var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model;
model.forEach(function(igrow) {
   console.log(igrow[model.getFieldKey("FIRST_NAME")]);
});

Output:

Oracle Apex: Loop through all records output on console log.

Example-2: Loop Through Interactive Grid Records and Get the Column Value in a Variable

In the following example, it will get the FIRST_NAME and LAST_NAME columns value in variables, and then it will print on the console log.

var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model;
var v_fname, v_lname;
v_fname = model.getFieldKey("FIRST_NAME");
v_lname = model.getFieldKey("LAST_NAME");
model.forEach(function(igrow) {
   console.log(igrow[v_fname] +" "+ igrow[v_lname]);
});

Output:

Neena Kochhar
Lex De Haan
Valliru Pataballa
Alexanderia Hunold
Bruce Ernst

Example-3: Loop Through Records and Sum a Column Value

In the following example, it will calculate the total of the SALARY column and then will print on the console log.

var model = apex.region("igemp").widget().interactiveGrid("getViews", "grid").model;
var n_sal, n_totsal = 0;
col_salary = model.getFieldKey("SALARY");
model.forEach(function(igrow) {
    n_sal = parseInt(igrow[col_salary], 10);
    if (!isNaN(n_sal)) {
        n_totsal += n_sal;
    }
});
console.log(n_totsal);

You can get the values in an item on your page in Oracle Apex. For example, add the below JavaScript code line to the bottom of the above code to get the total salary value in an item P2_TOTSAL:

$s('P2_TOTSAL', n_totsal);

Reference:

Related Tutorials:

Have you found the answer to your question? If not, you can discuss it with me in the comments section below or join my Q&A community OrclQA.com for developers and ask your question. It is FREE.

Vinish Kapoor

Follow

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

guest
25 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
ankit bhatt
ankit bhatt
7 months ago

very helpful post.
one question: can we get only those column where value has been changed by user ?
e.g user changed email for id-106 then i want to get all values from email column

ankit bhatt
ankit bhatt
Reply to  Vinish Kapoor
7 months ago

Thanks for the reply!
can we insert these values directly to table? e.g. email column values

Tomasz Czernecki
Tomasz Czernecki
6 months ago

Great work! Can I do this in interactive report?

Tomasz Czernecki
Tomasz Czernecki
Reply to  Vinish Kapoor
6 months ago

You’re right. However, I wonder if it makes sense to execute the SQL query a second time just to count the value of one column and display it in a separate field. Wouldn’t it be better to count the column that is already displayed?
Edit:
Ok, my mistake, I just add aggregation to the report and that’s all what I need 🙂

Lavanya
Lavanya
Reply to  Vinish Kapoor
6 months ago

How do I disable a link in interactive report based on value in other columns of the report. how to loop through using java script or any feasible way.

George
George
5 months ago
 I have defined P2_TOTSAL as an item of type Number field, and it shows me an error 'must be numeric'.
But ntotsal is numerical because I calculate the sum well.
Any ideas ?

George
George
Reply to  Vinish Kapoor
5 months ago

I forgot to mention that they change ‘parseInt’ to ‘parseFloat’ to handle numbers with decimals.
With parseInt it correctly returns the value to an item of type Number Field.

With parseFloat it shows error ‘Must be numeric’.
Then I put the item as text and it works fine but I can’t find an option to align it to the right

George
George
Reply to  Vinish Kapoor
5 months ago

Ok, thank you

Supriya
Supriya
Reply to  Vinish Kapoor
22 days ago
How I can do style="text-align: right;"

conditionally, while parsing data in loop?

Monica Jain
Monica Jain
2 months ago

Hi Vinish,

I’m trying to insert almost 1000 records displayed in IG using apex_parser sql query into the database. but the insert process is only inserting 50 records. I have tried almost every approach I could find on internet but nothing solved my issue. Could you please suggest something for this issue.

Thanks,
Monica Jain

SamSam
SamSam
1 month ago

Hello Vinish,

I have a problem with my loop :
My grid is not read to the end by my loop, so I get wrong results.
This is my code :

  var model = apex.region("my_tab").widget().interactiveGrid("getViews","grid").model;
  l_count = model.getServerTotalRecords();   alert(l_count);
   
  //Remise à zéro 
  l_mnt1 = 0;
  l_mnt2 = 0;

  $s("My_Item1", "");
  $s("My_item2", "");

  if (l_count > 0) {

    //Parcourir tout le tableau en entier 
    model.forEach(function(record) {

            l_col1 = model.getValue(record, "COL1"); 

            if (l_col1 == Val1) {
              l_mnt1 = l_mnt1 + Number(model.getValue( record, "AMOUNT" ));                        
            } 

            if (l_col1 == Val2) {
              l_mnt2 = l_mnt2 + Number(model.getValue( record, "AMOUNT" ));                        
            } 
             
          } )
    }
   
  $s("My_Item1", l_mnt1 );
  $s("My_item2", l_mnt2 );

   
I have to go through the grid by hand to the end for it to work !
what is wrong with my loop ?
I am desperate !! Could you help me please ??

Thanks in advance

SamSam
SamSam
Reply to  Vinish Kapoor
1 month ago

Thank you for your reply.

It is like this :

if (l_col1 == "A")
...
if (l_col1 == "B")

A and B exist in my grid. My column l_col1 is equal either A or B.

Supriya
Supriya
22 days ago

How to change colour of column value while in loop conditionally

Supriya
Supriya
22 days ago

I have this javascript in button click dynamic action –
How to change text color of column?

var model = apex.region(“IG_GRID_ID”).widget().interactiveGrid(“getViews”, “grid”).model;
var v_data_type, v_col1;
v_data_type = model.getFieldKey(“DATA_TYPE”);
v_col1 = model.getFieldKey(“COL1″);
model.forEach(function(r) {
  console.log(r[v_data_type] +” “+ r[v_col1]);
  if(r[v_data_type] == ‘XXX’)
    {
     console.log(‘In XXX’);
     //Here I want to change the text color of COL1 to red
//How to do this??
//v_col1.css=’background-color:red;’ 

     // r[v_col1].style=’color: red;’
      
      
    }
});

Lamine BENCHIKH
Lamine BENCHIKH
20 days ago

Good Morning,

I’m having issue in this example , when i try this code i’m getting the return value not the display value of the cell. is there a way to fix that or should i set the return value the same as display value ?.

Regards.