email notification script to return multiple values

[Hari1]

Hi All,

We have created an email notification script to create a report and i am not able to see all the records.

notification script:

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {

        baseUrl = gs.getProperty("glide.servlet.uri");
	email.setSubject("User- User ticket details : " + gs.now());
	
	var arr = [1,2,3,6,7,8]; //New,In Progress, On Hold, Resolved,CLosed,Canceled
	var storeCount = [];
	var listCount = {};
	var x = 0;
	var getGroup,getCountInProg, getCountOnHold, getCountNew,getState,cc, getStateVal,getIncCount;
	while(x < arr.length)
	{
		gs.log("User-Inside while");
		var gr = new GlideRecord('incident');
		gr.addQuery('active',true);
		gr.addQuery('assigned_to','46d44a23a9fe19810012d100cca80666' );
		gr.addQuery('state',arr[x]);
		gr.orderByDesc('state'); 
		gr.chooseWindow(0, 1);
		gr.query();
		while(gr.next())
		{
			gs.log("User-inside if");
		
			getState = gr.getValue('state');
			
			if(getState == arr[x])
				{
					getGroup = gr.getDisplayValue('assignment_group');
					getStateVal = gr.getDisplayValue('state');
					cc = parseInt(gr.getRowCount());
					listCount = {Groups: getGroup,State: getStateVal, DataVal : cc};
					storeCount.push(listCount);
					gs.info('Count Values is: ' + JSON.stringify(listCount));
				}
		}
			
			
		x++;
		
	}
	gs.info('StoreVal is: ' + JSON.stringify(storeVal));

	
	template.print("<table border=2>");
	template.print("<tr><td colspan=5 align =center>List Of Tickets</td></tr>");
	template.print("<tr><th>Assignment group</th><th>State</th><th>Count</th></tr>");
	
	storeCount.forEach(mySortingFunction);
	function mySortingFunction(item, index)
	{
		gs.log("User- Inside function");
		template.print('<tr><td>' + item.Groups + '</td><td>'+ item.State + '</td><td>'+ item.DataVal + '</td></tr>');
	}

})(current, template, email, email_action, event);

I am seeing the output as below:

Expected output needs to be based on state and assignment group in the above output i am not able to see the assignment groups "Hardware" and "Network" Instead i am able to see only 2 group and the count based on state :

I need to get individual state count based on individual groups.

Can anyone please help me out on this?

[asifnoor]

Hi,

In your email script, instead of looping through the states, first loop through the groups. So for this, do gliderecord of your table and get all unique groups and put them in array. Loop through that.

Inside that loop through the states, then you will get the count as you want.

Kindly mark the comment as a correct answer and helpful if it helps to solve your problem.

Regards,
Asif
2020 ServiceNow Community MVP

[Ashutosh Munot1]

HI,

One thing: from where is this storeVal coming from?

Thanks,
Ashutosh

[Tanaji Patil]

Could you try below script? I used GlideAggregate because you just need count.

(function runMailScript(/* GlideRecord */ current, /* TemplatePrinter */ template,
          /* Optional EmailOutbound */ email, /* Optional GlideRecord */ email_action,
          /* Optional GlideRecord */ event) {

    baseUrl = gs.getProperty("glide.servlet.uri");
    email.setSubject("User- User ticket details : " + gs.now());

    var count = new GlideAggregate('incident');
    count.addQuery('active', 'true');
    count.addQuery('assigned_to', '46d44a23a9fe19810012d100cca80666');
    count.addAggregate('COUNT');
    count.groupBy('assignment_group');
    count.groupBy('state');
    count.query();
    if (count.hasNext()) {
        template.print("<table border=2>");
        template.print("<tr><td colspan=5 align =center>List Of Tickets</td></tr>");
        template.print("<tr><th>Assignment group</th><th>State</th><th>Count</th></tr>");

        while (count.next()) {
            var assignmentGroup = count.assignment_group.getDisplayValue();
            var stateValue = count.state.getDisplayValue();
            var groupCount = count.getAggregate('COUNT');
            template.print("<tr><td>" + assignmentGroup + "</td><td>" + stateValue + "</td><td>" + groupCount + "</td></tr>");
        }
        template.print("</table>");
    }

})(current, template, email, email_action, event);

-Tanaji

Please mark response correct/helpful if applicable.

[Hari1]

Hi Tanaji,

Thanks a lot. It's working. We have tickets with INC and GREQ if the ticket is INC the field "Generic request" is "false". if the ticket is GREQ the field "Generic request" is "true". I need the count of this as well along with the state and assignment group.

REF::

Can you please help me out on this as well?

So far the output is

I need it to be as