First, I really had a hard time coming up with a good headline for this, so forgive me
I had a community member asking me a question and wanted me to explain it in a blog post, so here we go.
Background:
I'm using the related list "Time worked" on incidents. Now, whenever someone adds a new record in that related list or updates a existing one it should calculate the totalt time in a custom field on the incident record. And this should happen right after the time worked record is saved.
Now, there is 2 ways to do this, depending on what you want to do with the data. If it's only for show on the incident record I recommend using list control on the related list. and this is how you do that:
Right-click on the column you want to calculate and choose "List Calculations".
There you can choose the Total value and vola, you got the data at the bottom.
But then, if you might want to do reports on this value or something else, you want to put this data in a field on the incident form instead.
And here is an example how to do that with an after Business rule.
1. Create the field on the incident form.
I have chosen to create a duration field called "Total time worked" on the incident form and it gets the name u_total_time_worked. I also set the attribute max_unit=hours so I get the duration in hours and not the version where it involves days etc. more of a personal taste and you don't need to do it.
2. Getting the data in there
Since the Time worked isn't on the incident record we need to make an After Business Rule on the Time worked table that update the field on the incident. I made a condition on the Business Rule so that it should only run when the record is connected to an incident.
And here comes the script part that calculates and updates the incident record
(function executeRule(current, previous /*null when async*/) {
//Get the incident which this time worked record is connected to
var gr = new GlideRecord('incident');
gr.get(current.task);
//Now get all the time worked records connected to this incident
var gr1 = new GlideRecord('task_time_worked');
gr1.addQuery('task', current.task);
gr1.query();
var sec = 0;
//Lets count all the seconds time has worked on the inciddent
while (gr1.next()){
sec = sec + gr1.time_in_seconds;
}
//Since GlideDuration wants milliseconds we need to transform it from secs to millisecs.
sec = sec * 1000;
//And lets update the field on the incident
gr.u_total_time_worked = new GlideDuration(sec);
//We don't want any BRs etc. to run when we set this field
gr.setWorkflow(false);
//We also dont want to lasted updated by and last updated on the field to set after this.
gr.autoSysFields(false);
gr.update();
})(current, previous);
And now you can see on the incident form how long time you have worked on it:
6 Comments
