Find all records without active children records?

[Shane J]

My question is more specific to Request Item and children Catalog Tasks, but the logic would probably apply to any parent/child or one/many relationship.

I'd like to setup a scheduled job to auto-close any RITMs that don't have any active children Catalog Tasks, but I'm not sure how to query to find the records.   We don't assign RITMs to groups in our environment, so if somehow all of the active Catalog Tasks get closed without the parent RITM getting closed, the RITM just hangs out there in an active state.

I suppose another wrench could be that a RITM could be awaiting an Approval, but have no active Catalog Task.  

[Shane J]

Like so?   I ran this and it didn't pickup any 'bad' RITMs (which surprises me if true).

//for all active RITMs

var rzero = new GlideRecord('sc_req_item');

rzero.addQuery('active', 'true');

rzero.query();

while (rzero.next()) {

  var cat = new GlideRecord('sc_catalog');

  cat.addQuery('parent', rzero.sys_id);

  cat.addActiveQuery();

  cat.query();

  if (cat.hasNext()) {

  gs.log(rzero.number + " is fine");

  } else {

// rzero.state = '3';

// rzero.update();

  gs.log(rzero.number + " has no children tasks");

  }

}

[Brad Tilton]

You're looking at the wrong table for the tasks. It should be sc_task rather than sc_catalog. Also, for your addquery I would force the sys_id to a string, so rzero.sys_id.toString()

[Shane J]

OK, so here my current script with your changes as well as the check for Approvals:

//for all active RITMs

var rzero = new GlideRecord('sc_req_item');

rzero.addQuery('active', 'true');

rzero.query();

while (rzero.next()) {

  //Check for Catalog Tasks

  var cat = new GlideRecord('sc_task');

  cat.addQuery('parent', rzero.sys_id.toString());

  cat.addQuery('active', 'true');

  cat.query();

  if (cat.hasNext()) {

  gs.log(rzero.number + ' ' + cat.number + ' is fine');

  } else {

  //check for Approvals because it does not have any active Catalog Tasks

  var app = new GlideRecord('sysapproval_approver');

  app.addQuery('sysapproval', rzero.sys_id.toString());

  app.addQuery('state', 'requested');

  app.query();

  if (app.hasNext()){

  rzero.state = '3';

  rzero.close_notes = 'Request found to be abandoned.';

  rzero.update();

  gs.log(rzero.number + " has no children tasks so it is being closed");

  }

  }

}

Any idea why the bolded piece comes back as 'undefined' in the log?   Did I miss anything obvious?

[Brad Tilton]

It's because hasNext() is not actually taking you into the record, but returning a true or false. If you need to get into the record to log something you'd need to do:

if (cat.hasNext()) {

    if (cat.next()) {

        gs.log(rzero.number + ' ' + cat.number + ' is fine');

    }

}

The nice thing about hasNext() is that it's a little more lighter weight when you're doing all of these queries, especially when you don't need the values from the task records, you just need to know if they exist.

[Shane J]

Nice, thanks for the assist!

For anyone's reference who is perusing this thread, I put together a Request clean-up script as well to run after the one I initially inquired about:

//for all active Requests

var reqzero = new GlideRecord('sc_request');

reqzero.addQuery('active', 'true');

reqzero.query();

while (reqzero.next()) {

  var ritm = new GlideRecord('sc_req_item');

  ritm.addQuery('request', reqzero.sys_id.toString());

  ritm.addQuery('active', 'true');

  ritm.query();

  if (ritm.hasNext()) {

  if (ritm.next()){

  gs.log(reqzero.number + ' ' + ritm.number + ' child RITM.');

  }

  } else {

  reqzero.state = '3';

  reqzero.update();

  gs.log(reqzero.number + ' has no active children requests');

  }

}