Oracle Apex: Loop Through Interactive Grid Records

Oracle Apex: Loop Through Interactive Grid Records

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

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:

This Post Has 37 Comments

  1. ankit bhatt August 9, 2020

    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

    1. Vinish Kapoor August 9, 2020

      Hi Ankit,

      To get the column changed information you have to specify the static id for each column in the grid. For example, for the email column you can specify the static id as sid_email.

      Now you can use the apex.item.isChanged() method in the loop as following:

      if (apex.item("sid_email").isChanged()) {
   // your code goes here
}
    2. ankit bhatt August 9, 2020

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

    3. Vinish Kapoor August 10, 2020

      You can write the email values to a hidden page item separated by comma and then create another TRUE action as PL/SQL code type and get this hidden item value using apex_string.split function and insert into the table.

  2. Tomasz Czernecki September 1, 2020

    Great work! Can I do this in interactive report?

    1. Vinish Kapoor September 2, 2020

      In interactive grid we loop through because if the interactive grid is editable then the user may have changed the data so we loop through to get the latest data.

      Interactive reports are read-only reports, you don't need to loop through interactive report.

      But if you require to loop the data then you can use the SQL query on which the interactive report is based on.

      Use that query in Cursor and loop through it.

    2. Tomasz Czernecki September 2, 2020

      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 🙂

    3. Vinish Kapoor September 2, 2020

      I think you have to use the SQL query only to count the value of a column and display it in a field.

      Or you can use the default computation interactive report feature to show the sum at the below.

      And even if you will replace it to interactive grid and loop through to count it will take more time than the SQL query.

    4. Lavanya September 3, 2020

      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.

    5. Vinish Kapoor September 4, 2020

      You can disable a link based on a column value and for this you have to create link in your SQL query.

      Check the following post, there are two methods explained to create a link in report. For your requirement, check the method 2.

      Create a link in interactive report.

  3. George September 24, 2020 
     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 ?
    1. Vinish Kapoor September 24, 2020

      I have just tested the code for grid based on EMP table and it works fine for me:

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

      Try to change it as text field. But I have tried with both type.

    2. George September 24, 2020

      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

    3. Vinish Kapoor September 25, 2020

      Oh yes, in another post, I have given example of parseFloat() also to handle decimal values. Here I missed.

      But that is great that you find the solution.

      To align right, I think you can change the type to Numeric Field then provide a format mask.

      Else for text field, apply the following CSS in Advanced > Custom attributes of the text field:

      style="text-align: right;"
    4. George September 25, 2020

      Ok, thank you

    5. Supriya February 23, 2021 
      How I can do style="text-align: right;"

      conditionally, while parsing data in loop?

  4. Monica Jain December 18, 2020

    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

  5. SamSam February 10, 2021

    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

    1. Vinish Kapoor February 11, 2021

      You have initialized and assigned a value to l_col1, but how about the Val1 and Val2? From where are you deriving its value?

      Also, make sure you specifying the item in uppercase in JS for example, $s("MY_ITEM1", "");

    2. SamSam February 11, 2021

      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.

  6. Supriya February 23, 2021

    How to change colour of column value while in loop conditionally

  7. Supriya February 23, 2021

    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;'
          
          
        }
    });

    1. Vinish Kapoor February 24, 2021

      Please ask your question on orclqa.com

  8. Lamine BENCHIKH February 25, 2021

    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.

    1. Vinish Kapoor February 25, 2021

      Try using the below command:

      var retValue, displayValue;
