Copy email history from original HR case on the transferred HR case

ArpitaVK · 3 weeks ago

The requirement is that - full email history to transfer with an HR case when it is reassigned or recategorized (transferred), so that all communications related to the case remain visible, accurate, and centralized in one record. I am trying to consolidate the email logs from the original case and print them in work notes using a business rule.

Table: HR Case [sn_hr_core_case]

When to run: After insert, Transferred from is not empty AND Transferred from changes.

(function executeRule(current, previous /*null when async*/ ) {

var oldCaseId = current.getValue('transferred_from');

gs.info('AK07 oldCaseSysID = ' + oldCaseSysID);

var consolidatedLog = "--- CONSOLIDATED EMAIL HISTORY FROM " + current.transferred_from.getDisplayValue() + " ---\n\n";

var emailGr = new GlideRecord('sys_email');

//emailGr.addQuery('instance', oldCaseId);

emailGr.addQuery('instance', current.sys_id);

// emailGr.addQuery('target_table', 'sn_hr_core_case');

emailGr.addEncodedQuery('target_tableSTARTSWITHsn_hr_core_case');

emailGr.orderBy('sys_created_on');

emailGr.query();

gs.info('AK07 Queried sys_email table');

if (!emailGr.hasNext()) {

return;

}

while (emailGr.next()) {

consolidatedLog += "Date: " + emailGr.sys_created_on + "\n";

if (emailGr.user_id) {

consolidatedLog += "From: " + emailGr.user_user + " (" + emailGr.user_id.getDisplayValue() + ")\n";

} else {

consolidatedLog += "From: " + emailGr.user_user + " (" + emailGr.user + ")\n";

}

consolidatedLog += "Subject: " + emailGr.subject + "\n";

var bodyText = emailGr.body.replace(/<[^>]*>?/gm, '');

consolidatedLog += "Body:\n" + bodyText + "\n";

consolidatedLog += "--------------------------------------------------\n\n";

}

gs.info('AK07 Consolidated email log = ' + consolidatedLog);

current.work_notes = consolidatedLog;

current.update();

gs.info('AK07 HR case ' + current.number + ' updated with email logs');

})(current, previous);

This business rule is not working as expected. Could you please help me with that?

I am open to any suggestions on how it could be done in any other/better way as well.

Thanks much in advance!

AhsanM · 3 weeks ago

Hi Arpita,

Great progress! The BR is firing and finding emails correctly now. The two remaining issues are straightforward to fix.

Issue 1: From showing null (null)

The user_name field on sys_email is not always populated, especially for system generated emails. Replace the From logic with this:

var fromName = emailGr.getValue('user_name') || emailGr.getValue('user') || 'System';
var fromEmail = '';

if (!emailGr.user_id.nil()) {
    fromEmail = emailGr.user_id.getDisplayValue();
} else {
    fromEmail = emailGr.getValue('user') || 'unknown';
}

consolidatedLog += "From: " + fromName + " (" + fromEmail + ")\n";

This adds proper null guards and falls back to "System" when no user is found, which is common for automated notification emails.

Issue 2: Body containing HTML, CSS and special characters

You are reading body_text but the email was likely stored as HTML in the body field. The body_text field is sometimes empty for template based emails. Try this improved body extraction:

var rawBody = emailGr.getValue('body_text') || emailGr.getValue('body') || '';

// Remove style and script blocks entirely first
rawBody = rawBody.replace(/<style[^>]*>[\s\S]*?<\/style>/gi, '');
rawBody = rawBody.replace(/<script[^>]*>[\s\S]*?<\/script>/gi, '');

// Remove all remaining HTML tags
rawBody = rawBody.replace(/<[^>]*>/gm, '');

// Clean up HTML entities
rawBody = rawBody.replace(/&nbsp;/g, ' ');
rawBody = rawBody.replace(/&amp;/g, '&');
rawBody = rawBody.replace(/&lt;/g, '<');
rawBody = rawBody.replace(/&gt;/g, '>');
rawBody = rawBody.replace(/&quot;/g, '"');

// Remove excessive whitespace and blank lines
rawBody = rawBody.replace(/\n\s*\n\s*\n/g, '\n\n');
rawBody = rawBody.trim();

consolidatedLog += "Body:\n" + rawBody + "\n";

This handles template based emails much more cleanly by stripping style blocks before removing tags, then converting common HTML entities to readable characters.

Give these two fixes a try and let me know what the output looks like after. The body should now be human readable plain text.

Ahsan
ServiceNow Developer & Admin
Builder of NowFixer | Free AI debugging tool for ServiceNow scripts

