GlideRecord Mass update

[dmfranko]

I need to got back and recalculate a field for all resolved and closed incidents in my system, which is around 70k.   I've got something like the script below.   The problem is when I try to run it, takes a long time as somewhat expected and it also complains that it's a large dataset.   Are there any other options besides chunking this out via query?

r = new GlideRecord('incident')
r.addEncodedQuery("state=6^ORstate=7^opened_at<javascript:gs.dateGenerate('2016-01-14','20:00:00')")
r.query();
while(r.next()){
   var opened = r.opened_at;
   var resolved = r.resolved_at;
   var dc = new DurationCalculator();
   dc.setSchedule(gs.getProperty('incident.schedule'));
   var durSched = dc.calcScheduleDuration(opened,resolved);
   r.business_duration.setDateNumericValue(durSched*1000)
   r.business_stc = durSched;
   r.update();
}

[dmfranko]

So, this doesn't quite work.   It ends up infinitely looping because each time the query runs it returns the same record set.   If I was updating something that affected the query I believe it would work, but in this case I don't think it will.   Gave me some ideas though.

[Stijn Verhulst3]

You're right Dan. My apologies, didn't take the query you're using into account. However, I think it might work if you apply a workaround by excluding the already updated ones during the loops. This could be done asking for the sys id's and storing them into a seperate array. Here's a thought (let me know if this works for you):

var arrayUtil = new ArrayUtil();
var updatedIncidents = new Array();
var limit = 5000;
var runQuery = true;
var numbRec   = 0;
var totalRec = 0;

// Search for incidents. A limited number of records is set to keep the process under control.
// IMPORTANT NOTE: In case this script throws up the error "Compacting large row block", lower the 'setLimit()' below.
var inc = new GlideRecord('incident');

inc.setLimit(limit);
inc.addEncodedQuery("state=6^ORstate=7^opened_at<javascript:gs.dateGenerate('2016-01-14','20:00:00')");

inc.query();

// Start looping through the incidents (one loop covers the set limit above).
while (runQuery) {

   // Take the appropriate action on each of the found incidents and add 1 to the total number of processed records for each incident.
   while (inc.next()) {

           if (!arrayUtil.contains(updatedIncidents, inc.sys_id.toString())) {

                   var opened = inc.opened_at;
                   var resolved = inc.resolved_at;

                   var dc = new DurationCalculator();
                   dc.setSchedule(gs.getProperty('incident.schedule'));
                   var durSched = dc.calcScheduleDuration(opened,resolved);

                   inc.business_duration.setDateNumericValue(durSched*1000);
                   inc.business_stc = durSched;
                   inc.update();

                   updatedIncidents.push(inc.sys_id.toString());

                   totalRec += 1;

           }
   }

   // Search for the next limited number of incidents which met the same criteria.
   // In case at least 1 new has been found, restart the loop. If none has been found, stop the loop and print out debugging/logging information.
   limit += 5000;
   inc.setLimit(limit);
   inc.query();
   numbRec = inc.getRowCount();

   if (numbRec > 0) {

           runQuery = true;

   } else {

           runQuery = false;

           if (totalRec > 0) {
                   gs.log("Script execution ::: " + totalRec + " incidents have been processed.");
           } else {
                   gs.log("Script execution ::: no incidents have been processed.");
           }

   }

}