Check for any "closed complete" tasks on an RITM within the workflow

[booher04]

We have a need to check from within a workflow on a catalog item to see if there are any tasks(ad-hoc tasks created on the RITM not through the workflow) with the state of "Closed Completed".  We have a "wait condition" activity in place now that checks to make sure all tasks on that RITM are not active before the workflow will close, so I'd like to make something similar to check if there are any "closed completed" tasks before closing the RITM. 

The need for this is because we set the RITM state to match the Catalog Task created in the workflow, however if we "Closed Skipped" that catalog task, and we have an ad hoc task that was "Closed Complete" it doesn't allow us to set the RITM to "Closed Skipped" because of client script/business rule logic in place on the RITM.  

This is what we have in place now to make sure all tasks are closed before progressing the workflow to end.

var rec = new GlideRecord('sc_task');
rec.addQuery('request_item', current.sys_id);
rec.addQuery('active', true);
rec.query();
if(rec.hasNext()){
answer = false;
}
else{
//Continue
answer = true;
}

I tried changing things around from this but I can't get it to work properly.  Any ideas would be greatly appreciated.

[Tony Chatfield1]

Hi, a sc_task record can only be related to 1 sc_req_item (although a sc_req_item record may have multiple sc_tasks associated to it).
Meaning that if you are looking for ad-hoc sc_req_item records I think you need to be searching sc_req_item for relationships to it’s parent table IE sc_request.

So dot.walk or query to get the parent request sys_id and then query sc_req_item for records that have the 'request' field populated with the parent’s id.

• Depending on your workflow structure, it may be easier to populate a the request sys_id as scratchpad variable earlier in the WF.

[The SN Nerd]

The Workflow only runs when the RITM is updated, alas you'll need to do this via Business Rules and Events. 

1. In your workflow, add a Wait for Workflow Event called "request_item.all_tasks_closed" before your closure logic
2. Create an after update Business Rule on sc_task to check if there are any remaining open tasks. If so, fire the WF event. 

   // Copy code below into Business Rule
   var reqItemSysID = current.getValue('request_item');
   
   if (!hasOpenTasks(reqItemSysID ) ) {
   	broadcastWFEvent(reqItemSysID, "request_item.all_tasks_closed");
   }
   
   function hasOpenTasks(sysId) {
   	var numOpenTasks;
   	var rec = new GlideAggregate('sc_task'); // Best Practice: use GlideAggregate for counting records
   	rec.addQuery('request_item', sysId);
   	rec.addQuery('active', true);
   	rec.addAggregate('COUNT');
   	rec.query();
   	if(rec.query){
   		numOpenTasks = Number(rec.getAggregate('COUNT'));
   	}
   
   	return numOpenTasks > 0;
   }
   
   function broadcastWFEvent(sysId, wfEventName) {
   	var wf = new Workflow().getRunningFlows(sysId); 
   	while(wf.next()) { 
   		new Workflow().broadcastEvent(wf.sys_id, wfEventName); 
   	}
   }​

   This is the same design pattern as ServiceNow used in legacy Item Designer.

[booher04]

Ok, so let me ask this... that seems like a lot to have to do on every catalog item workflow, is there a better way to handle updating the RITM state field to match whatever the tasks state field is?  I've updated the original question with the screenshot of the workflow.