Is it possible to get the sum of time_worked for a task from the task_time_worked table in a client script?

[abrahams]

I've been asked to require additional fields when entering time while in a task record. They would like to be able to enter in time as they are working a task without losing changes that they made on the form. The requirement is also to give a warning message should they forget to enter in time worked. To do this I created a dialog window that allows them to enter in the required information without losing changes to the form. The problem I am encountering is that it doesn't update the timer when I do that until the form is refreshed. Would anyone know of a way to update the timer or have any idea how I might be able to tell if they entered time while in the form?

[abrahams]

After quite a bit of trial and error I was able to get this to work. It might not be pretty but it appears to work.

I didn't refresh the timer. I couldn't figure out how to do that but I did find a way to determine if the user entered time into the time worked record associated with the task at the time of submit.

What I did was create a script include that has two parameters (task sys_id and current user sys_id) which sums the time worked for the current task and user.

I have a display business rule that gives me the sum of time worked for the current task and user and places it into a scratchpad at the time of display.

I created an onSubmit client script that calls the script includes to get the time worked for the current task and user. I convert all time to seconds and compare the value in the scratchpad to the one returned by the script includes to see if there is a difference.

I would be willing to share my scripts if there is an interest.

[pavankum]

HI Abraham,

Could you please share your scripts.

[abrahams]

I am rather new at coding so there may possibly be a better way of doing this. I changed my code to use time_in_seconds and removed my time conversion. I thought as long as seconds exist I would use them. I removed any of our sys_id's.

I found this syntax in a number of places on the wiki. I don't remember them all but I will list what I remember.
http://wiki.servicenow.com/index.php?title=GlideAggregate
http://wiki.servicenow.com/index.php?title=Script_Includes
http://wiki.servicenow.com/index.php?title=GlideAjax
-------------------------------------------------------------------------------------------------------------
Business Rule Used to write the time worked in seconds to a scratchpad:

Table: Catalog Task [sc_task]
When: Display
Run at: server
Script:
// Gather time worked at the time of display
var timeWorked = getTimeWrk(current.sys_id,gs.getUserID());
g_scratchpad.prevTmWrk = timeWorked;

/*----------------------------------*/
/* Helper functions */
/*----------------------------------*/
// Get all time worked for this task and user
function getTimeWrk(curTask,curUser) {
var tw = new GlideAggregate('task_time_worked');
tw.addQuery('task', curTask);
tw.addQuery('user',curUser);
tw.addAggregate('SUM', 'time_in_seconds');
tw.groupBy('task');
tw.query();
if (tw.next()) {
var timeworked = tw.getAggregate('SUM', 'time_in_seconds');
return timeworked;
}
return '';
} // End getTimeWrk function
-------------------------------------------------------------------------------------------------------------
Script Include used to calculate time in seconds which is called in an onSubmit client script:

Description: Return time worked for a given task and user.
Initialize not used because I did not want to override the methods of AbstractAjaxProcessor
Script:
var userTimeWorked = Class.create();
userTimeWorked.prototype = Object.extendsObject(AbstractAjaxProcessor,{
userTimeWorked: function() {

// Get all time worked for this task and user
var curTask = this.getParameter('sysparm_task');
var curUser = this.getParameter('sysparm_user');
var twGa = new GlideAggregate('task_time_worked');
twGa.addQuery('task', curTask);
twGa.addQuery('user',curUser);
twGa.addAggregate('SUM', 'time_in_seconds');
twGa.groupBy('task');
twGa.query();
if (twGa.next()) {
var timeworked = twGa.getAggregate('SUM', 'time_in_seconds');
return timeworked;
}
return '';
}
});
-------------------------------------------------------------------------------------------------------------
Client script:
Type: onSubmit
Table: Catalog Task[sc_task}
Script:
function onSubmit() {
// Get Item
var item = g_form.getReference('request_item').cat_item;

// Process if Catalog Item
if(item == '[sys_id of my catalog task item placed here]' && g_form.modified) {;

// Set up variables
var curTask = g_form.getUniqueValue();
var curUser = g_user.userID;

// Gather current time worked for current task and user
var curTmWrk = getTimeWrk(curTask,curUser);

// Determine if current time worked same as previous time worked
var prevTmWrk = 0;
if (g_scratchpad.prevTmWrk && g_scratchpad.prevTmWrk != '') {
prevTmWrk = g_scratchpad.prevTmWrk;
}

if (prevTmWrk == curTmWrk) {
return confirm('Did you remember to record your time worked ...\nAre you sure you want to save this record?');
}
return true;
} // End item if

} // End onSubmit if

/*----------------------------------*/
/* Helper functions */
/*----------------------------------*/
function getTimeWrk(curTask,curUser) {
var ga = new GlideAjax('userTimeWorked');
ga.addParam('sysparm_name','userTimeWorked');
ga.addParam('sysparm_task',curTask);
ga.addParam('sysparm_user',curUser);
ga.getXMLWait();
var answer = ga.getAnswer();
return answer;
} // End getTimeWrk function
-------------------------------------------------------------------------------------------------------------