Updating date field in previously resolved tickets

[MegC]

Hello all, 

I am attempting to update a date field when a ticket was changed to resolved state. I was trying to use a glide walker, but I don't see that plugin in our list of installed (unless I'm not looking in the right place). I have checked to make sure that audit is selected on the form in question. I have updated the form so that when the state is marked as resolved it will update the resolved date field correctly, but now I'm trying to updated previously resolved tickets. 

The fix script I tried: 

var gr = new GlideRecord('u_content_requests');

gr.addQuery('state', '5');
gr.addNullQuery('u_resolved_date');
gr.query();

var count = 0;

while (gr.next()) {

	var hw = new sn_hw.GlideHistoryWalker(gr.getTableName(), gr.getUniqueValue());
	
	if (hw.walkToFieldValue('state', 'resolved')) {

		var historySnapshot = hw.getUpdateSnapshot();
		var actualDate = historySnapshot.sys_updated_on;
		
		gr.u_resolved_date = actualDate;

	gr.setWorkflow(false);
	gr.autoSysFields(false);
	gr.setLimit(10);
	gr.update();
	
	gs.info("Success: Updated  " + gr.number + "with date: " + actualDate);
	count++;
	}
	
	else {
		gs.warn('no history found for content request: ' + gr.number);
	}
	gs.info('fix script for content requests completed. total updated: ' + count);
}

Any insight or assistance would be appreciated! 

[vaishali231]

hey @MegC 

try this :

var gr = new GlideRecord('u_content_requests');
gr.addQuery('state', '5'); // resolved state value
gr.addNullQuery('u_resolved_date');
gr.query();

var count = 0;

while (gr.next()) {

var audit = new GlideRecord('sys_audit');
audit.addQuery('documentkey', gr.sys_id);
audit.addQuery('fieldname', 'state');
audit.addQuery('newvalue', '5'); // resolved value
audit.orderBy('sys_created_on'); // first time it became resolved
audit.setLimit(1);
audit.query();

if (audit.next()) {
    gr.u_resolved_date = audit.sys_created_on;
    gr.setWorkflow(false);
    gr.autoSysFields(false);
    gr.update();
    gs.info('Updated ' + gr.number + ' with resolved date: ' + audit.sys_created_on);
    count++;
} else {
    gs.warn('No state change audit found for: ' + gr.number);
}
}
gs.info('Fix script completed. Total updated: ' + count);

*************************************************************************************************************
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

[vaishali231]

hey @MegC 

Hope you are doing well.

Did my previous reply answer your question?

If it was helpful, please mark it as correct ✓ and close the thread 🔒. This will help other readers find the solution more easily.

Regards,
Vaishali Singh

[Ankur Bawiskar]

@MegC 

if table is audited then you can do this

You can do GlideRecord from following tables,

sys_history_set and sys_history_line

For e.g. I am fetching history for incident number 'INC0010016' and getting when State changed to some value from empty

var incident = new GlideRecord('incident');
incident.addQuery('number','INC0010016');
incident.query();

while(incident.next()){

var history = new GlideRecord('sys_history_set');
history.addQuery('id',incident.getValue('sys_id'));
history.query();
history.next();

var auditH = new GlideRecord('sys_history_line');
auditH.addQuery('set',history.getValue('sys_id'));
auditH.addEncodedQuery("label=State^newISNOTEMPTY^oldISEMPTY"); // new is not epmty and old is empty
auditH.query();
if(auditH.next()){

incident.u_field = new GlideDateTime(auditH.sys_created_on).getDate();
incident.update();

}

}

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@MegC 

Hope you are doing good.

Did my reply answer your question?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