How to populate the "email" field via script in the Schedule Report?

[Elton2]

Hi everyone, how are you?!

I need the Report to be sent to the "Group Holder" email, when two specific fields are "Empty" but as it is a Report I cannot use Business Rules. Because of this, I researched here in the Community (I would also like to thank some colleagues for their tips) and saw that I could create a Script Include, perform a Dot Walk to access the email, since it is a reference field (PRINT_1):

//Script Include

getEmailHolder: function() {

        var gr = new GlideRecord('cmdb_ci_business_company');

        gr.addQuery('u_business_partner_department.u_holder.email', '!=', '');

        gr.query();

        while (gr.next) {

            if (gr.u_business_partner_department.u_holder.email){

            var emailHolder = gr.u_business_partner_department.u_holder.email;

            return emailHolder

        }

    }

After that, I called the Script Include in the Condition field of "Schedule an email containing this report" (PRINT_2):

var email = g_form.getValue('email');

var ga = new GlideAjax('populateFieldEmail');

ga.addParam('sysparm_name', getEmailHolder);

ga.addParam('sysparm_value', email);

ga.getXML('getFieldValueEmailHolder');

function getFieldValueEmailHolder(response) {

    var answer = response.responseXML.documentElement.getAttribute('answer');

               return answer;

}

g_form.setValue('address_list', answer.emailHolder);

I need the Holder's email to be populated in the "Email addresses" field (PRINT_3), so I used
g_form.setValue(), but it's not working yet.

Could anyone give me a suggestion?
Thanks!

[piyushsain · Accepted solution]

Hi,

It is not required to use client callable Script include and no need to use GlideAjax in Condition use the below code in condition and remove Client Callable check in the Script Include

new global.populatedFieldEmail().getEmailHolder();

[Elton2]

Hi @piyushsain , how are you?!

Firstly, I would like to thank you for your attention and advice!!! I even used your tip, but it didn't work...

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 the "Holder" of the group, which is why 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!