Using report values in an Email script.

Abhinav_Byr
Tera Contributor

Greetings everyone, 

 

I'm trying to automate sending an formatted email that includes data from a report. A lot of solutions I've seen are just to attach an report to that email, but this isn't whats required of me. Iv'e tried using the <mail_script> tag based off of an old email script I was shown that worked back in 2018, but other solutions that are more up to date also don't work. I'll list my process down below as well as drop a snippet of my code. 

 

My script is stored in the "Scheduled Reports" section, and includes an HTML file using the code insertion feature. I've tried two ways to get this to work, one way is using the <mail_script> tag inside the HTML itself, but that was rendering the JS as plaintext instead of running it. Secondly, I tried putting the script in the "conditional" box with "ECMAScript 2021" turned on/off, and then use template.print to fill the tables in the code. Does anyone know how to format the data from the script into my HTML tables?

 

This is my original HTML file using the <mail_script> tags. I will post the conditional script below that.

<html>
<body

<br/><span style="font-family: Calibri; font-size: 100%"><b>Hello all, </b><br/></span>
<span style="font-family: Calibri; font-size: 100%">Cleaned for privacy</span>
<br/>
<br/>
<span style="font-family: Calibri; font-size: 100%">Cleaned for privacy<br/></span>
<br/>
<span style="font-family: Calibri; font-size: 100%">Cleaned for privacy<br/></span>
<br/>
<br/>

<table style="font-family: Calibri; font-size: 80%; width: 100%; border: 0px; border-collapse: collapse; width: 100%;" cellpadding="2" cellspacing="2">
    <!--<mail_script>
email.setFrom("Daily Report System <donotreply@yourdomain.com>");
// Add your status query logic here
</mail_script>
-->
</table>
<a href="https://your-status-dashboard.com">System Status Dashboard</a>
<br/><br/>

<!--===================================Section 1===================================-->
<span style="font-family: Calibri; font-size: 150%; color: #008000;"><b>"Blank" Changes Planned Next 3 Business Days</b><br/><br/></span>
<table style="font-family: Calibri; font-size: 80%; width: 100%; border: 0px; border-collapse: collapse; width: 100%;" cellpadding="2" cellspacing="2">
<tr style="font-weight: bold; background-color:#72A1EF;">
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">ID</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Summary</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Change Request Date (Eastern Time)</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Implementer</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Requester</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">SA?</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Pod</td>
<td style="text-align: center; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;">Division</td>
</tr>

<!-- Mail script based on Alok's EOD query -->

<mail_script>
 var strQuery = "start_dateRELATIVEGE@hour@ago@1^start_dateRELATIVELE@dayofweek@ahead@7^stateNOT IN3,4^short_descriptionNOT LIKE(PreLive)^short_descriptionNOT LIKE(InBuild)^short_descriptionNOT LIKEPHYSICAL NODE SPLIT^short_descriptionNOT LIKEenable genesis^short_descriptionNOT LIKE2g enablement^short_descriptionNOT LIKEbulk enablement^short_descriptionNOT LIKESSDIP Enablement^short_descriptionNOT LIKEGENESIS Midsplit enablement^short_descriptionNOT LIKEtxnID: GENESIS_^ORshort_descriptionISEMPTY^short_descriptionNOT LIKEtxnID: 2-OFDM_^ORshort_descriptionISEMPTY^short_descriptionNOT LIKE2nd OFDM bulk upgrades^short_descriptionNOT LIKEJanus:^EQ^ORDERBYstart_date^ORDERBYcmdb_ci"; 
 var gr = new GlideRecord('change_request');
 gr.addEncodedQuery(strQuery);
 gr.query();
 var count = gr.getRowCount();
 if (count > 0) {
     while (gr.next()) {
        template.print("<tr>");
        template.print("<td style='text-align: center; font-weight: bold; width: 10%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.number + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.short_description + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.start_date.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_implementer.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_requester.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + (gr.u_sa ? 'Yes' : 'No') + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_pod.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_division.getDisplayValue() + "</td>");
        template.print("</tr>");
     }
 } else {
     template.print("No changes planned for the next X business days.");
 }
 
</mail_script>

</table>
<br/>
<span style='font-family: Calibri; font-size: 90%'>Click <a href="https://comcast.service-now.com/now/nav/ui/classic/params/target/sys_report_template.do%3Fjvar_report_id%3D44f8cc43db982c507520fabfaa9619dd%26jvar_selected_tab%3DallReports%26jvar_list_order_by%3Dtable%26jvar_list_sort_direction%3Dasc%26sysparm_reportquery%3D%26jvar_search_created_by%3D%26jvar_search_table%3D%26jvar_search_report_sys_id%3D%26jvar_report_home_query%3D%26sysparm_use_polaris%3Dtrue">here</a> for detailed information.</span>
<br/>

<hr/>

</body></html>

 

var strQuery = "start_dateRELATIVEGE@hour@ago@1^start_dateRELATIVELE@dayofweek@ahead@7^stateNOT IN3,4^short_descriptionNOT LIKE(PreLive)^short_descriptionNOT LIKE(InBuild)^short_descriptionNOT LIKEPHYSICAL NODE SPLIT^short_descriptionNOT LIKEenable genesis^short_descriptionNOT LIKE2g enablement^short_descriptionNOT LIKEbulk enablement^short_descriptionNOT LIKESSDIP Enablement^short_descriptionNOT LIKEGENESIS Midsplit enablement^short_descriptionNOT LIKEtxnID: GENESIS_^ORshort_descriptionISEMPTY^short_descriptionNOT LIKEtxnID: 2-OFDM_^ORshort_descriptionISEMPTY^short_descriptionNOT LIKE2nd OFDM bulk upgrades^short_descriptionNOT LIKEJanus:^EQ^ORDERBYstart_date^ORDERBYcmdb_ci"; 
 var gr = new GlideRecord('change_request');
 gr.addEncodedQuery(strQuery);
 gr.query();
 var count = gr.getRowCount();
 if (count > 0) {
     while (gr.next()) {
        template.print("<tr>");
        template.print("<td style='text-align: center; font-weight: bold; width: 10%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.number + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.short_description + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.start_date.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_implementer.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_requester.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + (gr.u_sa ? 'Yes' : 'No') + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_pod.getDisplayValue() + "</td>");
        template.print("<td style='width: 30%; border-bottom: 1px solid #000; border-left: 1px solid #000; border-right: 1px solid #000; border-top: 1px solid #000;'>" + gr.u_division.getDisplayValue() + "</td>");
        template.print("</tr>");
     }
 } else {
     template.print("No changes planned for the next X business days.");
 }
  true;
1 ACCEPTED SOLUTION

"Notifications" is found under "System Notifications". To access these system modules you need to have "Admin" role assigned to your profile, so that you can perform these system config activities. 

After you have the access follow the steps below.

Navigate to "Flow Designer" from "All" application navigator page. Open flow designer and follow the screenshots below. 

 

Step 1) Click on New and select Flow. A new window will show up to give a name to your flow. Give a meaningful name, description and submit to proceed.

VikMach_11-1752975792755.png

 

Step 2) For trigger select "Scheduled" and choose appropriate option. Fill the field details and move on.

 

