Calculate a Duration Between the Created Date and Today's Date

[Joshua Cassity]

Man oh man.. how frustrating Date/Time calculations can be, am I right?

I built a new table for a customer who's looking to track their build book processes and needs to have four duration fields calculated by the system when he opens a record. I'm thinking if I can get one of them then I can easily get the rest so I've tried a couple of things with no success - see screen below for form layout.

Essentially the customer would like the duration between the system created time stamp (sys_created_on) and the current system date (the problem for me) and to have that duration be shown on the Total Time Open duration field (u_total_time_open).

Has anyone else gone though this kind of nightmare land and can lend a guy some assistance? Did i mention that I really hate date/time calculations.... lol

Bonus Question:   I'm pretty sure I can do the other duration calculations since those fields are actually on the form and already being date stamped but will those duration calculations work if the Created field is a (date/time) and the other fields are date only OR do I need to make them date/time as well?

Thanks in advance.

[drjohnchun]

For your case, I'd modify it to something like below so today's date/time is obtained in Script Include:

Add a Client Script:

function onChange(control, oldValue, newValue, isLoading) {
   var strt = g_form.getValue('<start_field>');
   //var end = g_form.getValue('<end_field>');
   var ajax = new GlideAjax('AjaxDurCalc');
   ajax.addParam('sysparm_name','durCalc');
   ajax.addParam('sysparm_strt',strt);
   //ajax.addParam('sysparm_end',end);
   ajax.getXMLWait();
   var answer = ajax.getAnswer();
   g_form.setValue('<duration_field>', answer);
}

And add a Script Include:

var AjaxDurCalc = Class.create();
AjaxDurCalc.prototype = Object.extendsObject(AbstractAjaxProcessor, {
   durCalc: function() {
       return gs.dateDiff(this.getParameter('sysparm_strt'),new GlideDateTime().getDisplayValue(), false);
   }
});

[Joshua Cassity]

Unfortunately that didn't work either.

Script Include (AjaxDurCalcToday😞

var AjaxDurCalcToday = Class.create();
AjaxDurCalcToday.prototype = Object.extendsObject(AbstractAjaxProcessor, {
       durCalcToday: function() {
               return gs.dateDiff(this.getParameter('sysparm_strt'),new GlideDateTime().getDisplayValue(), false);
       }
});

And the onLoad client script on the form (Calculate Total Time Open😞

function onLoad() {

       function onChange(control, oldValue, newValue, isLoading) {   
           var strt = g_form.getValue('sys_created_on');     
           var ajax = new GlideAjax('AjaxDurCalcToday');   
           ajax.addParam('sysparm_name','durCalcToday');   
           ajax.addParam('sysparm_strt',strt);   
           ajax.getXMLWait();   
           var answer = ajax.getAnswer();   
           g_form.setValue('u_total_time_open', answer);   
       }   

}

I had to add the suffix of 'Today' to both the script include and the function because the original ones are already being used successfully in other applications where both the dates are already present in fields.

I really don't know why ServiceNow doesn't include date/time math in their applications and why they simply don't let you set these via parameter in duration fields. Very, very, very frustrating.

[drjohnchun · Accepted solution]

Can you try

function onLoad() {
       var strt = g_form.getValue('sys_created_on'); 
       var ajax = new GlideAjax('AjaxDurCalcToday'); 
       ajax.addParam('sysparm_name','durCalcToday'); 
       ajax.addParam('sysparm_strt',strt); 
       ajax.getXMLWait(); 
       var answer = ajax.getAnswer(); 
       g_form.setValue('u_total_time_open', answer); 
}

[Joshua Cassity]

That appears to have done it... goo grief!!!! I have no clue why I had that onChange in there. Must have been a copy/paste deal.

For anyone else who would like to have the solution, here it is:

Script Include (AjaxDurCalcToday):

var AjaxDurCalcToday = Class.create();
AjaxDurCalcToday.prototype = Object.extendsObject(AbstractAjaxProcessor, {
       durCalcToday: function() {
               return gs.dateDiff(this.getParameter('sysparm_strt'),new GlideDateTime().getDisplayValue(), false);
       }
});

Client Script (Calculate Total Time Open):

function onLoad() {
       var strt = g_form.getValue('sys_created_on');
       var ajax = new GlideAjax('AjaxDurCalcToday');
       ajax.addParam('sysparm_name','durCalcToday');
       ajax.addParam('sysparm_strt',strt);
       ajax.getXMLWait();
       var answer = ajax.getAnswer();
       g_form.setValue('u_total_time_open', answer);
}

Begin Date = sys_created_on

Target Duration Field = u_total_time_open

One down.. three more to go!!!!

Thanks for your patience John. It's been a real help to us.

[drjohnchun]

Glad it worked out. As for your bonus question, yes, you can mix date and date/time for a duration calculation.