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

[sbutton]

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]

Thanks Jaspal,

testing the below but unfortunately still no success.

function lookupUsersAndUpdateReport() {


    // look up "managed_by" people -- assumes "table" is 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.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();

[Jaspal Singh]

Hi,

Can you try & include current.update(); as well in your while loop.

Thanks,

Jaspal Singh

[sbutton]

unfortunately no emails appear to be firing still.

[sbutton · Accepted solution]

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

[scottmbjorke]

In any ServiceNow instance where emailing is turned off (outbound emails are disabled from ServiceNow), look in the "System  Mailboxes", "Outbound", "Outbox". By default, this displays a list that includes the following filter parameter: Mailbox=Outbox.  Remove just that portion (by clicking the > immediately to its left). You'll likely find your outbound items have a Mailbox value of Skipped or something else that's not Outbox.

Hope this helps.