VikMach_0-1752972993556.png


Step 3) Click on "Action" button then type "Send Notification". Select "Send Notifications" to proceed.

VikMach_12-1752976104429.png

 

Step 4) Click on search button and follow the screenshots below.

 

VikMach_1-1752973067570.png

 

Step 5) Create new if you have already not create any notifications. If you have then select from the list show.

VikMach_4-1752973655010.png

 

Step 6) Fill in the details as example below and submit. (Click on Advanced just in case you don't see the "Show Notification Scripts"). When to send should be ""

VikMach_5-1752973850201.png

 

Step 7) Click on "Show Notification Scripts" to create your report list with HTML tags.

VikMach_6-1752973913817.png

 

Step 😎 If not already created then click New.

 

VikMach_7-1752974611485.png

 

Step 9) Add the script of your requirement and copy the "Name" of this script. It will be used in your email body later. See next step

 

VikMach_10-1752975541486.png

 

 

Step 10) Type your Subject and Email body, then add your script with syntax ${mail_Script:weekly_report_request}.

VikMach_8-1752975386538.png

 

Step 10) Finally when ready, add your notification in your flow like example below. Save the flow and test before activating it for deployment. 

 

VikMach_9-1752975462887.png

 

Hope this helps!

Let me know if it worked.

 

Regards,
Vikas K

 

View solution in original post

5 REPLIES 5

VikMach
Mega Sage

@abhinav, the scripting console in the Scheduled report is to run Server Side code and is not for embedding HTML code.
Also, the HTML code insertion feature that you have used is not for running server side scripts.
Rather. create a notification with the code you have pasted above and schedule it using the flow designer.

I hope this helps!

 

Regards,
Vikas K

So it is possible to do what I'm trying to do? When I try and search for "notification" or "flow designer" I don't believe I'm seeing what you are talking about. Is it possible that I don't have permissions to access this section? I'm pretty new to SNOW so I apologize for any stupid questions.
Thanks for the timely response. 

Abhinav_Byr_0-1752864079804.png

 

"Notifications" is found under "System Notifications". To access these system modules you need to have "Admin" role assigned to your profile, so that you can perform these system config activities. 

After you have the access follow the steps below.

Navigate to "Flow Designer" from "All" application navigator page. Open flow designer and follow the screenshots below. 

 

Step 1) Click on New and select Flow. A new window will show up to give a name to your flow. Give a meaningful name, description and submit to proceed.

VikMach_11-1752975792755.png

 

Step 2) For trigger select "Scheduled" and choose appropriate option. Fill the field details and move on.

 

VikMach_0-1752972993556.png


Step 3) Click on "Action" button then type "Send Notification". Select "Send Notifications" to proceed.

VikMach_12-1752976104429.png

 

Step 4) Click on search button and follow the screenshots below.

 

VikMach_1-1752973067570.png

 

Step 5) Create new if you have already not create any notifications. If you have then select from the list show.

VikMach_4-1752973655010.png

 

Step 6) Fill in the details as example below and submit. (Click on Advanced just in case you don't see the "Show Notification Scripts"). When to send should be ""

VikMach_5-1752973850201.png

 

Step 7) Click on "Show Notification Scripts" to create your report list with HTML tags.

VikMach_6-1752973913817.png

 

Step 😎 If not already created then click New.

 

VikMach_7-1752974611485.png

 

Step 9) Add the script of your requirement and copy the "Name" of this script. It will be used in your email body later. See next step

 

VikMach_10-1752975541486.png

 

 

Step 10) Type your Subject and Email body, then add your script with syntax ${mail_Script:weekly_report_request}.

VikMach_8-1752975386538.png

 

Step 10) Finally when ready, add your notification in your flow like example below. Save the flow and test before activating it for deployment. 

 

VikMach_9-1752975462887.png

 

Hope this helps!

Let me know if it worked.

 

Regards,
Vikas K

 

Hey Vik! Sorry for taking so long to reply, I needed to get admin permissions from my SNOW team. Looks like the solution worked. Thank you so much for your quick and detailed response, I spent way too much time looking at docs and trying workaround solutions that never worked.
Again, thanks! I'd be more than happy to buy you a coffee sometime!