how to SUM of multiple duration field?

lucky24 · ‎05-05-2022

Hi Team,

In metrics definition I am getting multiple record when I am changing same value in multiple time (changing value awaiting caller again and again)

So now I want to sum these duration and have to store in custom field of incident,

I am trying with below code but not get success

var duration = new GlideDuration(0);
var mi = new GlideRecord("metric_instance");
mi.addQuery("id", current.sys_id);
mi.addQuery("definition", "fc47ec1d2f1341105d0d56e62799b64e");
mi.addQuery("value", "Awaiting Caller");
mi.query();
mi.getRowCount();
// while(mi.next()){
var dur = new GlideDuration();
dur.setValue(mi.getValue('duration'));
duration = duration.add(dur);

// }
current.u_pause_duration= duration.getDisplayValue();
current.u_total_hour=duration;
gs.addInfoMessage(duration.getDisplayValue());
gs.addInfoMessage(mi.getRowCount());
current.update();

So how can we achieve it please help me here?

please help me here.

Mahesh23 · ‎05-05-2022

Hi,

Try below code

var dur = new DurationCalculator();

dur =mi.duration.dateNumericValue();

mi.duration.setDateNumericValue(dur);

Hope this can help you!

ThorS · 3 weeks ago

I was able to run a background script to perform the summary and provide an output; additionally the script will update your base records with the duration for each value in the field being durat(ed?).

You should be able to use this for your business rule:

(function() {

// === Configuration ===

var metricDefSysId = '1234'; // Replace with your metric_definition sys_id

var baseTableName = 'x_custombasetable'; // your base table name (lowercase)

// Helper function to convert duration string (e.g., '1970-01-12 00:05:59') to seconds

function durationStringToSeconds(durationStr) {

if (!durationStr)

return 0;

var gdt = new GlideDateTime(durationStr);

var epoch = new GlideDateTime('1970-01-01 00:00:00');

var diffMs = gdt.getNumericValue() - epoch.getNumericValue();

return Math.floor(diffMs / 1000);

}

// Maps to hold data

var aggregatedData = {}; // { recordSysId: { _record_number: string, state1: durationDays, ... } }

var uniqueStates = {}; // { stateName: true }

var unmatchedIds = []; // to log sys_ids not found in base table

// Query metric_instance for the metric definition

var gr = new GlideRecord('metric_instance');

gr.addQuery('definition', metricDefSysId);

gr.query();

while (gr.next()) {

var recordSysId = gr.getValue('id'); // Assuming this is sys_id of base record

if (!recordSysId) {

gs.info('metric_instance ' + gr.getValue('sys_id') + ' missing id field');

continue;

}

// Parse duration string to seconds

var rawDuration = gr.getValue('duration');

var durationSeconds = durationStringToSeconds(rawDuration);

// Convert seconds to days (float)

var durationDays = durationSeconds / 86400;

// Initialize aggregation object if needed

if (!aggregatedData[recordSysId]) {

aggregatedData[recordSysId] = {

_record_number: null

};

// Get base record by sys_id

var recGr = new GlideRecord(baseTableName);

if (recGr.get(recordSysId)) {

aggregatedData[recordSysId]._record_number = recGr.getValue('number') || 'UNKNOWN_NUMBER';

} else {

aggregatedData[recordSysId]._record_number = 'UNKNOWN';

unmatchedIds.push(recordSysId);

}

// Use 'value' field for the state

var stateValue = gr.getValue('value') || 'UNKNOWN_STATE';

uniqueStates[stateValue] = true;

if (!aggregatedData[recordSysId][stateValue]) {

aggregatedData[recordSysId][stateValue] = 0;

}

aggregatedData[recordSysId][stateValue] += durationDays;

}

// Build CSV header

var stateList = Object.keys(uniqueStates).sort();

var csvHeader = ['Record Number'].concat(stateList.map(function(s){ return '"' + s + '"'; })).join(',');

var csvRows = [];

csvRows.push(csvHeader);

for (var recId in aggregatedData) {

if (!aggregatedData.hasOwnProperty(recId)) continue;

var recData = aggregatedData[recId];

var row = [];

row.push('"' + (recData._record_number || '') + '"');

for (var i = 0; i < stateList.length; i++) {

var st = stateList[i];

var dur = recData[st] || 0;

// Round to 2 decimals

dur = Math.round(dur * 100) / 100;

row.push(dur);

}

csvRows.push(row.join(','));

}

var csvOutput = csvRows.join('\n');

gs.info('=== Duration (in days) per State per Custom Base Record ===');

gs.info(csvOutput);

if (unmatchedIds.length > 0) {

gs.info('=== Unmatched sys_ids (not found in base table) ===');

unmatchedIds.forEach(function(id) {

gs.info(id);

});

}

// === Map state names to base record duration field names ===

// Adjust these mappings to your actual state names and corresponding field names on x_custombasetable

var stateToFieldMap = {

"Work in Progress": "duration_work_in_progress",

"Complete": "duration_complete"

// Add all your state-to-field mappings here

};

// Now update each base record with summed durations

for (var recSysId in aggregatedData) {

if (!aggregatedData.hasOwnProperty(recSysId)) continue;

var recData = aggregatedData[recSysId];

// Load the base record

var recGr = new GlideRecord(baseTableName);

if (recGr.get(recSysId)) {

// For each state, set the corresponding duration field

for (var stateName in stateToFieldMap) {

if (!stateToFieldMap.hasOwnProperty(stateName)) continue;

var fieldName = stateToFieldMap[stateName];

var durationDays = recData[stateName] || 0;

// Update the field with rounded duration (days)

recGr.setValue(fieldName, Math.round(durationDays * 100) / 100);

}

recGr.update();

}

})();