How to calculate sum of hours for Time Work table and set the value in associated ritm/INC

[P M Kamer Taj]

How to calculate sum of hours for Time Work table and set the value in associated RITM/INC time worked timer.

[P M Kamer Taj · Accepted solution]

1.Create BR After Insert,update,delete on Time_card table.

2.Script:

(function executeRule(current, previous /*null when async*/ ) {

    // Add your code here

    //ensure time card is linked to a task

    if (!current.task) {

        return;

    }

    var ritm = new GlideRecord('sc_req_item');

    if (ritm.get(current.task)) { //check if ritm exist

        var totalTimeWorked = 0;

        var gr = new GlideAggregate('time_card');

        gr.groupBy('task');

        gr.addAggregate('SUM', 'total');

        gr.addQuery("task", ritm.sys_id)

        gr.query();

        while (gr.next()) {

            totalTimeWorked = gr.getAggregate('SUM', 'total');

        }

        //update the totall time worked -convert to milli seconds

        totalTimeWorked = parseInt(totalTimeWorked * 3600000);

        //fetch duration

        var dur = new GlideDuration(totalTimeWorked);

        ritm.time_worked = dur.getDurationValue();

        ritm.update();

    }

    var inc = new GlideRecord('incident');

    if (inc.get(current.task)) { //check if ritm exist

        var totalTimeWorked1 = 0;

        var gr1 = new GlideAggregate('time_card');

        gr1.groupBy('task');

        gr1.addAggregate('SUM', 'total');

        gr1.addQuery("task", inc.sys_id)

        gr1.query();

        while (gr1.next()) {

            totalTimeWorked1 = gr1.getAggregate('SUM', 'total');

        }

        //update the totall time worked -convert to milli seconds

        totalTimeWorked1 = parseInt(totalTimeWorked1 * 3600000);

        //fetch duration

        var dur1 = new GlideDuration(totalTimeWorked1);

        inc.time_worked = dur1.getDurationValue();

        inc.update();

    }

})(current, previous);

3.set the value of  the timer property to false

glide.ui.timer.started

[P M Kamer Taj]

To execute the fix script to populate summation of Total in already existing RITM time worked field.

var ritm = new GlideRecord('sc_req_item');   //change the table name for INC if you need for Incident

ritm.orderByDesc('sys_updated_on');

ritm.query();

//gs.log("this is the list"+ritm.getRowCount());

while (ritm.next()) {

    //gs.log("inside while"+ritm.number);

    var totalTimeWorked = 0;

    var gr = new GlideAggregate('time_card');

    gr.groupBy('task');

    gr.addAggregate('SUM', 'total');

    gr.addQuery("task", ritm.sys_id);

    gr.query();

    while (gr.next()) {

        totalTimeWorked = gr.getAggregate('SUM', 'total');

        //update the totall time worked -convert to milli seconds

        totalTimeWorked = parseInt(totalTimeWorked * 3600000);

        //fetch duration

        var dur = new GlideDuration(totalTimeWorked);

        ritm.time_worked = dur.getDurationValue();

        ritm.update();

    }

}