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:
gr.opened_by return the sys_id of the user who opened the record.
When setting users into the user-field on the scheduled report it's the sys_id of the user you should set and not the email-adress.
If you want to set the email adress you could dot-walk and set it like this
current.address_list = users;
In your case you just need to change gr.opened_by with gr.u_owner (I assume u_owner is a reference field to User table, if not it won't work).
When scripting towards a specific field, it's the field name you script against, not the field label. Field names are never in capital letter and that could be an issue if you run scripts if you use a capital letter in the beginning.
As an admin it's easy to get the field name, you just right-click the field label and you'll see show - 'u_owner' and this will be the field name. It might work as a regular user but you'll see watch - 'u_owner' instead.
I'm not sure if there is some other way to get the info about the field name, before I became admin I had never looked into this at all. Maybe you could get temporary admin access in a test instance at your company to be able to get the desired info, otherwise an admin might have to look it up.
In most cases if it's an out of the box field the name will be the same as the label (but not always and the "space" is replaced with _). If it's a custom field the name will most likely be the field label but with u_ in front of it.
