Pending approval Reminder

[vijay_kumar]

Need to send approval reminder to approvers every day .A single notification should have all the approval requests which are in requested along with the description,change type and additional info so the approver can prioritise the request.

Thanks in advance

vijay

[Jotiram Yadav]

You can write a scheduled script which will run on everyday. Create a notification on the sysapproval_approver table.

Now in script get the requests which are pending approval for same user(may be you can write a group by query). Now, trigger the event from this scheduled script. you can use param1 param2 to pass recipient and also requests which are pending in one of the variable.

These parameters you can use in the email template and configure mail template accordingly.

Thanks

[marcguy · Accepted solution]

This is quite a lengthy answer but we have done this for a few different tables to give users weekly reminders of various different things like projects, approvals, etc.

What you will need to do to get all of them in one email is fire an event for any user whom has an outstanding approval, in a scheduled script job (sysauto_script).

var users = []; //start with an empty list of approval users

var apps = new GlideRecord('sysapproval_approver');

apps.addQuery('state','requested'); //find requested approvals, you may want to specify which table i.e line below

apps.addQuery('sysapproval.sys_class_name', 'change_request'); //only find change related approvals

apps.query();

while (apps.next());

//now add the outstanding approver to the list

users.push(apps.approver);

}

//now we need to dedupe the list of users

var au = new ArrayUtil();

var noDupesApprovalUsers = au.unique(users);

//now we need to go through this unique list of users and fire an event against the users which in turn fire an email notification which will go and gather all of their outstanding approvals.

for (var i=0; i< noDupesApprovalUsers.length; i++) {

  gs.eventQueue('approval.reminder',noDupesApprovalUsers[i],'sys_user');

}

OK so now if you look in event log, you should see some events being fired called approval.reminder which is an event for every user that we have found who has an outstanding approval.

now we need to build a notification and template to find that users outstanding approvals and bunch them into ONE email.

So create a notification and it will be on sys_user table when approval.reminder event is fired:

Subject: Something like you have approvals outstanding....

Now I normally use an email template and script include to store the script and HTML in order to build a table of those approval records.

<mail_script>

var dem = new ApprovalReminderEmail();     //call the script include

template.print(dem.send(current.sys_id));       //call the send function and pass in the users sys_id

</mail_script>

</font>

Within this script include you are going to find the users outstanding approval records via GlideRecord and them add them to a HTML Table

send : function (user){

var outputString = ''; //put some html in here and your table header columns, styles etc.

var approvals = new GlideRecord('sysapproval_approver');

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

approvals.addQuery('sysapproval.sys_class_name','change_request');

approvals.addQuery('approver',user);

approvals.query();

while (approvals.next()({

// up to you how pretty you want to make your html but I normally build a var called outputString and keep adding to that with my new records i.e.

outputString = outputString +"<tr><a href=" + uri + "sysapproval_approver.do?sys_id="+approvals.sys_id+">"+approvals.sysapproval.getDisplayValue()+ "</a>"+" "+"</td><td width='150px'>" + approvals.sysapproval.short_description + " "+"</td><td width='150px'>" + approvals.sysapproval.start_date + " "+"</td><td>" + approvals.sysapproval.end_date + " "+"</td><td width='100px'>" + approvals.sysapproval.phase.getDisplayValue() + " "+"</td><td width='200px'>"+ approvals.sysapproval.assigned_to.getDisplayValue() + "</td></tr>";

}

//eventually you should end up with some html and a table

then just return all of that and I normally log it whilst building for troubleshooting bad html etc.

			gs.log(outputString,'approval reminders');
			return outputString;

[vijay_kumar]

hi mguy,

my script include is not getting called . can u plz help me with this with more clarity.

[marcguy]

sure, do you have this line in the email notification or email template?

var dem = new ApprovalReminderEmail();     //call the script include

template.print(dem.send(current.sys_id));       //call the send function and pass in the users sys_id

If you do then you need a script include called ApprovalReminderEmail and the first few lines of the script include should look like this:

//Call the check function, set the parameter to 1 to return a true/false, or 0 (empty) to return a string,

var WeeklyProjectManagerEmail = Class.create();

ApprovalReminderEmail.prototype = {

  initialize : function() {

  },

  send : function(user) {

gs.log('running send reminder email for ' + user); // add this line to check the logs for the script include is running

var outputString = ''; //put some html in here and your table header columns, styles etc.

var approvals = new GlideRecord('sysapproval_approver');

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

approvals.addQuery('sysapproval.sys_class_name','change_request');

approvals.addQuery('approver',user);

approvals.query();

while (approvals.next()({

// up to you how pretty you want to make your html but I normally build a var called outputString and keep adding to that with my new records i.e.

outputString = outputString +"<tr><a href=" + uri + "sysapproval_approver.do?sys_id="+approvals.sys_id+">"+approvals.sysapproval.getDisplayValue()+ "</a>"+" "+"</td><td width='150px'>" + approvals.sysapproval.short_description + " "+"</td><td width='150px'>" + approvals.sysapproval.start_date + " "+"</td><td>" + approvals.sysapproval.end_date + " "+"</td><td width='100px'>" + approvals.sysapproval.phase.getDisplayValue() + " "+"</td><td width='200px'>"+ approvals.sysapproval.assigned_to.getDisplayValue() + "</td></tr>";

}

//eventually you should end up with some html and a table

then just return all of that and I normally log it whilst building for troubleshooting bad html etc.

			gs.log(outputString,'approval reminders');
			return outputString;