Background script - query table and update record on second table

[kylem1]

Looking for help with the following background script. Query sys_user table where company record is not empty. Then, query core_company table and update the associated user company record notes field with "active". 

var grUser = new GlideRecord("sys_user");
grUser.addEncodedQuery("companyISNOTEMPTY");
grUser.query();
while (grUser.next()) {
if ((!grUser.company.nil())) {
var grCompany = new GlideRecord("core_company");
grCompany.addQuery('name',grCompany.name);
grCompany.addQuery('notes','DOES NOT CONTAIN','active');
if (grCompany.next()){
grCompany.notes = "active";
grCompany.update();
}
}
}

[Dhananjay Pawar]

Find above screenshot of result.

Thanks,

Dhananjay.

[kylem1 · Accepted solution]

This is what I ended up using. Thank you all for your help!

var grUser = new GlideRecord("sys_user");
grUser.addEncodedQuery("companyISNOTEMPTY");
grUser.query();

while (grUser.next()) {
   var grCompany = new GlideRecord("core_company");
   grCompany.addQuery('name',grUser.company.name);
   grCompany.addEncodedQuery("notes!=active^ORnotes=NULL");
   //grCompany.setLimit(1);
   grCompany.query();

   if (grCompany.next()){
      gs.log(grCompany.name);
      grCompany.notes = "active";
      grCompany.update();
   }

}

[Dhananjay Pawar]

Hi,

Check my previous comments. I have provided the solution.

[MrMuhammad]

Rest of the code is good but yeah I missed replacing below line

grCompany.addQuery('name',grCompany.name);

with

grCompany.addQuery('name',grUser.company.name);

Anyhow, I am glad you got it working 🙂

Thanks & Regards,

Sharjeel

[Hitoshi Ozawa]

If you're have nil check because you're getting all the records, it means companyISNOTEMPTY is not set properly. If you just want query for records with company being null, just use he addNullQuery.

var grUser = new GlideRecord("sys_user");
grUser.addNullQuery("company");
grUser.query();
while (grUser.next()) {
	var grCompany = new GlideRecord("core_company");
	grCompany.addQuery('name',grUser.name);
	grCompany.addQuery('notes','DOES NOT CONTAIN','active');
	if (grCompany.next()){
		grCompany.notes = "active";
		grCompany.update();
	}
}