Copy Journal Entries & Keep in Date Order

[Sue Frost]

I have an existing UI Action which copies a case (part of a custom app) to a new record. A number of fields from the original case are written to the new one.

There is a request to also copy over the work notes (work_notes) journal field. I've found / tweaked code to do that but the entries are not being copied in date order. The code I've found to sort an array is not doing anything.

If someone could point out what's wrong with this code or how it should look to sort the journal entires - newest at the top - I'd very much appreciate the help.

var notes = current.work_notes.getJournalEntry(-1); //gets all journal entries as a string where each entry is delimited by '\n\n'

notes.sort(function(a, b){

return a.sys_created_on-b.sys_created_on

})

var na = notes.split("\n\n");                                             //stores each entry into an array of strings

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

  gs.print(na[i]);

  gr.work_notes = na[i];

};

var msg = (gs.getMessage('copy.case'));

gr.work_notes = msg + current.number;

[Andrew Bettcher]

I had a little go with this:

var notes = current.work_notes.getJournalEntry(-1);
//gets all journal entries as a string where each entry is delimited by '\n\n' 
var na = notes.split("\n\n");  
for (var i = 0; i < na.length; i++) gs.print(na[i]);

 
It worked and so I added another section that did the same thing with comments (i.e. I want EVERYTHING) but it separated everything out seemingly randomly. So I have several separate entries that covers one email and they're not in order.

I guess that's why you did it as above......

[Andrew Bettcher]

And tried the nice and simple:

request.work_notes = current.work_notes.getJournalEntry(-1);
	
	request.upate();

That results in one nice and neat work_note on the target showing each work note entry in order. Doing similar for comments doesn't work though.

Can anyone help me get work_notes and additional comments please?

[Chuck Tomasi]

Hi Sue,

I'm playing around with the same concept (in Geneva), but coming up with different results from your test.

Upon a simple test of inserting records in to sys_journal_field, they do not appear in the activity history formatter. I found the only way to get them to show up is when there is a corresponding sys_history_line record. The way to get sys_history_line records is by using one of these two methods to create the journal entries:

gr.comments = 'some value';

or

gr['comments'].setJournalEntry('some value', 'some owner');

If either of these methods are used, the sys_journal_field is created as well as the sys_history_line. Modifying the sys_journal_field's sys_created_on value has no impact on the displayed date/time value in the activity history - that comes from sys_history_line record. Unfortunately, sys_history_line is a read-only table.

[kevan_smith]

I was in the same boat as Chuck.  While it's true the sys_history_line is read only, admins can modify the audit table which is what the system uses to build the sys_history_lines. Here's my solution, which addresses both the Journal entries and activity log. 

Note, Don't be surprised if the target ticket doesn't show the updated activity log right away.  It can take a month or more for the system to drop/rebuild the old history set/lines, these updates will be reflected once that happens (see Differences Between Audit and History Sets).  If anyone knows how to force the system to rebuild the activity log, please share!

I built this primarily to be used in a background/fix script; tweak the "validation testing" code at bottom to define source/target tickets.  You can also copy the function into a business rule.  

function copyJournalEntries(grSource /*glideRecord*/, grTarget /*glideRecord*/) {
	var grJnlSource = new GlideRecord('sys_journal_field');
	grJnlSource.addQuery('element_id', grSource.sys_id);
	grJnlSource.orderByDesc('sys_created_on');
	grJnlSource.query();
	while (grJnlSource.next()) {
		//gs.print('Evaluating ' + grSource.number + "." + grJnlSource.element + " (" + grJnlSource.sys_created_on + ")");
		var grJnlTarget = new GlideRecord('sys_journal_field');

		// Have we already synced this comment?
		// comment this section out to improve performance, at risk of creating duplicates
		grJnlTarget.addQuery('name', grTarget.sys_class_name);
		grJnlTarget.addQuery('element_id', grTarget.sys_id);
		grJnlTarget.addQuery('value', grJnlSource.value);
		grJnlTarget.addQuery('element', grJnlSource.element);
		grJnlTarget.addQuery('sys_created_on', grJnlSource.sys_created_on);
		grJnlTarget.addQuery('sys_created_by', grJnlSource.sys_created_by);
		grJnlTarget.setLimit(1);
		grJnlTarget.query();
		if (grJnlTarget.next()) {
			gs.print(grJnlTarget.element_id + " (" + grJnlSource.sys_created_on + ") found.");
			gs.print("Skipping " + grTarget.number + "." + grJnlSource.element + " (" + grJnlSource.sys_created_on + ").");
		} 
		else {
			gs.print("Copying " + grSource.number + "." + grJnlSource.element + " (" + grJnlSource.sys_created_on + ") to " + grTarget.number);
			// Journal entries used in email, portal, and "Comments and worknotes" formatter
			grJnlTarget.initialize();
			grJnlTarget.name = grJnlSource.name;
			grJnlTarget.element_id = grTarget.sys_id;
			grJnlTarget.value = grJnlSource.value;
			grJnlTarget.element = grJnlSource.element;
			grJnlTarget.sys_created_on = grJnlSource.sys_created_on;
			grJnlTarget.sys_created_by = grJnlSource.sys_created_by;
			grJnlTarget.autoSysFields(false);
			grJnlTarget.insert();
      gs.print(grJnlTarget.element_id + " (" + grJnlSource.sys_created_on + ") created.");
      
      
      // Audit entries are used in activity log generation (when the system rebuilds sys_history_set/sys_history_line)
			//See... https://docs.servicenow.com/bundle/paris-platform-administration/page/administer/security/concept/c_DiffBtwnAuditHistSets.html)
			var grAudit = new GlideRecord('sys_audit');
			grAudit.initialize();
			grAudit.documentkey = grTarget.sys_id;
			grAudit.fieldname = grJnlSource.element;
			grAudit.oldvalue = "JOURNAL FIELD ADDITION";
			grAudit.newvalue = grJnlSource.value;
			grAudit.user = grJnlSource.sys_created_by;
			grAudit.sys_created_by = grJnlSource.sys_created_by;
			grAudit.sys_created_on = grJnlSource.sys_created_on;
			grAudit.tablename = grTarget.sys_class_name;
			grAudit.autoSysFields(false);
			grAudit.insert();

		}
	}
}

