caluculation

SK5555 · ‎01-17-2025

calculate the time duration between when was record created and when the email was trigger

?

ex: duration = email created time - record created time

Ankur Bawiskar · ‎01-17-2025

whenever you are triggering that email you know the now time, you also know on which record email triggered

So pick the sys_created_on time

Get the difference using script something like this in your email script if you wish to send this difference in email body

var nowTime = new GlideDateTime();
var createdTime = new GlideDateTime(current.getValue('sys_created_on'));
var dur = new GlideDuration();
dur = GlideDateTime.subtract(createdTime, nowTime); //the difference between 
template.print(dur.getDisplayValue());

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

View solution in original post

SK5555 · ‎01-21-2025

I have used Database view and Joined two tables Alert and Sys Email and two variables one is alert created on and another one is sys email created on but I am not getting the value of Sys_mail.created on ?
I have checked the notifications also , notifications are triggering and stored in sys_email tables but when comes on database view table I am getting only alert table created on value . you can below script will get an idea
var grr = new GlideRecord("u_new_test"); //this is database table
grr.orderBy('sys_created_on'); // Get the earliest email
grr.setLimit(1);
grr.query();
grr.query();
if (grr.next()) {
var alrtCreatedTime = new GlideDateTime(grr.em_sys_created_on);
var emailCreatedTime =new GlideDateTime(grr.ml_sys_created_on); //This is the one not getting value
var duration = emailCreatedTime.getNumericValue() - alrtCreatedTime.getNumericValue();
gs.info('duration test : '+duration);
}

Ankur Bawiskar · ‎01-21-2025

@SK5555

Please close this thread by marking my response as correct as my comment helped you.

The discussion can continue on your subsequent question.

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Ankur Bawiskar · ‎01-21-2025

@SK5555

what's your business requirement?

are you sure you are joining the tables with correct view fields?

I hope Created on is added as view table for sys_email view table

are you using the correct prefix for the database view?

please share screenshot of your database view and the view tables and it's view field

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader