Autopopulate field using Scheduled Job

Jeni Sebastian · ‎06-06-2022

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 · ‎06-07-2022

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

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

Jeni Sebastian · ‎06-07-2022

Hi @suvro

I tried the script above but it's not changing the HR Partner EID field in the custom table and I also change the "customg.setValue('hrpeopleadvisor', userg.getValue('u_hrrepnm');" to

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

var userg = new GlideRecord('sys_user');

userg.addActiveQuery();
userg.query();

while (userg.next()){

var customg = new GlideRecord('customtable');
var hrpartnereid = customg.u_hr_partner_eid;

customg.addEncodedQuery('u_apprentice_statusINactive_loa,active');//Apprentice Status is Active/Active LOA
customg.addQuery('u_apprentice_eid', userg.user_name);
customg.query();

if (customg.next()){
customg.setValue('hrpartnereid', userg.getValue('u_hrrepnm'));
customg.update();

}
}

suvro · ‎06-07-2022

Is u_apprentice_eid a reference field. Then replace

customg.addQuery('u_apprentice_eid', userg.user_name); WITH

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

suvro · ‎06-07-2022

Also is u_hrrepnm is a string field ?? then there is mismatch. SO in the below code I have mentioned which line of code to be included if it is a string field and which line should be included if it is reference field. Do it accordingly it should work

var userg = new GlideRecord('sys_user');

userg.addActiveQuery();

userg.query();

while (userg.next()){

var customg = new GlideRecord(customTable);

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

customg.query();

if (customg.next()){

var useg = new GlideRecord('sys_user'); // if hrrepname is a string field

useg.get('name', userg.getValue('u_hrrepnm')); // if hrrepname is a string field

customg.setValue('hrpeopleadvisor', useg.sys_id); // if hrrepname is a string field

customg.setValue('hrpeopleadvisor',userg.u_hrrepnm); //if hrrepnm is a reference field

customg.update();

}