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

sachin_namjoshi · ‎02-14-2020

There is already OOB method available to query journal field.

You can use getJournalEntry() method to return additional comments field value

template.print(current.work_notes.getJournalEntry(1));

Regards,

Sachin

Maxwell3 · ‎02-17-2020

Thank you Sachin,

I have a requirement to only display the comments without the name of the users or the date/time.

Tony Chatfield1 · ‎02-17-2020

Querying core system tables is not an advisable solution and you should be able to drop user and timestamp from an OOB journal entry; try var myComment = current.comments.getJournalEntry(1); var pat = /.*/; template.print(myComment.replace(pat,""));

Allen Andreas · ‎02-14-2020

You can use:

gr.getRowNumber()

to get the row it's on and if you're filtering to just comments, that the update number should line up for you.

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

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