Help please! How to dynamically set recipients of an existing Scheduled Report to users that are in the report's output.

sbutton · ‎04-04-2020

Hi,

I'm working on trying to configure a Conditional Scheduled email of a report.

I've been following resources - Article,

I'm not quite sure why this isn't working as logically it makes sense to me that it should work.

I'm querying the Asset table (alm_asset) and trying to add the Managed by users to the user.list on the schedule report.

the only method to verify success is by checking the email logs and nothing is appearing. in my test report there's 5 records and 3 confirmed Managed by users in the list.

i've been looking at it a while and my eyes are starting to hurt. Can anyone highlight something i am doing wrong?

Appreciate any advice

function lookupUsersAndUpdateReport(){


   // look up "managed_by" people -- assumes "table" is alm_asset


   var senderArray = [];



   var taskQueryGR = new GlideRecord(current.report.table);


   taskQueryGR.addEncodedQuery(current.report.filter);


   taskQueryGR.query();


   while (taskQueryGR.next()){


           senderArray.push(taskQueryGR.managed_by+ '');


   }



   current.user_list = senderArray.join(','); 


   current.setWorkflow(false);


   current.update();


   current.setWorkflow(true);



   // only return true if there were records listed in the result set


   if (senderArray.length > 0){


           return true;


   }


   return false;


}

lookupUsersAndUpdateReport();



//my report query - managed_byISNOTEMPTY^substatus=Vendor Borrowed^assigned_to.active=false

sbutton · ‎04-06-2020

I have found the solution to this now.

Oversight on my part.

current.update does not work on a server side script which is what a scheduled email of a report is when using conditional option.

1. Create the report

2. Create the schedule of the report on demand.

3. Add the required subject and intro message. and file output (pdf, xlsx, etc)

4. Create a separate Scheduled job (script execution) and apply the script in the script field

var scheduleReport = new GlideRecord('sysauto_report');
scheduleReport.get('d742dd441bc4d410b43d0f69cc4bcb69'); //Sys ID of your schedule 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.managed_by.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 schedule report

This overrides the previous set of user on the user_list and executes the report.

Thanks,

Sean

View solution in original post

Ashutosh Munot1 · ‎04-04-2020

Hi,

Where are you using this script?

Below code is missing in your 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 = '';

Thanks,
Ashutosh Munot

sbutton · ‎04-04-2020

Thanks Ashutosh,

Using it in the conditional Field of Scheduled Email of report.

I'll review and edit as suggested and update on result.

sbutton · ‎04-04-2020

I added a variation of this into my script but i had no success.

a more direct approach of the attached article but.

var scheduleReport = new GlideRecord('sysauto_report');


scheduleReport.get('d742dd441bc4d410b43d0f69cc4bcb69'); //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.managed_by;


}



current.user_list = users;


current.update();



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


answer = true;

Jaspal Singh · ‎04-04-2020

Hi,

I guess,

current.user_list = senderArray.join(',');

should be in your while loop & not outside.

Thanks,

Jaspal Singh

Hit Helpful or Correct on the impact of response.