Reference a field in a different table in a script

[jrmckins]

I’m trying to create a scheduled report that sends open P3 incidents to the owners of the Business Service (BS table) for each P3.

I have:

1. A report
2. A scheduled report (on demand)
3. A script that generates the list of email addresses and sends the report

Here are the report conditions. It uses the “incident” table:

Here are the relevant fields:

Here’s the scheduled report (on demand):

Here’s the script. This works when I use the “assigned_to” field which is in the “incident” table:

var scheduleReport = new GlideRecord('sysauto_report');

scheduleReport.get('4955a6081b94d1501cddbfc61a4bcb56'); //Sys ID of the scheduled Report

var recipients = [];

var tablename = scheduleReport.report.table;

var query = scheduleReport.report.filter;

var gr = new GlideRecord(tablename);

gr.addEncodedQuery(query);

gr.query();

while (gr.next()) {

    recipients.push(gr.assigned_to.sys_id.toString());

}

var arrayUtil = new ArrayUtil();

var finalRecipients = arrayUtil.unique(recipients);

scheduleReport.user_list = finalRecipients.join(','); //get unique recipients

scheduleReport.update();

SncTriggerSynchronizer.executeNow(scheduleReport); //execute scheduled report

As I mentioned, this works for “assigned_to” which is the “Assigned to” field in the “incident” table. I’d like to use the “Business owner” field (u_business_owner) in the “Business service” (cmdb_ci_service) table. When I change “assigned_to” to “u_business_owner” in this line, “recipients.push(gr.assigned_to.sys_id.toString());”, I get no results.

I’m, obviously, doing something wrong. How do I change this script to use the “u_business_owner” field to generate the list of recipients?

[Shakeel Shaik]

Hi @jrmckins ,

Once try this code:

while (gr.next()) {

    recipients.push(gr.getValue(assigned_to));

}

If my response is helpful, then Please mark it as Correct Answer/Helpful.

Please check and let us know.

Thanks 🙂

Shakeel Shaik.

[jrmckins]

The code I posted with "assigned_to" works. I'm not sure what your answer is solving.

[Harish KM]

I tested your code with dumy report in PDI works fine for me. Is there a user present on incident table on business service field? Run this in background script and check also make sure Business owner field is not empty

var scheduleReport = new GlideRecord('sysauto_report');

scheduleReport.get('ba5557701bd4911009ad9758b04bcbff'); //Sys ID of the dummy Report

var recipients = [];

var tablename = scheduleReport.report.table;

var query = scheduleReport.report.filter;

var gr = new GlideRecord(tablename);

gr.addEncodedQuery(query);

gr.query();

while (gr.next()) {

    recipients.push(gr.u_business_owner.sys_id.toString());

}
gs.info(gr.u_business_owner.sys_id.toString());

[jrmckins]

"Is there a user present on incident table on business service field?"

I have no idea what you're asking here.