Fix Script to update the value of Assigned by for historical records is not working

[SAS21]

Requirement is need to capture the user , assigning an INC request to an analyst for the historical records from the Audit table. 

Hence created a reference field u_assignedby_req to capture the user who assigns an INC to an analyst. 

Need to update the above field for the historical records.

Here is code tried but not working.. Appreciate the suggestions

var gr = new GlideRecord('u_incident');
gr.addEncodedQuery("assigned_toISNOTEMPTY");
gr.query();
while(gr.next()) {
    var audit = new GlideRecord('sys_audit');
    audit.addEncodedQuery("tablenameSTARTSWITHu_incident^fieldname=assigned_to");
    audit.addQuery('documentkey', gr.sys_id);
    audit.orderByDesc('sys_created_on');
    audit.query();
if (audit.next()) {
        var user = new GlideRecord('sys_user');
        user.addQuery('user_name',audit.user);
        user.query();

      gr.u_assignedby_req = user.name;  // u_assignedby_req  is a reference field created to capture the user assigning 
        gr.setWorkflow(false); 
        gr.autoSysFields(false); 
        gr.update();
}

 }

[Anand Kumar P]

Hi @SAS21 ,

Try below script you are missing if statement in script and also u_assignedby_req  is a reference field so you have to set sys_id not name.

var gr = new GlideRecord('u_incident');
gr.addEncodedQuery("assigned_toISNOTEMPTY");
gr.query();
while (gr.next()) {
    var audit = new GlideRecord('sys_audit');
    audit.addEncodedQuery("tablename=u_incident^fieldname=assigned_to");
    audit.addQuery('documentkey', gr.sys_id);
    audit.orderByDesc('sys_created_on');
    audit.query();
    if (audit.next()) {
        var user = new GlideRecord('sys_user');
        user.addQuery('user_name', audit.user);
        user.query();
        if (user.next()) {
            gr.u_assignedby_req = user.sys_id;
            gr.update();
        }
    }
}

 Please mark it as solution proposed and helpful if its serves your purpose.

Thanks,

Anand 

[SAS21]

Hi Anand,

Thank you for the reply. Still no luck. Tried printing user and document key from audit table. I see the value printing as 'undefined'. 

[Anand Kumar P]

Hi @SAS21 ,

Script looks correct make sure table name u_incident custom table name is correct oob incident table name is just incident put logs as below
gs.log('u_incident record with sys_id: ' + gr.sys_id);
gs.log(' sys_audit record with user: ' + audit.user);

Thanks,

Anand

[Anand Kumar P · Accepted solution]

Hi @SAS21 ,

For me its working fine in my instance

Thanks,

Anand