Fix script to copy data from sys_journal to a new field on INC

[Russell Abbott]

I created a new field on my INC form. This field uses a BR to update with the current date when a new Customer Comment is added. To be reported on later (Show me Incidents without a Customer Comment in x number of days)

This is great going forward.

Would I be able to run a fix script to populate this field (u_last_customer_update) with the date of the most recent customer comment?

I have the below, but what comes after the = I'm not sure

var incGR=new GlideRecord('incident');
incGR.addEncodedQuery('active=true^u_last_customer_updateISEMPTY');
incGR.query();
while(incGR.next()){
	incGR.u_last_customer_update=incGR.comments.created;
	incGR.update();
}

[Subrahmanyam2]

Hi Russell,

sys_journal_field table stores the journal field entries.
You need to write a fix script which traverses through all the incident records and then query each incidents sys_journal_field entries in descending order. Pick the first entries updated timestamp and put it in the new field.

"Element" field in "sys_journal_field" stores the journal field name and 

"Element ID" field in "sys_journal_field" stores the sys_id of the incident record.

Hope this helps!

Thanks and regards,

Subrahmanyam Satti

[Russell Abbott]

It helps in direction and concept for sure, but in the writing of the script is where i am stuck

[Subrahmanyam2 · Accepted solution]

var incGR=new GlideRecord('incident');
incGR.addEncodedQuery('active=true^u_last_customer_updateISEMPTY');
incGR.query();
while(incGR.next()){
    var journalEntries = new GlideRecord("sys_journal_field");
    journalEntries.addQuery("name", "incident");
    journalEntries.addQuery("element_id", incGR.getValue("sys_id"));
    journalEntries.addQuery("element", "comments");
    journalEntries.orderByDesc("sys_created_on");
    journalEntries.setLimit(1);
    journalEntries.query();
    if(journalEntries.next()){
        incGR.setValue("u_last_customer_update", journalEntries.getValue("sys_created_on"));
        incGR.setWorkflow(false); // Not executing BR's
        incGR.autoSysFields(false); // Not updating system fields such as updated by, updated
	    incGR.update();
    }	
}

Please check this if it helps.

[Russell Abbott]

That worked like a charm. I appreciate the extra mile to help, that was more complicated than I initially thought. I'm new to scripting and I'm currently taking some code academy courses to help, one day I may be able to return the favour to someone else.