Script To Populate Reference Field

[Nic Omaha]

I have a table which has a string field called "u_created_by" which currently has "joe.smith" which is the users ID. I have since created a reference field for reporting purposes that is populating the "requestor" going forward. I am having trouble writing a script to do a one time update that would take the users ID from the string field, query the user table and populate the new reference field "requestor".

Any help is appreciated! 

[Sukraj Raikhraj]

Here is an example , might need some adjustment..

var grUser = new GlideRecord("sys_user");
grUser.addQuery('u_created_by','=','joe.smith');
grUser.query();

while (grUser.next()) {

grUser.requestor=grUser.u_created_by;
grUser.update();

}

[Rajesh M1]

Hi Nic,

Every table will have created and created by fields by default. Not sure about your exact requirement but to update the existing code you can try below code in lower instances.

Try below script:

var tableNam = new GlideRecord('TableName'); //Mention the table name

tableNam.addQuery('newReferenceFieldName','');//Mention the backend field name of the reference field.

table.setLimit(50); //Specify limit of record which query should return.

tableNam.query();

while(tableNam.next())

{

var user = new GlideRecord('sys_user');

user.addQuery('use_name',tableNam.u_created_by);

user.query();

if(user.next())

{

tableNam.newReferenceFieldName = user.sys_id; //Mention the new reference field name

tableNam.setWorkflow(false); // Aborts execution of business rules

tableNam.update();

}

}

Mark my response as correct/helpful if it helps.

Regards,

Rajesh M.

[Ankur Bawiskar]

@Nic Omaha 

Create fix script or write background script for this

updateRecords();

function updateRecords(){

	var rec = new GlideRecord('tableName');
	rec.addQuery('u_created_byISNOTEMPTY');
	rec.query();
	while(rec.next()){
		var userRec = new GlideRecord('sys_user');
		if(userRec.get('user_name', rec.u_created_by)){
			rec.u_requestor = userRec.sys_id;
			rec.update();
		}
	}
}

Regards
Ankur

[Nic Omaha]

Thank you very much for the response. I checked all the fields and this script looks correct but when I run it as a background i get the following error. It is looking to update about 20,000 records. Thoughts?