Retrieve comments for current approval in mail script

[John Phifer]

Hi guys,

I'm still learning ServiceNow, so be gentle. I'm working on a workflow notification for Change Request that sends to the Requested By upon rejection and want to include the comments from the users that rejected it in the email notification.

I have a script I found in another old discussion thread that I've modified a bit to fit my needs, but my problem is it retrieves comments from any rejected approvals for the change request and not just from the current approval set. In other words, if it was rejected once and resubmitted, it's pulling in the comments from the previous rejection.

(function runMailScript(current, template, email, email_action, event) {
   var app = new GlideRecord('sysapproval_approver'); 
   var regex = /20.*\(Comments\)\n/g;     
   app.addQuery('sysapproval', current.sys_id);       
   app.addQuery('state', 'rejected');       
   app.query();     
   while (app.next()) {           
   template.print("Approver: " + app.approver.getDisplayValue() + ":\n");       
   template.print("Comments: " + app.comments.getJournalEntry(-1).replace(regex,"") + "\n\n");       
   }     
})(current, template, email, email_action, event);

I think I've figured out I need to change the addQuery part, but I haven't been able to figure out from searching online what the proper query would be.

Thanks in advance for help!

[sachin312]

Hi John,

Did you try getJournalEntry(1) instead of getJournalEntry(-1)?

(or)

template.print("Comments: " + current.comments.getJournalEntry(1).replace(regex,"") + "\n\n");

[Abhinay Erra]

getJournalEntry(-1) gets all the comments where as getJournalEntry(1) gets only the latest entry of the comments

[John Phifer]

Yes, I'm aware how that function works but that's not the problem I'm trying to solve. If I have two subsequent approvals because it got rejected more than once, I'm getting the comments from both approvals, even though they're under separate IDs in the approvals table (see screenshot).

I only want to retrieve comments from the current approval.

[John Phifer · Accepted solution]

After some digging around through the GlideRecord query options I settled on adding a secondary query to filter the results down to just those from the most recent approval group:

(function runMailScript(current, template, email, email_action, event) {
   var app = new GlideRecord('sysapproval_approver'); 
   var regex = /20.*\(Comments\)\n/g; //strip out 'commments' line from each entry     
   // find all rejected approval records for the current Change ID, sorted by most recently created
   app.addQuery('sysapproval', current.sys_id); 
   app.addQuery('state', 'rejected');
   app.orderByDesc('sys_created_on');
   app.query(); 
   if (app.next()) {
   // find all records within the current set that are from the most recent approval group
   var gr = new GlideRecord('sysapproval_approver');
   gr.addQuery('group', app.group);
   gr.addQuery('state', 'rejected');
   gr.query();
   //for each result, return the approver's name and comments
   while (gr.next()) {
   template.print('<table><tr><td style="font-family: Arial, Helvetica, sans-serif;"><b>Approver:</b> ' + gr.approver.getDisplayValue() + '</td></tr>');       
   template.print('<tr><td style="font-family: Arial, Helvetica, sans-serif;"><b>Comments:</b> ' + gr.comments.getJournalEntry(1).replace(regex,"") + '</td></tr></table>');
   }
   }
})(current, template, email, email_action, event);

This is what the resulting email will look like:

Hope this is helpful to someone else!