Displaying Additional comments in Email notification.

Maxwell3 · ‎02-14-2020

Hello all,

I am querying the sys_journal_field table to display the Additional comments on email notifications, I am also using getRowCount to display the comments' respective order number next to them on the notifications.

I am having issues displaying the correct number next to them, please see the example below. The "current status" should say "Update 8" because it is the 8th/last comment on the incident but instead it says "Update:1". The other updates should say Update 5, 6,7 but instead they say 2,3,4.

I am using the script below for both but separately, the only difference is that I am using gr.setLimit(1) for the "current status" and gr.setLimit(5) for the other ones.

var gr = new GlideRecord('sys_journal_field');
//In the sys_journal_field table, if the element column contains comments as opposed to work_notes & the element_id matches the current id than return the current elements & order in Ascending order.
gr.addQuery('element','comments');
gr.addQuery('element_id',current.sys_id.toString());
gr.orderByDesc('sys_created_on');
gr.setLimit(5);
gr.query();

var i = gr.getRowCount();
gr.next();

while(gr.next()){

if(i>2)
{
i=i-1;
var date = gr.sys_created_on;
var nowDateTime = new GlideDateTime(date);
var gd1 = new GlideDate();
gd1.setValue(nowDateTime.getLocalDate());
var gt = new GlideTime();
gt.setValue(nowDateTime.getLocalTime());
var str = gd1.getByFormat("MM/dd/yyyy");
var t = gt.getByFormat('hh:mm a');
//After re-formatting the GlideDate & GlideTime, assign the values to 'str' and print out the results.

template.print(current.priority.getDisplayValue()+' - UPDATE: '+i.toString()+' received an update on ' + str +' at '+ t +' ET: '+ gr.value.toString());
template.print('<br />');
template.print('<br />');
}
}

Allen Andreas · ‎02-18-2020

Hello,

Yea I mentioned the alternative, which would be:

The other alternative would be to set a "count" variable to the row count and then in each while loop, just reduce it by 1 each time, thus doing the count for you since you know how many records there'll be. Since you're going from Newest to Oldest which would mean you'd need the higher row number first...rather than last.

So within the query lines, you'd do something like:

var gr = new GlideRecord('incident');
gr.addQuery('active', 'true');
gr.orderByDesc('sys_created_on');
gr.query();
var count = gr.getRowCount();
while(gr.next()) {
Do some stuff here
and here
and here
then last line would be: 
if (count == 1) {
count = "Initial";
 } else {
count = count - 1;
 }
}

So you can use the "count" variable as your "update number" and it'll start at the highest number, then when it would get to 1, it'll change to the word "Initial", which is what you wanted for your first entry.

Please mark reply as Helpful/Correct, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

View solution in original post

Maxwell3 · ‎03-14-2020

Hello Balaji,

Is there a way to display the additional comments after an Incident has been re-opened. After an Incident gets re-opened, I would like to display only the additional comments posted after the Incident gets re-opened.

Allen Andreas · ‎03-14-2020

Hello,

You didn't ask that question to me...but...I'll provide my 2 cents.

You could attempt to do this two ways, maybe?

1) Look at the Reopen Count field on the incident and then send any comments after date/time the value is 1 or higher (which may require you to look at the sys_audit of that record...a bit more complicated).

2) You can perhaps create a field on the incident table to log when an incident is reopened with a date/time (so a business rule when update of incident where the state changes from Resolved or Closed to any other state), then use that in your query for additional comments created after reopened date/time.

Please mark reply as Helpful, if applicable. Thanks!

Please consider marking my reply as Helpful and/or Accept Solution, if applicable. Thanks!

Maxwell3 · ‎03-16-2020

I always appreciate your "2 cents" Allen.