Get previous approval comments in approval notification of RITM in email notification

Ankita Gupte · ‎11-18-2024

Hi Experts,

I am trying to sent previous approval comment to the next approver in RITM approval notification and has written below email notification script and calling it in approval notification using ${email_script:email_script_name}

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

    var gr = new GlideRecord('sysapproval_approver');
    gr.addQuery('document_id', current.sys_id);
    gr.addQuery("state", "approved"); // Filter out only approved record if you want all comment this line
    gr.query();

    // If no approved records found
    if (!gr.hasNext()) {
        template.print('No approved records found.');
    } else {
        while (gr.next()) {
            template.print('<b>Approver Details:</b><br>');
            template.print('<b>Approver:</b> ' + gr.getDisplayValue("approver") + '<br>');
            template.print('<b>Approval State:</b> ' + gr.getValue("state") + '<br>');
            template.print('<b>Approved Time:</b> ' + gr.getDisplayValue("sys_updated_on") + '<br>');
            template.print('<b>Comments:</b> ' + gr.comments.getJournalEntry(1) + '<br><br>');
        }
    }
})(current, template, email, email_action, event);

But it gives comment as "No Approved Records found" even if comments are present under previous approver. Please advice how can I achieve the requirement.

Ankita Gupte · ‎11-19-2024

Hi Runjay,

I modified the code as below and now its working fine as required. I tested it from fulfiller view only not sure how it will work for users approving it over email. I need to check that. Also I tried getting all approval comments instead of only latest comment.

Also instead of using current.sys_id. I used current.document_id

// Use the document_id of the current record to fetch previous approvals
var gr = new GlideRecord('sysapproval_approver');

// Query to find previous approval records attached to the same document_id
gr.addQuery('document_id', current.document_id);  // Use the document_id of the current record
gr.addQuery("state", "approved"); 
gr.orderByDesc('sys_updated_on'); 

gs.info('Current document_id being queried: ' + current.document_id);

gr.query();

var previousApprovers = [];
while (gr.next()) {
    previousApprovers.push(gr); 
}

if (previousApprovers.length > 0) {
    gs.info('Found ' + previousApprovers.length + ' previous approvals for document_id ' + current.document_id);
    
    // Go through each previous approver
    previousApprovers.forEach(function(approvalRecord) {
        template.print('<b>Approver Details:</b><br>');
        template.print('<b>Approver:</b> ' + approvalRecord.getDisplayValue("approver") + '<br>');
        template.print('<b>Approval State:</b> ' + approvalRecord.getValue("state") + '<br>');
        template.print('<b>Approved Time:</b> ' + approvalRecord.getDisplayValue("sys_updated_on") + '<br>');
        
        // Get all journal entries related to the approval record's comments
        var commentGr = new GlideRecord('sys_journal_field');
        commentGr.addQuery('element_id', approvalRecord.sys_id);
        commentGr.addQuery('element', 'comments'); 
        commentGr.orderBy('sys_created_on');
        commentGr.query();

        //Go through all comments for this approval record
        if (commentGr.hasNext()) {
            template.print('<b>Comments:</b><br>');
            while (commentGr.next()) {
                template.print(commentGr.value + '<br>');
            }
        } else {
            template.print('<b>No comments available.</b><br>');
        }

        template.print('<br>');
    });
} else {
    gs.info('No approved records found for document_id: ' + current.document_id);
    template.print('No previous approved records found.');
}

Thank you for all the help. Appreciate it!

View solution in original post

Runjay Patel · ‎11-18-2024

Hi @Ankita Gupte ,

Use below script in your mail script. Also use ${mail_script:email_script_name} instead of ${email_script:email_script_name}

var gr = new GlideRecord('sysapproval_approver');
    gr.addQuery('document_id', current.sys_id);
    gr.addQuery("state", "approved"); // Filter out only approved record if you want all comment this line
    gr.query();

    // If no approved records found
    if (gr.next()) {
        template.print('<b>Approver Details:</b><br>');
        template.print('<b>Approver:</b> ' + gr.getDisplayValue("approver") + '<br>');
        template.print('<b>Approval State:</b> ' + gr.getValue("state") + '<br>');
        template.print('<b>Approved Time:</b> ' + gr.getDisplayValue("sys_updated_on") + '<br>');
        template.print('<b>Comments:</b> ' + gr.comments.getJournalEntry(1) + '<br><br>');
    }else
	 template.print('No approved records found.');

-------------------------------------------------------------------------

If you found my response helpful, please consider selecting "Accept as Solution" and marking it as "Helpful." This not only supports me but also benefits the community.

Regards
Runjay Patel - ServiceNow Solution Architect
YouTube: https://www.youtube.com/@RunjayP
LinkedIn: https://www.linkedin.com/in/runjay

-------------------------------------------------------------------------

Ankita Gupte · ‎11-18-2024

Hi Runjay,

Thank you for updates, but the comments are not showing up. Instead it is showing "No Approved Records Found."

Abhay Kumar1 · ‎11-18-2024

@Ankita Gupte The issue is occurring because your script is querying the sysapproval_approver table with the document_id set to the current.sys_id. In the context of the RITM approval notification, current.sys_id typically refers to the RITM record (sc_req_item), not the approval record (sysapproval_approver).

Ankita Gupte · ‎11-18-2024

Hi Abhay,

Requesting to please help me with correct script. Should I use sysapproval.sys_id?