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

[Service Manager]

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

[Tony Chatfield1]

You may need to clarify your table/field structure. You state that you want to insert records, but only mention 1 field by content not by name or type, California exists as a location, are you populating this into a u_workplace reference field or as a string? What is the relationship structure, is California set as the users 'location? Which would mean your u_workplace field needs to contain details of the users location? Do you need to insert a relationship for all users where one is not populated or only for users where California is their location. An XML of an existing u_ workplace record may make some of this clearer.

[jeffgreener · Accepted solution]

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();
}
}
}

[Service Manager]

Awesome!

It was very Helpful but it inserted the records for all users. we have to insert the records(workplace) only for those uses who does not have workplace in their related list.

Thanks

[jeffgreener]

You'll note I have the "if exist" check piece in there, but you'll need to make sure it's checking the correct data points:

var checkworkplace = new GlideRecord('u_workplace');

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

if (!checkworkplace.next()){

.

.

.

}

this will cause it to only run the inside loop to add the record if there is no record in that table with sys_id it's about to add. it does assume I'm pulling and comparing the correct columns though, I had to guess based on your question.