Pre-filling records in an APEX Interactive Grid using JSON and JavaScript

I was working at a client who was using the calendar functionality and wished that each calendar entity would have a meeting with several agenda points. But different kind of meetings could have different agenda points.

In this blog I will demonstrate how to add records to an APEX Interactive Grid using data retrieved from JSON and using JavaScript.

So the agenda points for a specific calendar were filled from a table containing standard agenda points belonging to a specific meeting type.

Of course you can create these standard list of agenda points after you have saved the calendar appointment. But if changes need to be made in the agenda points you will have to reopen it.

So I was investigating how to display a standard set of agenda points in an Interactive Grid table for a new calendar meeting without having to save it first.

How to enter a new record into an Interactive Grid using JavaScript?

Adding a new record to an Interactive Grid can be done via JavaScript. The column names in the Interactive Grid are AGENDAPOINT and REMARKS, the static_id of the region is IG_AGENDAPOINTS

var widget = apex.region('IG_AGENDAPOINTS').widget();

var grid = widget.interactiveGrid('getViews','grid');  

var model = grid.model; 

var myNewRecordId = model.insertNewRecord();

var myNewRecord = model.getRecord(myNewRecordId);
model.setValue(myNewRecord, 'AGENDAPOINT', value_agendapoint); 
model.setValue(myNewRecord, 'REMARKS', value_remark); 

How to translate the standard agenda point ORACLE SQL data from the table into this JavaScript?

You need somehow to put this data into a collection that you can loop through. The best way to do this is, is as a JSON format. So we need to create a JSON of the SQL Data and put it in an APEX field.
In my environment we didn’t have TO_JSON functions yet, so I had to create the JSON by hand.

select '['||(listagg( '{"agendapoint":"'||a.agendapoint||'",'||
                      '"remarks":"'||a.remarks||
                      '"}', ',') within group (order by a.agendapoint DESC))||
       ']' myJson
  into :P2010_STANDARD_AGENDA_JSON                             
  from ( select saa.agendapoint
              , saa.remarks
           from mv_standard_agenda saa
          where saa.meeting_type = :P2010_MEETING_TYPE 
       ) a;

The result of this query is a JSON structure like this:

[{
        "agendapoint": "Agenda3",
        "remarks": "Remark3"
    },
    {
        "agendapoint": "Agenda2",
        "remarks": "Remark2"
    },
    {
        "agendapoint": "Agenda1",
        "remarks": "Remark1"
    }
]

Now that the data is in an APEX field ( in my example field P2010_STANDARD_AGENDA_JSON ) we can loop through its data and use it to fill my IG table. This is the full code that is being used:

var widget      = apex.region('IG_AGENDAPOINTS').widget();
var grid        = widget.interactiveGrid('getViews','grid');  
var model       = grid.model;

var myJSONString = $v("P2010_STANDARD_AGENDA_JSON");
var myObject = JSON.parse(myJSONString);

model.clearData();

for ( var i = 0; i < myObject.length; i++) {
  var obj = myObject[i];
      
   //insert new record on a model
    var myNewRecordId = model.insertNewRecord();

    //get the new record
    var myNewRecord = model.getRecord(myNewRecordId);
    
    //update record values
    model.setValue(myNewRecord, 'AGENDAPOINT', obj.agendapoint);
    model.setValue(myNewRecord, 'REMARKS', obj_remark);     
};

// So to conclude, these are the steps:
// ORACLE table => JSON => javascript => new records in Interactive Grid

Additional information

For all this to work you already need access to the calendar’s sequence id  (e.g: :P2010_ID := mv_calendar_seq.nextval ).

In the Interactive Grid agenda points columns you will also have a reference column (CAL_ID) towards the calendar record (ID). This column is automatically filled by entering the Default attribute with the value of the calendar id ( P2010_ID ).

When a customer changes the meeting type, I create a Dynamic Action that queries the JSON data into the local APEX variable. After that I execute the JavaScript to fill the Interactive Grid.

Please note that the data in the JSON is being queried in descending order. This is done because in an Interactive Grid the records are being created on the first row each time.

For an example of this functionality I can point you to my APEX site:

https://apex.oracle.com/pls/apex/mvogelpoel/r/michel-s-examples/autofill_ig?
Username: demo
Password: demodemo