icon-arrow icon-check icon-mail icon-phone icon-facebook icon-linkedin icon-youtube icon-twitter icon-cheveron icon-download icon-instagram play close icon-arrow-uturn icon-calendar icon-clock icon-search icon-chevron-process
Werken bij Whitehorses
Blog 30/04/2021

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

Tables_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.

Integratiespecialist
Michel Vogelpoel /
Integratiespecialist

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

Table structure of calender and agendapoins
The table structure of the calendar and agenda points

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

 

The result of added records to the iG
The result of the javascript

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 ).

 

The default value of the cal_id column
The default value of the cal_id column.

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

example application

Geen reacties

Geef jouw mening

Reactie plaatsen

Reactie toevoegen

Jouw e-mailadres wordt niet openbaar gemaakt.

Geen HTML

  • Geen HTML toegestaan.
  • Regels en alinea's worden automatisch gesplitst.
  • Web- en e-mailadressen worden automatisch naar links omgezet.
Integratiespecialist
Michel Vogelpoel /
Integratiespecialist

Wil je deel uitmaken van een groep gedreven en ambitieuze integratiespecialisten? Stuur ons jouw cv!