View solution in original post

vaishali231 · 3 weeks ago

hey @ArpitaVK

Condition:

current.transferred_from.changes() && !gs.nil(current.transferred_from)

Script

(function executeRule(current, previous /*null when async*/) {
   if (!current.transferred_from) {
       return;
   }
   var oldCaseId = current.getValue('transferred_from');
   gs.info('AK07 oldCaseSysID = ' + oldCaseId);
   var consolidatedLog = "--- CONSOLIDATED EMAIL HISTORY FROM " +
       current.transferred_from.getDisplayValue() + " ---\n\n";
   var emailGr = new GlideRecord('sys_email');
   emailGr.addQuery('instance', oldCaseId); // fetch emails from OLD case
   emailGr.addQuery('target_table', 'sn_hr_core_case');
   emailGr.orderBy('sys_created_on');
   emailGr.query();
   if (!emailGr.hasNext()) {
       gs.info('AK07 No emails found for old case');
       return;
   }
   while (emailGr.next()) {
       consolidatedLog += "Date: " + emailGr.getValue('sys_created_on') + "\n";
       var fromUser = emailGr.getValue('user_id') ?
           emailGr.user_id.getDisplayValue() :
           emailGr.getValue('user');
       consolidatedLog += "From: " + fromUser + "\n";
       consolidatedLog += "Subject: " + emailGr.getValue('subject') + "\n";
       var bodyText = emailGr.getValue('body_text') || '';
       bodyText = bodyText.replace(/<[^>]*>/g, '');
       consolidatedLog += "Body:\n" + bodyText + "\n";
       consolidatedLog += "--------------------------------------------------\n\n";
   }
   gs.info('AK07 Consolidated email log created');
   // Do NOT use current.update() in After BR
   current.work_notes = consolidatedLog;
})(current, previous);

*************************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.
Regards
Vaishali Singh

ArpitaVK · 3 weeks ago

Hello @vaishali231, Thanks for your reply, but this script did not work for me. Can you also specify when to run conditions?

Pavan Srivastav · 3 weeks ago

Hello Arpita,

Could you try -

javascript

(function executeRule(current, previous) {

    var oldCaseId = current.getValue('transferred_from');
    
    if (!oldCaseId) {
        gs.info('AK07 No transferred_from value found, exiting.');
        return;
    }

    gs.info('AK07 oldCaseId = ' + oldCaseId);

    var consolidatedLog = "--- CONSOLIDATED EMAIL HISTORY FROM " 
        + current.transferred_from.getDisplayValue() + " ---\n\n";

    var emailGr = new GlideRecord('sys_email');
    emailGr.addQuery('instance', oldCaseId);                          // ✅ old case
    emailGr.addEncodedQuery('target_tableSTARTSWITHsn_hr_core_case'); // ✅ HR tables only
    emailGr.orderBy('sys_created_on');
    emailGr.query();

    gs.info('AK07 Queried sys_email table');

    if (!emailGr.hasNext()) {
        gs.info('AK07 No emails found for old case: ' + oldCaseId);
        return;
    }

    while (emailGr.next()) {
        consolidatedLog += "Date: " + emailGr.getValue('sys_created_on') + "\n";

        if (emailGr.user_id) {
            consolidatedLog += "From: " + emailGr.getValue('user_name') 
                + " (" + emailGr.user_id.getDisplayValue() + ")\n";
        } else {
            consolidatedLog += "From: " + emailGr.getValue('user_name') 
                + " (" + emailGr.getValue('user') + ")\n";
        }

        consolidatedLog += "Subject: " + emailGr.getValue('subject') + "\n";

        var rawBody = emailGr.getValue('body') || '';               // ✅ null guard
        var bodyText = rawBody.replace(/<[^>]*>?/gm, '');
        consolidatedLog += "Body:\n" + bodyText + "\n";
        consolidatedLog += "--------------------------------------------------\n\n";
    }

    gs.info('AK07 Consolidated log built, length = ' + consolidatedLog.length);

    // ✅ Safe update without recursion
    var updateGr = new GlideRecord('sn_hr_core_case');
    if (updateGr.get(current.sys_id)) {
        updateGr.work_notes = consolidatedLog;
        updateGr.autoSysFields(false);
        updateGr.setWorkflow(false);
        updateGr.update();
        gs.info('AK07 HR case ' + current.number + ' updated with email logs');
    }

})(current, previous);

ArpitaVK · 3 weeks ago

Hello @Pavan Srivastav , Thanks for your reply, but this script did not work for me. Can you also specify when to run conditions?