Autopopulate field using Scheduled Job

[Jeni Sebastian]

Hello everyone. Im a newbie in servicenow and I'm kinda lost in getting the value of the field in sys_user table .

My requirement is to update every month the "HR Partner EID" of the users in a custom table..  I need to reference it in the sys_user table field named "HR People Advisor Name".

Example: If the HR  people advisor name in the user table is "Maria Foj"  , it should also display the same name in the HR partner EID field that is in the custom table.

Custom table field:

User Table field:

What I did was , I created a scheduled job that will run every month but I really didnt get the value from HR people advisor name to be displayed in the HR partner eid field. 

All your responses will be a great help to me. Thank you

[Ankur Bawiskar · Accepted solution]

If it's a reference then it can hold user sysId and not u_hrrepnm

In that case the script I shared will work fine

try{
	var gr = new GlideRecord("custom table");
	gr.addQuery("u_apprentice_statusINactive_loa,active");
	gr.addEncodedQuery("u_apprentice_eidISNOTEMPTY");
	gr.query();
	while (gr.next()) {
		var user = new GlideRecord('sys_user');
		if(user.get('user_name', gr.u_apprentice_eid)){
			gr.u_hr_partner_eid = user.getUniqueValue();
			gr.update();
		}
	}
}
catch(ex){
	gs.info(ex);
}

Regards
Ankur

[Ankur Bawiskar]

@Jeni Sebastian 

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards
Ankur

[suvro]

Hi Jeni,

First you have to figure out, which fields in User table and the custom table is used to match the records,then you will have to make a nested GlideRecord which should go something like this

var userg = new GlideRecord('sys_user');

userg.addActiveQuery();

userg.query();

while (userg.next()){

var customg = new GlideRecord(customTable);

customg.addQuery('field_matching_with_user_table', userg.sys_id);

customg.query();

if (customg.next()){

customg.setValue('hrpeopleadvisor', userg.getValue('u_hrrepnm');

customg.update();

}

}

[Jeni Sebastian]

Hi @suvro Thanks for this

The only thing that the custom table and the user table have in common is the "Apprentice EID" field

Form:

The Apprentice EID field is the "user_name" field in the sys_user table. 

What I did was, I used "Addencodedquery" to filter the custom table by "Apprentice Status: Active" coz what I need is to only update "HR Partner EID" field  in records that has "Apprentice Status: Active" .

So I need to update the HR Partner EID field in the custom table every month using sched job, and I need to get that value in the sys_user table (HR People Advisor Name field).

[suvro]

Great just modify the code I shared earlier ....add your addEncodedQuery in the customg and modify the my addQuery() as below

customg.addQuery('apprentice_id_field_name', userg.user_name);

It will work fine.

Please mark my answer as helpful and correct if it helped in resolving your query, so that it benefits other users having similar queries