“It’s not about ideas. It’s about making ideas happen.”
— Scott Belsky
Last time, we played around with the Customer column in our new example data table, and today we will jump back over to the Labor Hours column and see if we can create that mouse-over breakdown of who put in the hours. The first thing that we will need to do is to gather up the data, so will need to add a groupBy to our GlideAggregate so that we can obtain the hours per technician.
var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.groupBy('user');
timeGA.orderBy('user');
timeGA.query();
Once we have the data, we will need to format it for display. There are a number of different ways that we can approach this, including straight CSS and many creative variations, but the easiest thing to do is to just use the title attribute of a span, which seems to accomplish the same thing.
To create the text, we can establish some variables before we go into the while loop, and then inside the loop we can build up the text, one person’s hours at a time.
var hours = 0;
var tooltip = '';
var separator = '';
var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.groupBy('user');
timeGA.orderBy('user');
timeGA.query();
while (timeGA.next()) {
var total = timeGA.getAggregate('SUM', 'total') * 1;
hours += total;
tooltip += separator + timeGA.getDisplayValue('user') + ' - ' + total.toFixed(2);
separator = '\n';
}
Once we accumulate the total hours and build up the tooltip text, we can then format the HTML we want, but only when there are hours charged to the task.
if (hours > 0) {
response = '<span style="text-align: right; width: 100%;" title="' + tooltip + '">' + hours.toFixed(2) + '</span>';
}
Putting it all together, our new function to provide right-justified total hours with a mouseover breakdown looks like this:
getScriptedValue: function(item, config) {
var response = '';
var hours = 0;
var tooltip = '';
var separator = '';
var timeGA = new GlideAggregate('time_card');
timeGA.addQuery('task', item.sys_id);
timeGA.addAggregate('SUM', 'total');
timeGA.groupBy('user');
timeGA.orderBy('user');
timeGA.query();
while (timeGA.next()) {
var total = timeGA.getAggregate('SUM', 'total') * 1;
hours += total;
tooltip += separator + timeGA.getDisplayValue('user') + ' - ' + total.toFixed(2);
separator = '\n';
}
if (hours > 0) {
response = '<span style="text-align: right; width: 100%;" title="' + tooltip + '">' + hours.toFixed(2) + '</span>';
}
return response;
}
I think that is enough examples for folks to get the basic idea. Adding the ability to include HTML with a scripted value opens up a number of possibilities. We have just explored a couple of them here, but I am sure that specific requirements will drive many other variations from those willing to give it a try and see what they can do with it.
Here is an Update Set with the modifications, including this new example page. Feedback can be left here in the comments, or in the discussion area where it has been posted out on Share. If you have been able to utilize this feature for anything interesting, a screenshot would definitely be something in which folks would have an interest, so please let us all in on what you were able to accomplish.
