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?

[jrmckins]

"u_business_owner" is not on the incident table. That's the point of my question.

[Sagar Pagar · Accepted solution]

Hi,

You have to dot walk the business owner field as

recipients.push(gr.business_service.owned_by.toString());

make sure field name is correct

Thanks,
Sagar Pagar

[jrmckins]

I pinged a developer friend who gave me this advice too. I was dot-walking using the table name, not the field name. When I switched to the field name, it worked. I knew it was something simple that I overlooked.

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

Thanks!!

[jrmckins]

To extend this conversation, how do I add a BCC recipient to this? Let's assume the email address of the person is "first.last@company.com"