How to insert a Value in the Email Field using condition field of the Schedule Report?

[Elton2]

Hi everyone , how are you?!  

Firstly, I would like to thank for attention!!!   

I searched here on the Community and saw a suggestion that I found very interesting:

So I changed it according to my needs, because when the Fields: 'u_customer_depart' and 'department'
are Empty, an email with the Report will be sent to Holder's email of the group, which is because I used this code
as reference: https://www.servicenow.com/community/platform-analytics-forum/scheduled-report-to-email-contacts-tha... 

answer = getEmailHolder();

function getEmailHolder() {

    var emailArray = [];

    var gr = new GlideRecord('cmdb_business_company');

    gr.addEncodedQuery('u_customer_departISEMPTY^ORdepartmentISEMPTY');

    gr.query();

    while (gr.next()) {

        emailArray.push(gr.u_business_partner_department.u_holder + '');

    }

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

    current.setWorkflow(false);

    current.update();

    current.setWorkflow(true);

    if (emailArray.length > 0) {

        return true;

    }

    return false;

}

Note that I performed a Dot Walk to access the Holder's email (as it belongs to a Reference field), done this is inserted into an Array, but it only needs to be 1 email, as each Holder has 1 email, this needs to be populated in the "Email Field" (image attached), but it's still not working, I was wondering if you had any tips?

Thank you very much!

[Tai Vu · Accepted solution]

Hi @Elton2 

Let's have a look in the description of the Condition field.

Configuring Script sandbox property

If you enable the script sandbox property (glide.script.use.sandbox), the script being evaluated via either of these two entry points runs within a reduced rights sandbox with the following characteristics:

• Only those business rules marked client callable are available within the sandbox.
• Only script includes marked client callable are available within the sandbox.
• Certain API calls (largely but not entirely limited to those dealing with direct DB access) are not allowed.
• Data cannot be inserted, updated, or deleted from within the sandbox. Any calls to current.update(), for example, are ignored.

You can try the alternative below.

1. Create a Scheduled Script Execution.

2. In the Script box, do the query to collect email recipients

3. Update the Schedule Email and execute it.

Sample.

var emailArray = [];
var gr = new GlideRecord('cmdb_business_company');
gr.addEncodedQuery('u_customer_departISEMPTY^ORdepartmentISEMPTY');
gr.query();
while (gr.next()) {
    emailArray.push(gr.u_business_partner_department.u_holder + '');
}

var grSchedule = new GlideRecord('sysauto_report');
grSchedule.get('a0e19a5347ea7d90ab9bb6bf016d4394'); //replace your schedule email sys_id
grSchedule.user_list = emailArray.join(',');
grSchedule.setWorkflow(false);
grSchedule.update();
SncTriggerSynchronizer.executeNow(grSchedule);

Enjoy!

Cheers,

Tai Vu

[AshishKM]

Hi @Elton2 ,

As we understand from code, 

current.user_list = emailArray.join(','); this code line is setting all emails in users List on the scheduled report form.

Did you check the email log, if email has been trigger for all users. 

-Thanks,

AshishKMishra

[AshishKM]

@Elton2 , tried to replicate on PDI but no luck, condition script not setting the emails and sys_email not showing the log. 

[Tai Vu · Accepted solution]

Hi @Elton2 

Let's have a look in the description of the Condition field.

Configuring Script sandbox property

If you enable the script sandbox property (glide.script.use.sandbox), the script being evaluated via either of these two entry points runs within a reduced rights sandbox with the following characteristics:

• Only those business rules marked client callable are available within the sandbox.
• Only script includes marked client callable are available within the sandbox.
• Certain API calls (largely but not entirely limited to those dealing with direct DB access) are not allowed.
• Data cannot be inserted, updated, or deleted from within the sandbox. Any calls to current.update(), for example, are ignored.

You can try the alternative below.

1. Create a Scheduled Script Execution.

2. In the Script box, do the query to collect email recipients

3. Update the Schedule Email and execute it.

Sample.

var emailArray = [];
var gr = new GlideRecord('cmdb_business_company');
gr.addEncodedQuery('u_customer_departISEMPTY^ORdepartmentISEMPTY');
gr.query();
while (gr.next()) {
    emailArray.push(gr.u_business_partner_department.u_holder + '');
}

var grSchedule = new GlideRecord('sysauto_report');
grSchedule.get('a0e19a5347ea7d90ab9bb6bf016d4394'); //replace your schedule email sys_id
grSchedule.user_list = emailArray.join(',');
grSchedule.setWorkflow(false);
grSchedule.update();
SncTriggerSynchronizer.executeNow(grSchedule);

Enjoy!

Cheers,

Tai Vu

[AshishKM · Accepted solution]

@Tai Vu , perfect.