retValue = apex.item('PRODUCT_LIST').getValue();
displayValue = apex.item('PRODUCT_LIST').displayValueFor(retValue);

      But if still, you have some issues, please ask your question at https://orclqa.com so that community members can help you.

  9. Clarisa May 1, 2021

    Hi Vinish, thank you for your post. Is it possible to send the value of the sum in runtime to another IG where totals of each column are shown, on the same page instead of sending the value to an item?

    1. Vinish Kapoor May 2, 2021

      If the totals of another IG is in item, then you can simply add the value. Below is an example:

      $s("P32_AN_IG_ITEM", $v("P32_AN_IG_ITEM") + n_total);
  10. Kamran Sajjad May 13, 2021

    Hi, I want to put the total in P2_TOTSAL but exclude the deleted ones. Your example counts the deleted rows also.

    1. Vinish Kapoor May 13, 2021

      Try this one to exclude deleted ones:

      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) {
  var id = model.getRecordId( igrow ),
  meta = model.getRecordMetadata( id );
if ( !meta.deleted ) {
  n_sal = parseInt(igrow[col_salary], 10);
  if (!isNaN(n_sal)) {
    n_totsal += n_sal;
  }
}
 
});
console.log(n_totsal);
    2. Kamran Sajjad May 13, 2021

      It worked Thank you sir

  11. Mikel Kolici June 29, 2021

    Hi Vinish,
    I tried this code and I have the problem that it loops through only first 50 rows.
    I have simplified the code just to print in console log for debugging:

    var model = apex.region("igManageItems").widget().interactiveGrid("getViews", "grid").model;
    model.forEach(function(igrow) {
      

    fontColor=igrow[model.getFieldKey("FONT_COLOR")];

    console.log(fontColor);

    });

    It is fired on "page load".
    When I execute after the page loads, as an action of a button, it loops until the end (80 records).
    Please help me how to fix this issue.

    Thank you
    Mikel

  12. Anand July 7, 2021

    This is really good info. Does this work in APEX 4.2.6 version?
    I defined static ID for IR as irsoa
    And created a dynamic action for a button to execute javascript as below -

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

    But I get this error in console -

    f?p=158:79:33964728911852::::::203 Uncaught TypeError: apex.region is not a function
      at Object.javascriptFunction (f?p=158:79:33964728911852::::::203)
      at Object.da.doAction (desktop_all.min.js?v=4.2.6.00.03:19)
      at Object.da.doActions (desktop_all.min.js?v=4.2.6.00.03:19)
      at HTMLInputElement.<anonymous> (desktop_all.min.js?v=4.2.6.00.03:19)
      at Function.each (desktop_all.min.js?v=4.2.6.00.03:2)
      at init.each (desktop_all.min.js?v=4.2.6.00.03:2)
      at Object.da.actions (desktop_all.min.js?v=4.2.6.00.03:19)
      at HTMLInputElement.<anonymous> (desktop_all.min.js?v=4.2.6.00.03:19)
      at HTMLInputElement.dispatch (desktop_all.min.js?v=4.2.6.00.03:3)
      at HTMLInputElement.i (desktop_all.min.js?v=4.2.6.00.03:3)

    1. ANAND July 12, 2021

      I understood, this is not possible for apex version 4.2.6

  13. Theresa July 27, 2021

    I've been trying your examples to calculate the sum of a column in an IG. Every DA I tried (change, row initialization, lose focus) shows the old value of the column though. How can I get the current value? The requirement is that I show the sum as the users enter the data. So, if there is only 1 row of data it should show the single value and i get nothing because it's using the old value of the column. If there are two rows it would show the sum of both rows, but I'm only getting the total from the first record because again, it doesn't recognize the change on the 2nd row until you create a new row.

    My IG has a STATIC ID of FUND. The js code is from your example with the id/column name substituted. What am I missing? Does your example assume the user clicks the save button on the grid first?

    JS: on a da connected to the BALANCE column on lose focus (or change) gives the same undesired results.

    var model = apex.region("FUND").widget().interactiveGrid("getViews", "grid").model;
    var n_bal, n_totbal = 0;
    col_balance = model.getFieldKey("BALANCE");
    model.forEach(function(igrow) {
      n_bal = parseInt(igrow[col_balance], 10);
      if (!isNaN(n_bal)) {
        n_totbal += n_bal;
      }
    });
    console.log(n_totbal);

  14. Gregory Grimes August 18, 2021

    I have a developer that wants sum up a column in an IG which the above clearly demonstrates. However, he wants to skip that row if the value of another column is either 'X' or 'Y'. I have been trying an IF statement without success. At runtime I get a JavaScript error. I have created another variable to get the other field's value but my JavaScript skills are weak. Any suggestions, or references?
    Thanks.

    1. Vinish Kapoor August 18, 2021

      See the below example:

      var model = apex.region(“FUND”).widget().interactiveGrid(“getViews”, “grid”).model;
var n_bal, n_totbal = 0;
col_xy = model.getFieldKey(“YourColumn”);
col_tot = model.getFieldKey(“YourTotalColumn”);
model.forEach(function(igrow) {
  v_xy = igrow[col_xy];
  n_bal = igrow[col_tot];
  if (v_xy == "X" || v_xy == "Y") {
    n_totbal += n_bal;
  }
});
console.log(n_totbal);
  15. Alauddin October 1, 2021

    Dear brother ,
    Hope you are well. I am new in apex. I need your help to check duplicate column value in run time. I have used this code to stop duplicate value entry but it does not work.

    var model = apex.region("DC").widget().interactiveGrid("getViews", "grid").model;
    var v_ID;
    v_ID= model.getFieldKey("ID");
    model.forEach(function(igrow) {
    if (v_ID==model.getFieldKey("ID")){
    apex.message.alert("Duplicate Found");
    }
    });
    ------------------ I also used your duplicate value checking soluation

Comments are closed.