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.

[Ashok10]

Hi Rob,

I can see order field is there question_answer table. So can you add   'ql.orderBy('order');' after that encoded query.

That is

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

ql.orderBy('order');

I guess it works. Please give a try and let me know

[Kalaiarasan Pus]

Rob, will give you a update by today . @sai that will not work .

[Kalaiarasan Pus · Accepted solution]

Rob, Try this

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() + ": " + current.sysapproval.variable_pool[ql.question.name].getDisplayValue()+ "\n");      

}

should work just well

[rjmace]

Perfect! That works exactly how I need it to!

Thanks a lot for your help Kalai!

[Ashok10]

Kalai,

Understood that record producer variables will store in 'question_answer' table. Can you please tell me where exactly catalog items variables   stores.