HELP! How to dynamically set recipients of an existing Scheduled Report to email addresses that are in the report's output?

scottbjorke · ‎01-01-2017

I have a lot of Scheduled Reports that run a given Report whose output includes email addresses. Presently, my Scheduled Reports get emailed only to whoever I have pre-defined as the STATIC list of email addresses, per the Scheduled Reports UI (in this case, 'bob@mycomany.com'). From there, those recipients have to open the attached report, spot the email address on each row, and FW the report attachment directly to those email addresses from the report output. This seems unnecessary -- I'd rather "cut out the middle man" and have SN set the recipients equal to the email addresses I find within the report.

I assume this means:

- I can simply 'utilize' the same Report that is already defined in my Scheduled Report

- set Conditional = TRUE then insert my scripting logic into the Scheduled Report definition

- walk through some kind of GlideRecord that essentially IS my predefined Report

- get each record's email address (which is one of its report columns) -- but be aware I don't know exactly how to get its exact name because I'm dot-walking and don't have admin rights to utlize the SN log

- appending it to something like "current.address_list" (with a trailing comma to separate each email address)

- that current.address_list can hold a long comma-delimited list of emails as a string value and is not an array[] instead

- retuning TRUE to indeed trigger the report.

Of course, any or all the above assumptions may be really lousy And of course, there's probably a much easier, simpler way to do what I want to do.

Please note I do not have admin privileges. I'm just a Change Manager trying to reduce operational waste and learn more about leveraging SN's capabilities myself.

Here's header and the script of my latest attempt. Would really appreciate it if someone could take a look and reply with ideas/comments that are indeed helpful. That also includes giving me code examples that essentially replace mine (ie, starts over).

Please note I have confirmed the below approach does successfully send my report output to the STATIC recipient from the script AND to the email address predefined in the UI below. I just can't figure out how to glean additional email recipients from the addresses found in the report output.

---

// REMINDER: THE BELOW SCRIPT EXECUTES ONLY IF "CONDITIONAL" IS CHECKED ABOVE

// Include the Script Include
gs.include('ReportEmailList');

// Set the STATIC recipients first
var email_recipients = 'john.doe@mycompany.com';
var next_email = ' ';

//Execute the scheduled report within a local GlideRecord
var rec = new GlideRecord('sysauto_report');
rec.get('name', 'Scott some rows to return various Release Owners'); // this is same as what's in the header
rec.query();

// cycle through each report row (if any), get the DYNAMIC recipient value, and append it to the recipients string with a leading comma
if (rec.getRowCount() > 0) {
while (rec.next()) {
next_email = rec.GetValue('u_owner.email');
email_recipients += ',' + next_email;
}
}

current.address_list = email_recipients; // FYI - I have verified this line works to affect email recipients manually -- just need help setting 'email_recipients' variable using report output
// Need to set this true for condition script to run
answer = true;

---

PS Here's how my Report is defined:

palmen · ‎01-03-2017

I modified your solution a bit and came up with the following to fit the requirement.

I'm not sure why you set a static recipient in the code, this could be written in the Email addresses field directly.

Instead of adding the users to the Email addresses field I added them to the Users field instead (see below script)

var scheduleReport = new GlideRecord('sysauto_report');

scheduleReport.get(current.sys_id); //Sys ID of your schedule Report


var tablename = scheduleReport.report.table;

var query = scheduleReport.report.filter;

var users = '';


//Execute the report as a GlideRecord with the set table and condition

var gr = new GlideRecord(tablename);

gr.addEncodedQuery(query);

gr.query();

while (gr.next()) {

   users += ',' + gr.opened_by;

}


current.user_list = users;

current.update();


// Need to set this true for condition script to run

answer = true;

This script works fine for me when testing in my developer instance.

View solution in original post

Chandra51 · ‎07-31-2020

yes, the report has many number of different owned_by records Sean.

sbutton · ‎08-07-2020

Hi Chandra, i was away taking some leave.

Have you had any success with this one?

I just tested it from scratch in a personal developer instance and it works.