Just want to clarify that I've edited this post to include the more danger of calculated value and perhaps not being the pot of gold for me and more like a devil in disguise. I even thought that I might delete this post since afterwards other beloved community members pointed out their experience with this and there might be some not so good things about it.But then I came to the conclusion that there isn't so much info about this and I better keep this post to point out the good and bads with it and hopefully some other than me gets the knowledge of it.
So... Calculated value. This was something I didn't knew of before, but when I found out what I could do with it, I really start loving it, but it might have been a short term relationship
Lets tell the story from the start.
It all began when I was looking at a report that was going to tell me the average time of an SLA an it looked something like this:
And I had 2 big problems with this...
- For example 2 days, 7 hours, 33 min, 20 sec. This doesn't mean 2 working days and 7 hours. This mean 2*24 hours + 7 hours and that is a total of 56 working hours, which is over one working week! *Not a single person I've talked to and showed this report thought it was like that. They all went for the 2 working days..
- Having the average split up like this... "4 days 1 hour 13 min 20 sec" and "2 days 21 hours 26 min 40 sec" etc. It doesn't look good in my eyes at all. And this isn't the worst example of them all.
So after some thinking we decided that we would like to have the same report, but instead of mixing it with days etc, we want it to just show hours. That is much easier to understand and grasp.
My first idea was to just put on some makeup on the field "Business elapsed time". So I went to the dictionary override and put in the follow attribute "max-unit=hours".
And from the start, it was looking good. Now days are gone and it sums up on hours like this:
But sadly, the report acted like it didn't know anything and still showed the report with days..
So I ended up with created a own field that I would put the hours in. So I created a decimal field on the task_sla table named "
var dur = new GlideDuration(current.business_duration.getDurationValue());
var millisec = dur.getNumericValue();
var hours = millisec/1000/60/60;
current.u_business_duration_in_hours = hours;
But then I was going to fill in this on all the already completed records, so I went into the script - background to test it out and I noticed it took quite a while to do it so I start looking for another way around to make this happening.
Then I came across the "calculated value". You reach this by right clicking on the field and choosing "configure dictionary". Then you got a tab called "Calculated value". If you don't see it you need to turn on "Advanced view". Check the box "Calculated" and a magic script editor appears.
Now if you take the script above and just adjust it a tiny bit to this:
(function calculatedFieldValue(current) {
var dur = new GlideDuration(current.business_duration.getDurationValue());
var millisec = dur.getNumericValue();
var hours = millisec/1000/60/60;
return hours.toFixed(1); // return the calculated value
})(current);
And then you press update.
Now you don't need a business rule for to make the value and the best part is by doing this, all your already created records with this field is automatically without needed to do any scripting in the Script - background. And it goes fast 😃
Now for example a report can look like this:
Now I was all happy and thought I found the real pot of gold and a new good way to "calculate fields". But like for example jim.coyne in the comments below, this is a potential performance crasher.
These are the ways I would do it.
1. Using a Business rule to calculate the value when you update the record and by using this it will only be updated when you edit the record, but most often this will be good enough.
2. Using Calculated value will always calculate the value when the record is queried. Meaning that when you looking at a list with the records, all of the records will update the calculated field value. Now, when your instance is growing this might not be a problem in the beginning, but it can be in the future when your numbers is a lot bigger. Another thing that I came thinking of is that using calculated field might be harder to find in the future when your'e looking at the field. It's easier to find a business rule when looking at the field and trying to find out what is putting the numbers in the field.
3. last way would be running a scheduled job to update the fields, but I think I rather go for nr 1.
So rather that says wooo, here is a good new way. I rather say that really not use it and if you do, use it with caution.
4 Comments
