How to sum of duration field

lucky24 · ‎05-09-2022

Hi Team,

We have have custom choice field called substate which has different value.I have defined metrics to run on this field. This is for Incident table

the substate value is caller, vendor, customer.

so I have to calculate time for each value means how much time ticket was in particular substate choice value.

but when I changing value to caller and again changed to caller so it's is creating multiple record in metric instance for caller value .

So I have to sum of all duration and have to populate in incident custom string field.

for that I have written business rule but I faild to get result

please help me here. I am stuck here from last 3 days .

Please can you help me here with some script .?

Thanks

Mahesh23 · ‎05-09-2022

Hi,

Try below code

var dur ='';
var gr - new GlideRecord('metric_instance');
gr.addQuery('id',current.getValue('id');
gr.addQuery('defination','REPLACE_SYS_ID');
gr.addQuery('value','Caller');
gr.query();
while(gr.next()){
    dur = new DurationCalculator();
    dur =gr.duration.dateNumericValue();

}
    var tab = new GlideRecord('table_name');
    tab.get(current.getValue('id');
    tab.u_substate.setDateNumericValue(dur);
    tab.update();

Hope this can help you!

ThorS · 2 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();

}

})();