Dot walk field using GlideAggregate.

[Hari1]

Hi,

I need to get the reference fields from task table to task_slo table. But i don't see the right output that i am expecting.

Email script:

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {
	
	gs.log("Ageing- runMailScriptForNeedAttention");
	
	var finalJSONArray = [];
	var obj = {};
	var stateValue,getEscalatedValue,getReopenValue,getTtrValue,getRecommitValue,groupCount,assignmentGroup,numberVal1,assGroup1;
	
	var query = 'numberSTARTSWITHinc^ORnumberSTARTSWITHgreq^assigned_to=5bf08e8cdbb87300bda4d602ca96198b^active=true';
	var gr2 = new GlideRecord('task');	
	gr2.addEncodedQuery(query);
	gr2.query();
	if(gr2.next())
	{		
		var numberVal = gr2.number.getDisplayValue(); 
		gs.log("Ageing- numberVal: " + numberVal);
		
		var assGroup = gr2.assignment_group.getDisplayValue();
		gs.log("Ageing- assGroup: " + assGroup);
		
		var assTo = gr2.assigned_to.getDisplayValue();
		gs.log("Ageing- assTo: " + assTo);
		
		
        var inc = new GlideAggregate('task_sla');
       
		inc.addQuery('gr2.assigned_to', '5bf08e8cdbb87300bda4d602ca96198b');
		inc.addQuery('has_breached','true');
		inc.addQuery('active','true');
		inc.addAggregate('COUNT');
        inc.groupBy('has_breached');
		inc.groupBy('active');
		inc.query();
		
			if(inc.next())
			{
			gs.log("Ageing- Inside 2nd if task_sla 1st");
			gs.log("Ageing- details:" + inc.assignment_group);
			numberVal1 = inc.task.getDisplayValue();
			assGroup1 = inc.task.assignment_group.getDisplayValue();
			var assGroup2 = inc.gr2.assignment_group.getDisplayValue();
			var assGroup3 = inc.assignment_group.getDisplayValue();
			getEscalatedValue = inc.has_breached.getDisplayValue();
			
			gs.log("Ageing- numberVal1: " + numberVal1);
			gs.log("Ageing- assGroup1: " + assGroup1);
			gs.log("Ageing- getEscalatedValue: " + getEscalatedValue);		
			gs.log("Ageing- assGroup2: " + assGroup2);	
			gs.log("Ageing- assGroup3: " + assGroup3);	
			groupCount = inc.getAggregate('COUNT');

           if (JSON.stringify(obj) == "{}") 
			{
				gs.log("Ageing- inside if JSON");
				obj.assignment_group = assGroup1;
				obj.has_breached = {};
                obj.has_breached[getEscalatedValue] = parseInt(groupCount);
				obj.inc = parseInt(groupCount);
           }
			   else if (obj.assignment_group == assGroup1) 
			{
				gs.log("Ageing- inside else if");
                if (!JSUtil.nil(obj.has_breached[getEscalatedValue])) 
				{
                    obj.has_breached[getEscalatedValue] = obj.has_breached[getEscalatedValue] + parseInt(groupCount);
                } 
				else 
				{
                    obj.has_breached[getEscalatedValue] = parseInt(groupCount);
                }
			}
			   else 
			{
				gs.log("Ageing- inside else last");
                finalJSONArray.push(obj);
                obj = {};
                obj.has_breached = {};
                obj.has_breached[getEscalatedValue] = parseInt(groupCount);               
				obj.inc = parseInt(groupCount);
            }	
				
		
			}
		
           finalJSONArray.push(obj);
			 gs.info("Ageing- Special ININC-finalJSONArray: " + JSON.stringify(finalJSONArray));
		
	}
	})(current, template, email, email_action, event);

[Hari1]

I need the count of tickets that have breached the SLA. If it has i.e. "has_breached" is true. I need the count of incidents. 

[Alok Das]

I have modified your code and removed the logs and variables which were not used. Also, I removed the glide to incident and added all the queries at once in sla_task table query.

Please give a try and let me know if you face any issue with this script:

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
/* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
/* Optional GlideRecord */ event) {
	
	var finalJSONArray = [];
	var obj = {};
		var stateValue,getEscalatedValue,getReopenValue,getTtrValue,getRecommitValue,groupCount,assignmentGroup,numberVal1,assGroup1;
		
		var inc = new GlideAggregate('task_sla');
		inc.addEncodedQuery('task.numberSTARTSWITHinc^ORtask.numberSTARTSWITHreq^has_breached=true^task.active=true^task.assigned_to=62826bf03710200044e0bfc8bcbe5df1^active=true');
		inc.addAggregate('COUNT');
		inc.groupBy('has_breached');
		inc.groupBy('active');
		inc.query();
		if(inc.next())
			{
			numberVal1 = inc.task.getDisplayValue();
			assGroup1 = inc.task.assignment_group.getDisplayValue();
			getEscalatedValue = inc.has_breached.getDisplayValue();
			groupCount = inc.getAggregate('COUNT');
			
			if (JSON.stringify(obj) == "{}")
				{
					obj.assignment_group = assGroup1;
					obj.has_breached = {};
						obj.has_breached[getEscalatedValue] = parseInt(groupCount);
						obj.inc = parseInt(groupCount);
					}
					else if (obj.assignment_group == assGroup1)
						{
						if (!JSUtil.nil(obj.has_breached[getEscalatedValue]))
							{
							obj.has_breached[getEscalatedValue] = obj.has_breached[getEscalatedValue] + parseInt(groupCount);
						}
						else
							{
							obj.has_breached[getEscalatedValue] = parseInt(groupCount);
						}
					}
					else
						{
						finalJSONArray.push(obj);
						obj = {};
							obj.has_breached = {};
								obj.has_breached[getEscalatedValue] = parseInt(groupCount);
								obj.inc = parseInt(groupCount);
							}
						}
						finalJSONArray.push(obj);
					})(current, template, email, email_action, event);

Note: I have not tested the code, request you to go through a round of testing.

Kindly mark my answer as Correct and Helpful based on the Impact.

Regards,

Alok