Email notification script for Surveys

JonathanJacob · ‎05-01-2012

Hi All,

I am looking to create email notification regarding completed surveys. I have created an event in the registry and a business rule. I am in the process of creating a email notification. I am kind of stuck on how to accomplish list. I have created this mail script (below). In an effort to put my responses on a email with some sort of table with the headers I have created. Any feedback would be great!


<html>
<body>
<strong>Summary of Survey responses:</strong>

<table border="0">
<tr>
<th><b>Number</th></b>
<th><b>Name</th></b>
<th><b>Department</th></b>
<th><b>Phone</th></b>
<th><b>VIP</th></b>
<th><b>Assigned To</th></b>
<th><b>Title</th></b>
<th><b>Overall</th></b>
<th><b>Responsiveness</th></b>
<th><b>Technical Expertise</th></b>
<th><b>Information Provided</th></b>
<th><b>Professional Courtesy</th></b>
<th><b>Comment</th></b>
</tr>


<mail_script>
var gr = new GlideRecord("task_survey_detail");
var sprint = "" ;
//sys id current survey
var current_survey = "" ;
gs.log("sprint=" + sprint);
         gs.log("survey=" + current_survey);
gr.addQuery("instance", current.sys_id);
gr.query();
while(gr.next()) {
gs.log("survey=" + current_survey);
         gs.log("tasknum=" + gr.tsdts_task);
         gs.log("sprint=" + sprint);
         if (current_survey != gr.tsdinst_sys_id) {
         //template.print("<tr>"+ sprint + "</tr>");
         template.print(sprint);
         current_survey = gr.tsdinst_sys_id;
         sprint = '<td>' + gr.tsdts_task + '</td><td>' + gr.tsdinst_taken_by + '</td><td>' + gr.tsdinst_taken_by.department.getDisplayValue() + '</td><td>' + gr.tsdinst_taken_by.phone + '</td><td>'+ gr.tsdinst_taken_by.vip.getDisplayValue() + '</td><td>' + gr.tsdt_assigned_to + '</td><td>' + gr.tsdt_short_description + '</td><td>' + gr.tsdresp_response + '</td>';
       }
       else
       {
       sprint = sprint + '<td>' + gr.tsdresp_response + '</td>';
       }
         //template.print('<tr><td>' + gr.task.number + '</td><td>' + gr.taken_by.name + '</td><td>' + gr.taken_by.department.getDisplayValue() + '</td><td>' + gr.taken_by.phone + '</td><td>'+ gr.taken_by.vip.getDisplayValue() + '</td></tr>' + gr.assigned_to + '</td></tr>' + gr.task.short_description + '</td></tr>' + gr2.response + '</td></tr>');
}
</mail_script>
</table>
</body>
</html>

JonathanJacob · ‎05-09-2012

I have refined the script slightly which works a little better, but I am still having problems gathering multiple lines of data. Can anyone provide any advice?

Summary of Survey responses:

var sprint = "" ;
var current_survey = "" ;
gs.log("sprint=" + sprint);
gs.log("survey=" + current_survey);
var gr = new GlideRecord("task_survey_detail");
gr.addQuery("tsdinst_sys_created_on", "<=", gs.daysAgo(5));
gr.addQuery("tsdinst_survey", "Helpdesk Satisfaction Survey");
gr.orderBy("tsdresp_question.order");
gr.query();
while(gr.next()) {
gs.log("survey num=" + current_survey);
gs.log("sprint=" + sprint);
gs.log("sysid=" + gr.tsdinst_sys_id);

if (current_survey != gr.tsdinst_sys_id) {
gs.log(""+ sprint + "");
//template.print(""+ sprint + "");
//current_survey = gr.tsdinst_sys_id;
sprint = '"+ sprint + "");
current_survey = gr.tsdinst_sys_id;
}
//template.print(""+ sprint + "");
//current_survey = gr.tsdinst_sys_id;
//template.print('' + gr.assigned_to + '' + gr.task.short_description + '' + gr2.response + '');
}
gs.log("lastlog" + sprint);
//template.print(""+ sprint + "");

Number	Name	Department	Phone	VIP	Assigned To	Title	Overall	Responsiveness	Technical Expertise


' + gr.tsdts_task.getDisplayValue() + '	' + gr.tsdinst_taken_by.getDisplayValue() + '	' + gr.tsdinst_taken_by.department.getDisplayValue() + '	' + gr.tsdinst_taken_by.phone.getDisplayValue() + '	'+ gr.tsdinst_taken_by.vip.getDisplayValue() + '	' + gr.tsdt_assigned_to.getDisplayValue() + '	' + gr.tsdt_short_description.getDisplayValue() + '	' + gr.tsdresp_response.getDisplayValue() + '	'; } else { sprint = sprint + gr.tsdresp_response + '	'; template.print("


' + gr.task.number + '	' + gr.taken_by.name + '	' + gr.taken_by.department.getDisplayValue() + '	' + gr.taken_by.phone + '	'+ gr.taken_by.vip.getDisplayValue() + '

Chuck Tomasi · ‎05-09-2012

Debug tip: Have you tried using the Background scripts to test the JavaScript code and verify you are getting multiple records w/all the expected output statements? Your gs.log() statements will print right to the screen so you should see lots of lovely HTML. Give that a try to ensure the correctness and completeness of your script before inserting it back in to the mail script.

JonathanJacob · ‎05-09-2012

Hi Chuck,

Thanks for the tip!

I have tried the background script which works however here is the issue I am having.

previously I had: gr.addQuery("instance", current.sys_id);
which worked perfectly however the problem was I need more than one survey for each email notification.

when I changed that to: gr.addQuery("tsdinst_sys_created_on", "<=", gs.daysAgo(5));

it no longer works. It seems like the while loops is providing some strange results, I think this has to do with the gr. response variable.

Chuck Tomasi · ‎05-09-2012

Yeah, you'll want to exchange 'current' for some other GlideRecord on that same table. If you have a specific sys_id in mind, use that.

For example:



var id = '88e8a8d36d55c772737ce0012b1333'; // ID of a sample 'current' record

// more stuff here to set up var gr = new GlideRecord()

gr.addQuery('instance', id);