Background script to insert records into related list table(custom) for bunch of users

Service Manager · ‎12-03-2019

Hi,

I need help to create a background script to insert records into custom table (u_workplace). User(sys_user) is parent table and workplace(u_workplace) is the child table in related list. There is a workplace named as 'California' which needs to be inserted for bunch of users. FYI - California is existing in an other table location(u_location), few users already have workplace record in related list and we have to insert the workplace only for those users who doesn't have workplace.

Thanks

jeffgreener · ‎12-03-2019

you're missing a lot of needed details, but this should get you in the right direction:

/*assuptions:
1) name= column on u_location table that would contain "california"
2) user = column on u_workplace table that is a reference to user table
3) location = columno on u_workplace that references u_location table
4) all other fileds on the workplace table have default values
*/

var grwrkplace;
var grloc = new GlideRecord('u_location');
grloc.addQuery('name','California'); //note 1
grloc.query();
if (grloc.next())
{
var gruser = new GlideRecord('sys_user');
gruser.query();
while (gruser.next())
{
var checkworkplace = new GlideRecord('u_workplace');
checkworkplace.addQuery('user',gruser.sys_id); //note 2
checkworkplace.query();
if (!checkworkplace.next()){
grwrkplace=new GlideRecord('u_workplace');
grwrkplace.initialize();
grwrkplace.user=gruser.sys_id; //note 2
grwrkplace.location=grloc.sys_id; //note 3
grwrkplace.insert();
}
}
}

View solution in original post

Service Manager · ‎12-03-2019

Thanks for quick response!

each users works at multiple location(California, New Jersey, Dallas and others). So records already exits in the table(u_workplace) with users sys_id.

we are suppose to add the additional records 'California' if the user doesn't have it.

Thanks

jeffgreener · ‎12-03-2019

okay tweak this section as such then:

however the reason I suspect that it added it in for all users is that I don't have the correct field names for user, and nor location on the u_workplace table. You'll need to update the script to match your instance. Or send a screenshot of the u_workplace table. As it stands I think the field is wrong so it's not finding any matches on this check when it otherwise would.

var checkworkplace = new GlideRecord('u_workplace');

checkworkplace.addQuery('location',grloc.sys_id);
checkworkplace.addQuery('user',gruser.sys_id); //note 2
checkworkplace.query();

if (!checkworkplace.next()){

.

}