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 · ‎02-17-2020

Thank you Allen,

Is there a way to display the row number but minus 1. For example, Update 2 will display on the email as Update 1 and Update 3 will display as Update 2 and so on?

Allen Andreas · ‎02-17-2020

Hi,

I mean you can subtract 1 from the row number by turning the string in to a number be doing something like:

var num = gr.getRowNumber();
var newNum = Number(num);
newNum = newNum - 1;

Or you may need to use

gr.getLocation()

You'll need to try it out and see what you get.

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

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

Maxwell3 · ‎02-18-2020

Allen,

I have replaced getRowCount() with getRowNumber(), but it doesn't seem to be working. May I be missing something?

Allen Andreas · ‎02-18-2020

Hi,

Can you try:

gr.getLocation()

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

Maxwell3 · ‎02-18-2020

Allen,

getLocation() does not work either.