// validation testing... 
var grSource = new GlideRecord('incident');
grSource.addQuery('parent.sys_class_name', 'sn_customerservice_case');
//grSource.addQuery('sys_created_on', '<', '2020-08-30');

grSource.orderByDesc('sys_created_on');
grSource.setLimit(50);
grSource.query();
while (grSource.next()) {
	var grTarget = grSource.parent.getRefRecord();
//  gs.print (grSource.number + " " + grTarget.number);
	copyJournalEntries(grSource, grTarget);
}

[kevan_smith]

I was in the same boat as Chuck.  While it's true the sys_history_line is read only, admins can modify the audit table which is what the system uses to build the sys_history_lines. Here's my solution, which addresses both the Journal entries and activity log. 

Note, Don't be surprised if the target ticket doesn't show the updated activity log right away.  It can take a month or more for the system to drop/rebuild the old history set/lines, these updates will be reflected once that happens (see Differences Between Audit and History Sets).  If anyone knows how to force the system to rebuild the activity log, please share!

I built this primarily to be used in a background/fix script; tweak the "validation testing" code at bottom to define source/target tickets.  You can also copy the function into a business rule.  

function copyJournalEntries(grSource /*glideRecord*/ , grTarget /*glideRecord*/ ) {
    var grJnlSource = new GlideRecord('sys_journal_field');
    grJnlSource.addQuery('element_id', grSource.sys_id);
    grJnlSource.orderByDesc('sys_created_on');
    grJnlSource.query();
    while (grJnlSource.next()) {
        var grJnlTarget = new GlideRecord('sys_journal_field');

        // Have we already synced this comment?
        // comment this section out to improve performance, at risk of creating duplicates
        grJnlTarget.addQuery('name', grTarget.sys_class_name);
        grJnlTarget.addQuery('element_id', grTarget.sys_id);
        grJnlTarget.addQuery('value', grJnlSource.value);
        grJnlTarget.addQuery('element', grJnlSource.element);
        grJnlTarget.addQuery('sys_created_on', grJnlSource.sys_created_on);
        grJnlTarget.addQuery('sys_created_by', grJnlSource.sys_created_by);
        grJnlTarget.setLimit(1);
        grJnlTarget.query();
        if (grJnlTarget.next()) {
            gs.print(grJnlTarget.element_id + " (" + grJnlSource.sys_created_on + ") found.");
            gs.print("Skipping " + grTarget.number + "." + grJnlSource.element + " (" + grJnlSource.sys_created_on + ").");
        } else {
            gs.print("Copying " + grSource.number + "." + grJnlSource.element + " (" + grJnlSource.sys_created_on + ") to " + grTarget.number);
            // Journal entries used in email, portal, and "Comments and worknotes" formatter
            grJnlTarget.initialize();
            grJnlTarget.name = grTarget.sys_class_name;
            grJnlTarget.element_id = grTarget.sys_id;
            grJnlTarget.value = grJnlSource.value;
            grJnlTarget.element = grJnlSource.element;
            grJnlTarget.sys_created_on = grJnlSource.sys_created_on;
            grJnlTarget.sys_created_by = grJnlSource.sys_created_by;
            grJnlTarget.autoSysFields(false);
            grJnlTarget.insert();
            gs.print(grJnlTarget.element_id + " (" + grJnlSource.sys_created_on + ") created.");


            // Audit entries are used in activity log generation (when the system rebuilds sys_history_set/sys_history_line)
            //See... https://docs.servicenow.com/bundle/paris-platform-administration/page/administer/security/concept/c_DiffBtwnAuditHistSets.html)
            var grAudit = new GlideRecord('sys_audit');
            grAudit.initialize();
            grAudit.documentkey = grTarget.sys_id;
            grAudit.fieldname = grJnlSource.element;
            grAudit.oldvalue = "JOURNAL FIELD ADDITION";
            grAudit.newvalue = grJnlSource.value;
            grAudit.user = grJnlSource.sys_created_by;
            grAudit.sys_created_by = grJnlSource.sys_created_by;
            grAudit.sys_created_on = grJnlSource.sys_created_on;
            grAudit.tablename = grTarget.sys_class_name;
            grAudit.autoSysFields(false);
            grAudit.insert();

        }
    }
}

// validation testing... 
var grSource = new GlideRecord('incident');
grSource.addQuery('parent.sys_class_name', 'sn_customerservice_case');
//grSource.addQuery('sys_created_on', '<', '2020-08-30');

grSource.orderByDesc('sys_created_on');
grSource.setLimit(50);
grSource.query();
while (grSource.next()) {
	var grTarget = grSource.parent.getRefRecord();
//  gs.print (grSource.number + " " + grTarget.number);
	copyJournalEntries(grSource, grTarget);
}