Record Producer variables in emails

[rjmace]

Hi Everyone,

We're currently in the process of implementing the HR Catalog to our environment, and as part of that process I'm working on the emails that are fired off when HR cases are lodged, approvals requested and the like.

At the moment, I'm working on the approval email. When a customer submits a form, which is a record producer from the HR catalog and it's sent off for approval, an email is sent to the approver. The email itself is going through fine, however I want to include the variables I have added to the record producer (all of our forms are using variable fields due to the fact that there isn't much commonality between them, so adding them as fields to the actual hr_case table wouldn't work.) in the email that goes to the approver.

We have a similar setup for our Service Catalog, but being that it words of of the sc_req_item table and the fact that there are packages unique to the Service Catalog, using the mail script I have there doesn't seem to be working. So far I have:

<mail_script>
   var item = new GlideRecord("hr_case");
   item.addQuery("sys_id", current.sysapproval);
   item.query();
   while(item.next()) {
         var keys = new Array();
           var set = new GlideappVariablePoolQuestionSet();
           set.setRequestID(item.sys_id);
           set.load();
           var vs = set.getFlatQuestions();
           for (var i=0; i < vs.size(); i++) {
   var currentVar = vs.get(i);

   if (currentVar.getDisplayValue() != 'false' && currentVar.getDisplayValue() != '' && currentVar.getDisplayValue() != '-- None --') {
   template.print('<tr><td style="padding: 5px 5px 5px 5px; text-align: left; font-family: Arial, sans-serif; font-size: 12px;   line-height: 14px; color:black;"');
   if (currentVar.getDisplayValue() == 'true' || currentVar.getDisplayValue() == "Select the room(s) you're connecting to") {
   template.print('></td><td style="padding: 5px 5px 5px 5px; text-align: left; font-family: Arial, sans-serif; font-size: 12px;   line-height: 14px; color:black;"');
   }

   template.print('>');
   template.print("<b>" + currentVar.getLabel() + "</b>");

   if (currentVar.getDisplayValue() != 'true') {
   template.print('</td><td style="padding: 5px 5px 5px 5px; text-align: left; font-family: Arial, sans-serif; font-size: 12px;   line-height: 14px; color:black;">'+ currentVar.getDisplayValue());
   }
   template.print('</td></tr>\n');
   }

           }
   }
   template.print('</table>\n');
</mail_script>

I believe my issues are starting from the declaration of the 'set' variable on line 7, which uses the GlideappVariablePoolQuestionSet package. From what I've found from the Wiki, this is a package unique to the Service Catalog, which I'm guessing as a result would mean it would fail to pull down the variables of a record on the hr_case table, which in return means the mail script fails to produce anything. To confirm this I tried a gs.log within the for loop which did not appear in the logs.

Has anyone done anything similar? If so, how were you able to resolve this? Is it a matter of using a different package to retrieve the variables? I found another topic in the boards but it didn't appear that a resolution was found.

Thanks in advance for any assistance.

[Kalaiarasan Pus]

this is the table that hold's the variables value for record producers. You need to change your script and glide through this table....

question_answer

[Kalaiarasan Pus]

this should help ... modify as per your need...

1. <mail_script>  
2. for (name in current.sysapproval.variable_pool) {  
3.       if (current.sysapproval.variable_pool[name]) {  
4.               var q = getQuestion(current.sysapproval.sys_id, name);  
5.               template.print(q + ": " + current.sysapproval.variable_pool[name].getDisplayValue() + "\n");  
6.       }  
7. }  
8.  
9. function getQuestion(recID, name) {  
10.       var ql = new GlideRecord('question_answer');  
11.       ql.addEncodedQuery('table_sys_id=' + recID + '^question.name=' + name);  
12.       ql.query();  
13.       var qLabel = '';  
14.       while (ql.next()) {  
15.               qLabel = ql.question.getDisplayValue();  
16.       }  
17.       return qLabel;  
18. }  
19. </mail_script>  

[Kalaiarasan Pus]

just wrote this .. haven't been able to nail it but you could use this as a reference ...

var ql = new GlideRecord('question_answer');

      ql.addQuery('table_sys_id',current.sysapproval.toString());

      ql.orderBy('order');

      ql.query();

      while (ql.next()) {

              template.print(ql.question.getDisplayValue() + ": " + ql.value+ "\n");

      }

Note : Reference field will return sys_id .. So needs a workaround ...

[rjmace]

Thanks for your help Kalai, I tried something similar to your suggestion using a glidequery for question_answer and hit the same road block in that the reference fields are returning their sys ID..

Does anyone know if it's possible to sort the list of question answers by the order I set in the record producer variable?

Right now, the mailscript I'm using(as it returns all results) is:

<mail_script>  

template.print('<table cellpadding="0" cellspacing="0" border="0">\n');  

for (name in current.sysapproval.variable_pool) {  

      if (current.sysapproval.variable_pool[name]) {  

              var q = getQuestion(current.sysapproval.sys_id, name);  

template.print('<tr><td style="padding: 5px 5px 5px 5px; text-align: left; font-family: Arial, sans-serif; font-size: 12px;   line-height: 14px; color:black;"');  

template.print('>');            

template.print("<b>" + q + ": " + "</b>" + current.sysapproval.variable_pool[name].getDisplayValue() + "\n");  

      }  

}  

function getQuestion(recID, name) {  

      var ql = new GlideRecord('question_answer');  

      ql.addEncodedQuery('table_sys_id=' + recID + '^question.name=' + name);  

      ql.query();  

      var qLabel = '';  

      while (ql.next()) {  

              qLabel = ql.question.getDisplayValue();  

      }  

      return qLabel;  

}  

template.print('</td></tr>\n');  

template.print('</table>\n');  

</mail_script>  

I've tried modifying the encoded query to add ^ORDERBY or adding a line for ql.orderBy which also didn't work.

Has anyone solved this issue before?

Thanks in advance.