Fix script to populate fields based on field values

[Pablo H]

Hello folks,

I need to create a fix script based on the values from sys_audit table. The logic is that it will go trough the records for the "Offer" table and check the values of the fields 'result_company' and 'result_contractor', check from the options 'accepted' and 'rejected'.

If for both fields are accepted, it will set the value of the field 'offer_accepted' to the date when the change was done.

If one of the fields 'result_company' and 'result_contractor' is rejected, it will set the value of the field 'offer_rejected" to the date when the change was done.

This will be done on every record for the offer table.

Thank you for any help. Thanks!

[Prithvi_b · Accepted solution]

Hi Pablo,

Please try this. Verify before running the script.

var gr = new GlideRecord('sys_audit');
gr.addQuery('tablename', 'incident'); / /give the table
gr.addEncodedQuery('fieldname=u_result_company^ORfieldname=u_result_contractor^newvalue=accepted^ORnewvalue=rejected');  //field name is u_result_company or u_result_contractor and newvalue is accepted or rejected
gr.orderBy('sys_created_on');
gr.query();
while (gr.next()) {
var date = gr.sys_created_on.getDisplayValue(); //date/time of record created in sys_audit table
var id = gr.documentkey;
//gs.log("test "+date +" "+id);
var kr = new GlideRecord('incident');
kr.addQuery('sys_id', id);
kr.query();
if (kr.next()) {
gs.log(kr.u_result_contractor);
if ((kr.u_result_contractor == 'accepted') && (kr.u_result_company == 'accepted')) {
var num = kr.number;
gs.log("test " + date + " " + num); // logs are for testing
kr.setValue('u_offer_accepted', date); //date/time of record created in sys_audit table
kr.update();
}

else {
if ((kr.u_result_contractor == 'rejected') || (kr.u_result_company == 'rejected')) {
gs.log("test " + date + " " + num);
kr.setValue('u_offer_rejected', date); //date/time of record created in sys_audit table
kr.update();
}
}
}
}

Thankyou

[Akshayb65]

perfect solution 

[Pablo H]

Thanks a lot @Prithvi_b !