Hello All,
We have requirement where we want to calculate total sum of hours of Time Work table.
As per the attached image we need to calculate sum of hours by each Task. Here if we insert a record we need to enter hours and each task can have multiple time work records.
For this I have created After Insert Business Rule Time Work table
Any and all help greatly appreciated!
Thank you
Hello @Sonu Parab
1) Create a new duration field on [task] table name u_sum_of_time_worked
2) Create after insert, update, delete Business Rule on [task_time_worked]
Advanced Script:
(function executeRule(current, previous /*null when async*/) {
// Initialize the total duration in seconds
var totalSeconds = 0;
// Create a GlideRecord instance for the 'task_time_worked' table
var timeWorkedGR = new GlideRecord('task_time_worked');
// Query all 'time_worked' entries related to the current task
timeWorkedGR.addQuery('task', current.task);
timeWorkedGR.query();
// Iterate through all the found entries and sum up the duration
while (timeWorkedGR.next()) {
var duration = timeWorkedGR.time_worked.getDisplayValue(); // Get the duration as text
// Convert the duration into hours and minutes
var parts = duration.split(' ');
for (var i = 0; i < parts.length; i += 2) {
var value = parseInt(parts[i], 10);
if (!isNaN(value)) {
if (parts[i + 1].includes('Hour')) {
totalSeconds += value * 3600; // Convert hours to seconds
} else if (parts[i + 1].includes('Minute')) {
totalSeconds += value * 60; // Convert minutes to seconds
}
}
}
}
// Update the task entry in the 'task' table
var taskGR = new GlideRecord('task');
if (taskGR.get(current.task)) {
var totalDuration = new GlideDuration(totalSeconds * 1000);
taskGR.u_sum_of_time_worked = totalDuration; // Set the total duration
taskGR.update(); // Save the changes
}
})(current, previous);
3) Update existing Records via Background Script:
var r = new GlideRecord('table_name');
r.query();
while (r.next()) {
r.setForceUpdate(true);
r.autoSysFields(false);
r.setWorkflow(false);
r.update();
}
Result:
