David Whaley
Mega Sage

Hello Everyone,

 

Thanks for taking the time to view my article.  Searching the community you can see there is no shortage of posts on how to create Approval Reminders. There are different ways they can be send out using like a scheduled job, the workflow engine and the great new Flow Designer.  One I have used in the past  is @marcguy's answer in this thread.

 

The problem I always had was the number of emails generated to an approver with outstanding approvals.  Every approval required its own notification so when you replied 'Approve' or 'Reject' the proper approval would be updated.  So every morning an executive or director will log in and check their email and if they did not keep up on their approvals (or a delegate) they would have multiple email reminders.  This article will focus on a solution that builds one email that contains all of the users outstanding approvals and will allow them to respond 'Approve' or 'Reject' on each approval that is contained in one email message.   This is done by querying the sys_watermark table for the message in the notification email script.

 

If you currently have a scheduled job or flow you may just need to edit your email notification script.  If not navigate to System Definition ->Scheduled Jobs

DavidWhaley_0-1669061671066.png

Click the New button select Automatically run a script of your choosing

DavidWhaley_1-1669061909644.png
Give your new scheduled script job a name, select the frequency it will run and a time.  If you would like for your reminders to run only on weekdays enter in the following script in the Condition script editor field.

 

(function(){
	//run on weekdays only
	var day = new Date().getDay();
	return (day != 0 && day !=6);
})();

 

In the Run this script field we will need to query all unique users that have outstanding approvals.  We can do that by entering in the following script.

 

var arrUtil = new ArrayUtil();
var answer = [];
var app = new GlideRecord('sysapproval_approver');
	app.addQuery('state','requested');
	app.orderBy('approver');
	app.query();
while(app.next()){
	if (arrUtil.indexOf(answer, app.approver)==-1) {
		answer.push(app.approver.getValue());
	}
}
for (var i = 0; i < answer.length; i++) {
	gs.eventQueue('open.approval.reminders', app, answer[i]);
}

 

This queries all approvals with a State of Requested, gets the unique approver user and creates an event log entry named open.approval.reminders with the sys id of the approval and user.

Your scheduled job should look something like this

DavidWhaley_2-1669062551569.png

In the Event Registry create a record and name it open.approval.reminders

DavidWhaley_3-1669062727871.png

Once the scheduled job runs you should see multiple entries in the event log with the name you specified and in Parm1 you will see sys_id's from the sys_user table (the approver reference field in the sysapproval_approver record)

DavidWhaley_2-1669064210336.png

 

Creating the notification email script.

Filter on 'notification email' and select 'Notification Email Scripts' to create a new mail script

DavidWhaley_0-1669063397744.png

Give your email script a name.  I name mine the same as my event record in the registry 'open.approval.reminders' and in the script enter in the first two lines,

 

 

baseUrl = gs.getProperty("glide.servlet.uri") + 'YOURPORTALNAME?sysparm_content_url=';
mailto = 'mailto:' + gs.getProperty("glide.email.user");

 

Replace YOURPORTALNAME with the name of your instance's service portal (this could be sp).  We will use these 2 variables further in our script

Then we will need to get the Requested Items for the approvals using the part of the mail script.

 

//Get Requested Items needing approval
var app = new GlideRecord('sysapproval_approver');
	app.addQuery('state','requested');
	app.addQuery('approver', event.parm1);
	app.addQuery('sysapproval.sys_class_name','sc_req_item');
	app.query();

while(app.next()){
		var ritm = new GlideRecord('sc_req_item');
			ritm.get(app.sysapproval);

 

Now let's write this data to an html table.

 

//Request Header Short Description and RITM
	template.print('<table style="border-left: 4px solid #7bb342; width:100%; border-collapse:collapse;">');
	template.print('<tbody>');
	template.print('<tr style="background-color: #7bb342;">');
	template.print('<td style="text-align: left;"><span style="font-size: 10pt; font-family: Montserrat, sans-serif; color: #ffffff;"><strong>' + ritm.short_description + '</strong></span></td>');
	template.print('<td style="text-align: right;"><span style="font-size: 10pt; font-family: Montserrat, sans-serif;"><strong><a style="color: #ffffff;" href=' + baseUrl + ritm.getLink() + '>' + app.getDisplayValue('sysapproval') + '</a>' + '</strong></span></td>');
	template.print('</tr>');

//Request Description	
	template.print('<tr>');
	template.print('<td colspan="2"; style="text-align: left; margin: 3px; font-size: 10pt; font-family: Montserrat, sans-serif; ">' + ritm.description + '</td>');
	template.print('</tr>');
	template.print('</tbody>');
	template.print('</table>');

 

This was the best way I could get this into a table in the format I wanted.   If anyone has a better way to do this or even has a better-looking result, please comment below with a better solution.  This will create the following table in the body of your email.

DavidWhaley_5-1669064988459.png

The top row is the RITM short description and also includes a link to the RITM.  I chose green for Requested Items but feel free to use any colors or fonts you would like.  The script will get all approvals for the user so you can have more that 1 table in your email like this depending on how many requested approvals there are for the user.

DavidWhaley_6-1669065438161.png

Now, we need to build the links to accept or reject.  In order for the approval or rejection to go the proper approval we will need to query the sys_watermark table for particular approvals sys id in the source id field.  When the approval email is received ServiceNow will know which approval it applies to because we will be getting the Ref:MSG number from this record.

Example of a sys_watermark record

DavidWhaley_7-1669065906252.png

To get this we will add the following to our mail script

 

//Approval or Reject Links
	var msg = new GlideRecord('sys_watermark');
		msg.addEncodedQuery('source_id='+ app.sys_id);
		msg.query();
	var number = '';
	if(msg.next()) {
		number = msg.number;
	}

 

Now to build the Approve or Reject reply links for the ritm approval.  Again, as earlier, this was the way I was able to get it to work any thoughts on improvement are welcome.

 

var approve = mailto + '?subject=RE:%20' + app.getDisplayValue('sysapproval') + '%20-%20approve&body=%0D%0A%0D%0A%0D%0ARef%3A' + number;
	var reject = mailto + '?subject=RE:%20' + app.getDisplayValue('sysapproval') + '%20-%20reject&body=%0D%0A%0D%0ARef%3A' + number;
	
	template.print('<table style="border-left: 4px solid #7bb342; width:100%; border-collapse:collapse;">');
	template.print('<tbody>');
	template.print('<tr>');
	template.print('<td style="border-radius: 5px; background-clip: padding-box; background-color:#7bb342; text-align: center; margin: 3px; width: 10%;";><span style="font-size: 10pt; font-family: Montserrat, sans-serif;"><a style="color: #ffffff;" href=' + approve + '><strong>' + "Approve" + '</a></strong></span></td>');
	template.print('<td style="background-color:#ffffff; width: 80%;"></td>');
	template.print('<td style="border-radius: 5px; background-clip: padding-box; background-color:#df2027; text-align: center; margin: 3px; width: 10%;"><span style="font-size: 10pt; font-family: Montserrat, sans-serif;"><a style="color: #ffffff;" href=' + reject + '><strong>' + "Reject" + '</a></strong></span></td>');
	template.print('</tr>');
	
	template.print('</tbody>');
	template.print('</table>');
	template.print('<br>');
}

 

This is all you would need for requested items.  You can add Changes, Knowledge articles and any other approvals you may have and append to the script.

 

I have included my email script which includes Change Requests as well.  Change requests are blue and will also be included in the email but the format is a bit different because we are getting more fields from the change record.

DavidWhaley_0-1669082206333.png

You will also need to add the email script to a notification.  Create a new email notification and for 'Send when' choose Event is fired and then select your event name you created earlier.

DavidWhaley_1-1669068505725.png

 

