Wednesday 17 May 2017

APEX 5.1 Interactive grid row processing and dynamic actions

APEX 5.1 IG processing

Interactive grid row processing and dynamic actions

IG row based DA tips


You started working with IG and wanted to implement dynamic actions within your IG report region.

Idea here is: You have a column in IG that triggers a change and DA fires and retrieves data from database before displaying them in your row columns.

There is a mini catch that I had issues figuring out - why and where hence this post. 


If you start googling about APEX IG most likely you will end up in good hands of John Snyder and his "how to hack APEX IG" series. Excellent material and heaps of useful things you might need along the way. 

The one that was of most help to me was Christoph's blog. Gives you a basic idea how to manipulate IG rows and change values.

Now that you have all the material it is time for implementation. 

First thing is to create your interactive grid (including a static ID). Second step select a column on which you want a DA to trigger. Create a DA with JS below. Third step is creating a page process that will return values from database. 

My process GETDOCINFO is: 
DECLARE
   r_doc_info varchar2(150);    
BEGIN
-- This should be getting the cached document info
r_doc_info:= get_info(APEX_APPLICATION.g_x01);
     
        --BUILD JSON OBJECT
        apex_json.open_object;
        apex_json.open_array('item');
        apex_json.open_object;
        apex_json.write('id', 'DOC_ID');
        apex_json.write('docID',
r_doc_info);
        apex_json.close_object;
        apex_json.close_array;
        apex_json.close_object;
  
EXCEPTION when others then
   htp.p('"ERROR"');
END;

As you can see it is pretty basic example. 

Now to the JavaScript bit.
//SET IG ROW details to be used in a fnc
//if this is removed IG row/model can not be retrieved
var p_element = this.triggeringElement;
var p_rowId = $(this.triggeringElement).closest('tr').data('id');

var dID;

apex.server.process(
    "GETDOCINFO", {
        x01: this.triggeringElement.value //PASSING IN doc_ref
    }, {
        success: function(data) {
            //console.log(data)
            var i, item;
           
  // this handles the page items to return set by the server process
 // data result is object with property item which is an array of page item id, value pairs
            if (data && data.item) {                  
                for (i = 0; i < data.item.length; i++) {
                    item = data.item[i];
                    dID = item.docID;
                }
            } //end if        
           
            //Update IG columns
            set_IG(dID);
        } //success
    }
);



//notice how this is different from example in blog   
function set_IG (pID){
    //Get the link element that was clicked
    var $te = $(p_element);
   
    //Identify the particular interactive grid
    var ig$ = apex.region("event_docs").widget();
    //here you have to change to use static ID of your region
    

    //Fetch the model for the interactive grid
    var model = ig$.interactiveGrid("getViews","grid").model;
    

    //Fetch the record for the particular rowId
    var record = model.getRecord(p_rowId);
   
    model.setValue(record,"DOCUMENT_ID", pID);   
}


Only thing to keep in mind is in yellow lines. Without keeping a reference to a row and element triggering this DA you may start receive errors in your browser console toString property not found. Comparing to Christopher's example there is very little difference but original version simply was not working for me when being called from ajax callback as information about the row and model would be lost along the way

As example this JS below would not work: 
apex.server.process(
    "GETDOCINFO", {
        x01: this.triggeringElement.value //PASSING IN doc_ref
    }, {
        success: function(data) {
            //console.log(data)
            var i, item;
            if (data && data.item) {
                var dID,dName,dUrl;
                for (i = 0; i < data.item.length; i++) {
                    item = data.item[i];
                    dID = item.value;                   
                }
                set_IG(dID);
            } //end if          
           
        } //success
    }
);

function set_IG (pID){

    //Get the element that was changed
    var $te = $(this.triggeringElement);
    
    //Get the ID of the row
    var rowId = $te.closest('tr').data('id');
    
    //Identify the particular interactive grid
    var ig$ = apex.region("event_docs").widget();
    
    //Fetch the model for the interactive grid
    var model = ig$.interactiveGrid("getViews","grid").model;
    
    //Fetch the record for the particular rowId
    var record = model.getRecord(rowId);
   
    model.setValue(record,"EVENT_DOCUMENT_ID", pID);   
   
}


Alternative approach would be to base your JS on rows selected and do processing from there. Something like: 

var view = apex.region("event_docs").widget().interactiveGrid(" getViews", "grid");
var records = view.getSelectedRecords();
//console.log(records);
//console.log(records[0][1]);
//difference between input type and object like LOV
for ( i = 0; i < records.length; i++) {
    //if dealing with LOV for example
    console.log(view.model.getValue(records[i], "DOC_REFERENCE").v);
   
    //if dealing with regular text type for example
    console.log(view.model.getValue(records[i], "DOCUMENT_ID"));
}


Please note that there are heaps of different ways how to access data in IG model aim was only to bring to attention and what to keep an eye on.

This is all for now. Over and out.
 
Thanks,
SLino

8 comments:

  1. Hello,
    really nice code :). I have a similar problem. I have a interactive grid with some rows. If the user changes a value in one span it should change also a value of another span. for example: i have some owners when the user changes the owner it should change the owner id. owner id is only readable. Do you have any idea how i can manage this problem? would be really nice if you can help me.

    thanks a lot

    greets
    Alex

    ReplyDelete
  2. Hi Alex, this should not be to complex if you went through my example. Also please check out these: http://hardlikesoftware.com/weblog/2017/07/10/apex-interactive-grid-cookbook/ or https://ruepprich.wordpress.com/2017/03/09/apex-updating-interactive-grid-cells/ I am sure your problem will be solved. Please let me know if this does not help :)

    ReplyDelete
    Replies
    1. Hello Lino,
      thank you for your fast answer. I solved the problem with the JavaScript code but the problem is, that it is not dynamic. Not so easy. Maybe it is better to change the value with some sql trigger or procedure. Or is it possible to use PL/SQL to to change one cell if another cell would be changed by the user?

      Thanks again for your efforts and links.

      Greets
      Alex

      Delete
  3. Hi Alex, just been looking at Roel's blog post http://roelhartman.blogspot.com.au/2017/07/refresh-selected-rows-in-interactive.html. Would this be helpful to you? Any chance you can recreate an example on apex.oracle.com for me to have a look, to be sure 100% I get what you are after.

    Cheers,
    Lino

    ReplyDelete
  4. Hello Lino,

    Awesome blog..It is very Helpful. Thanks lino.

    I have a doubt

    How can we get current record value , previous record value and next record value when user clicks particular record in IG?

    Please help me on this.

    Thanks in Advance.

    ReplyDelete
  5. Hi Santhosh, thanks for your comments. I am currently busy working on few projects. Hope this is not urgent for you. You can try getting correct RowIds by changing selectors in yellow or better said adding new ones. As soon as it eases up on my I can have a look at it. :)

    ReplyDelete
    Replies
    1. Hi Lino,

      i have a select list for a page item and when i press the add row button of IG , the return value of select list should store in one column of IG.
      or if i change the select list of item ,the IG column lov should dynamically change. How is it possible . Please support

      Delete
    2. Hi, you can try with DA -> Event Row initialization for example and true action that has code similar to this

      var model = this.data.model,
      rec = this.data.record, //this is a row you want to change
      meta = model.getRecordMetadata(this.data.recordId);

      //set LOV
      if ( meta.inserted ) {
      if ( model.getValue(rec, "ACTIVE").v === "" ) {
      model.setValue(rec,"ACTIVE", {d:"Yes", v:"Y"});
      }

      }

      Delete