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

[sachin_namjoshi]

use below code

var grUser = new GlideRecord("sys_user");
grUser.addEncodedQuery("companyISNOTEMPTY");
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();
}
}
}

[MrMuhammad]

There are couple of issues that needs fix. 

Replace below

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

with

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

Below line is missing 

grCompany.query();

UPDATED CODE HERE

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

while (grUser.next()) {

   var grCompany = new GlideRecord("core_company");
   grCompany.addQuery('name',grCompany.name);
   grCompany.addQuery('notes','DOES NOT CONTAIN','active');
   grCompany.setLimit(1);
   grCompany.query();

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

}

Thanks & Regards,

Sharjeel

[kylem1]

Still not updating the company record. I added a gs.log line below the while statement when is correctly logging the company referenced for the user. However, there is no update to the notes field on the company record. Adding a log to the last if statement does not produce output so I do not believe I am making it past the grCompany query. 

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

while (grUser.next()) {
   gs.log(grUser.company.name);
   var grCompany = new GlideRecord("core_company");
   grCompany.addQuery('name',grUser.company.name);
   grCompany.addQuery('notes','DOES NOT CONTAIN','active');
   grCompany.setLimit(1);
   grCompany.query();

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

}

[Dhananjay Pawar]

Hi,

Try below updated working code,

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

while (grUser.next()) {
gs.log(grUser.company.name);
var grCompany = new GlideRecord("core_company");
grCompany.addQuery('name',grUser.company.name);
grCompany.addEncodedQuery('notesNOT LIKEactive');
grCompany.setLimit(1);
grCompany.query();

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

Mark correct and helpful based on impact.

Thanks,

Dhananjay.