On the who will receive tab check 'Event parm 1 contains recipient' and 'Send to event creator'

DavidWhaley_4-1669068853238.png

Finally, on the What it will contain make sure you have ${mail_script:open.approval.reminders} in the message HTML to add your script to the email notification

DavidWhaley_3-1669068760097.png

Once the links are clicked in their default email client the mail script we created earlier will populate the Subject and add the Ref:MSG# in the body of the email

DavidWhaley_0-1669068170686.png

All the user has to do now is click send to approve the RITM.

Let me know if you have any questions in the comments below.

Thank you,

David Whaley

Comments
M_iA
Kilo Sage

Thanks for posting this @David Whaley . This is just what I needed as some of my users had quite a few outstanding approvals. So collate into one email is a great step forward.

One thing I have noticed though is that once the notification is sent, it is attaching all of the notifications to just one record in the target field:

M_iA_0-1673544544252.png

If you go onto that target record, it has every notification that was sent out regardless whether its related or not.

Any ideas? Did you encounter this?

David Whaley
Mega Sage

Yes, that is an issue I have and currently I have not found a resolution.  I have tried to update it with query from the sys_watermark table but as of yet I have not been successful.  The behavior is less than optimal but doesn't affect the approvals.  We determined it was acceptable and our users preferred to have these in one email rather than our approvers getting multiple emails.  I have not had any issues with this behavior.  When I have a resolution I will be sure to post it.

Joakim St_ckel
Tera Expert

Hi @David Whaley,
I'm looking to implement this solution, but for Knowledge Articles. It seems like I should just have to edit the sc_req_item to kb_knowledge if I'm not mistaken, correct?

David Whaley
Mega Sage

Hi @Joakim St_ckel 

 

Correct, in the script where you get your approvals for the requested items if you edit it with kb_knowledge.  I did the same thing with Change Requests and just copied the section for requested items to reflect the change_request table.

 

jparman
Tera Guru

Hi @M_iA ,

 

Were you able to resolve the issue?

DianaBTX
Tera Expert

Hi David, thank you for this.  i am testing and even though i have Hello ${approver}, in the email notification, all the emails are getting addressed to Hello Zachhello approver.pnghello zach.png 

jacobspacek
Giga Guru

@David Whaley

 

Thank you for such a wonderful detailed post!

David Whaley
Mega Sage

Hi @DianaBTX 

 

Sorry about the late response.  You would need to add a mail script in place of the salutation instead of

 

Hello ${approver},

 

you would have something like ${mail_script:open.approval.reminders.salutation}

 

The email notification script,

 

Name of the email notification script:  open.approval.reminders.salutation

Script:

 

mailto = 'mailto:' + gs.getProperty("glide.email.user");

//Get Requested Items needing approval
var app = new GlideRecord('sysapproval_approver');
	app.addQuery('state','requested');
	app.addQuery('approver', event.parm1);
	app.addQuery('sysapproval.sys_class_name','sc_req_item');
	app.setLimit(1);
	app.query();

while(app.next()){
		var ritm = new GlideRecord('sc_req_item');
			ritm.get(app.sysapproval);

//Salutation
	template.print('<table style="width:100%; border-collapse:collapse;">');
	template.print('<tbody>');
	template.print('<tr>');
	template.print('<td colspan="2"; style="text-align: left; margin: 3px; font-size: 10pt; font-family: Montserrat, sans-serif; ">Hello: ' + app.approver.getDisplayValue() + '</td>');
	template.print('</tr>');
	template.print('</tbody>');
	template.print('</table>');

}

 

 

DianaBTX
Tera Expert

Thanks David,  my boss wants to go a different direction now  🙂

Ravi Chandra_K
Kilo Patron
Kilo Patron

This is super useful David on such an important use case!!!

Bookmarked!

 

Kind Regards,

Ravi Chandra 

 

Version history
Last update:
‎11-21-2022 06:39 PM
Updated by:
Contributors