Showing Request Item / Catalog Variables in Mail Script

[wattsj]

I'm using an email notification to send out information regarding approvals on RITM's. The information I want to send will contain the variable information from the item that was ordered.

I stole this script from another email notification:


template.print("Summary of Requested item:\n");
var gr = new GlideRecord("sc_req_item");
gr.addQuery("sys_id", current.sysapproval);
gr.query();
while(gr.next()) {
template.print(gr.number + ": " + gr.quantity + " X " + gr.cat_item.getDisplayValue() + "\n");
template.print(" Options:\n");
for (key in gr.variables) {
var v = gr.variables[key];
if(v.getGlideObject().getQuestion().getLabel() != '' && v.getDisplayValue() != '') {
template.space(4);
template.print(' ' + v.getGlideObject().getQuestion().getLabel() + " = " + v.getDisplayValue() + "\n");
}
}
}


My only issue is that the variables don't display in the proper order. You would think this would be an easy thing to accomplish, but I've been stuck for awhile. Any ideas?

[martincohn]

So we had the script documented by wattsj working perfectly for months. Approval - User activity in the workflow, calls the Email Notification: Item Approval Request, then hits the Email template: request.itil.approve.role.item with the script modified as described by wattsj.

Did a refresh/clone (copied Prod over our Dev instance) and it stopped working. SNC's explanation was...

"This bit of code queries the sc_item_option_mtom table for all request_items that match the current approval request. That is, it asks the database for all of the options inside this request. It then makes sure that the option is something that needs to be visible, and that there is a label for the option, before printing that option into e-mail.

Prior to the clone, these options were returned by the server in the default order- in mysql, that is by the date they were inserted into the database. This is different than the "created" date on the record itself, which can be changed or modified or overwritten. It is the actual date in which this actual record was really inserted into the database. When you cloned your production data over, all of these records get the same system-level date. That is, they all get the same "timestamp" for when they were entered into the database. The record themselves are not changed- those records are copied over exactly as they were on Production. it's the metadata associated with those records- the stuff that the MySQL server keeps behind-the-scenes, that is different.

Because the metadata on these records indicates they were inserted into the database at the same time, the sorting then becomes random. The MySQL server will return the options in whichever fashion is currently quickest. From our viewpoint, this is practically random, since we aren't aware of the current the position of the read head on the disk drive, or the current memory capacity of the MySQL process, or wether the data is already in memory somewhere, or any of the other various things that could affect this. To us, it seems random because it's different every time.

If you want to get these options in a particular order, you need to add a sort option to the query, telling the MySQL server that you want to get the data back in a particular order. Here is the wiki page on GlideRecord's sort option:
http://wiki.service-now.com/index.php?title=GlideRecord#orderBy

You most likely want to order by sys_created_on."

Have tried to do this and some other options but no joy.

What we would like to do is to use the variable order value (table: Question [question], Column name: order) to get the variables back in the correct (consistent) order. Any assistance getting this working again would be greatly appreciated!

Thanks
Martin

[Brad Tilton]

I haven't tested this, but I added an orderBy query on line 12 that I think will get the job done.

<mail_script>
  template.print("Summary of Requested item:\n"); 
  var gr = new GlideRecord("sc_req_item");
  gr.addQuery("sys_id", current.sysapproval);
  gr.query();
  while(gr.next()) {
template.print(gr.number + ":  " + gr.quantity + " X " + gr.cat_item.getDisplayValue() + "\n");
template.print("    Options:\n");

var varown = new GlideRecord('sc_item_option_mtom');
varown.addQuery("request_item", current.sysapproval);
varown.orderBy("sc_item_option.order");
varown.query();
while (varown.next()){
var visible = varown.sc_item_option.item_option_new.visible_summary;
var question = Packages.com.glideapp.questionset.Question.getQuestion(varown.sc_item_option.item_option_new);
question.setValue(varown.sc_item_option.value);
if (question.getLabel() != "" &amp;&amp; question.getDisplayValue() != "" &amp;&amp; visible == true){
template.space(4);
template.print('     ' +  question.getLabel() + " = " + question.getDisplayValue() + "\n");
}
}

  }
</mail_script>

[shill]

I couldn't find any documentation on it anywhere, but have discovered if you set the 'Visible on Summaries' field on the variable form to false, it will stop it from adding that variable on the email using Brad's script.
This came in real handy when I had a workflow variable that I really didn't need the approver to see.

[Aartz]

Hi, i have a similar requirement. I tried this script.

But at the line "Packages.com.glideapp.questionset.Question.getQuestion(varown.sc_item_option.item_option_new);" it doesnt do anything

I mean it doesn't log or throw any error.

Any idea why is it happening? Do i need to activate any plugin for this package?

[SteveS]

if you are on Calgary then you need to replace the Packages call
//before Calgary
//var question = Packages.com.glideapp.questionset.Question.getQuestion(varown.sc_item_option.item_option_new);
on Calgary
var question = GlideappAbstractChoiceListQuestion.getQuestion(varown.sc_item_option.item_option_new);

entire script would be something like this

template.print("Summary of Requested item:\n");
var scReqItem = new GlideRecord("sc_req_item");
scReqItem.addQuery("sys_id", current.sysapproval.toString());
scReqItem.query();

while (scReqItem.next()) {
gs.print(scReqItem.number + ": " + scReqItem.quantity + " X " + scReqItem.cat_item.getDisplayValue() + "\n");
gs.print(" Options:\n");

var varown = new GlideRecord('sc_item_option_mtom');
varown.addQuery("request_item", current.sysapproval.toString());
varown.orderBy("sc_item_option.order");
varown.query();

while (varown.next()) {
var visible = varown.sc_item_option.item_option_new.visible_summary;
//var question = Packages.com.glideapp.questionset.Question.getQuestion(varown.sc_item_option.item_option_new);//Packages call replaced with line below on CAlgary+
var question = GlideappAbstractChoiceListQuestion.getQuestion(varown.sc_item_option.item_option_new);
question.setValue(varown.sc_item_option.value);

if (question.getLabel() != "" && question.getDisplayValue() != "" && visible == true) {
template.space(4);
template.print(' ' + question.getLabel() + " = " + question.getDisplayValue() + "\n");
}
}
}


for more information on Packages calls replacement make sure you take a look at http://wiki.servicenow.com/index.php?title=Packages_Call_Replacement_Script_Objects