Field value is not populating as expected in Case Table

[tulasi8]

Hi Community,

We have a requirement involving the Order table, where a field named “Total AMC Hours” stores the total number of AMC hours available for an order. We have introduced a new field on the Order record called “Remaining AMC Hours.”

Each Order is associated with an Asset, and each Asset can have multiple Cases linked to it. On the Case form, users log effort in the Time Worked related list.

The requirement is:

• When a Time Worked entry is added on a Case,
• And the Case state changes to Closed,
• Then the Remaining AMC Hours on the associated Order should be updated as:

Remaining AMC = Total AMC Hours – Total Time Worked

We implemented a Business Rule on the Case table (After Insert/Update, condition: state changes to Closed).
Example:

• Total AMC Hours on the Order = 20
• Time Worked entered before closure = 5 hours
• Remaining AMC Hours becomes 20 – 5 = 15

This works correctly on initial closure.   @Ankur Bawiskar 

Issue:
If a user revisits the Case after it is already closed and updates the Time Worked value (e.g., from 5 to 10 hours), the Remaining AMC Hours on the Order does not automatically recalculate to reflect the updated time (expected: 20 – 10 = 10).

We need guidance on how to modify the existing Business Rule script so that any update to Time Worked—even after the Case is closed—automatically recalculates and updates the Remaining AMC Hours accordingly.

Can someone please assist with the correct approach?

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

    var asset = current.asset;

    if (!asset) return;

    var orderNumber = current.asset.u_order;

    var orderGR = new GlideRecord('csm_order');

    orderGR.addQuery('sys_id', orderNumber);

    orderGR.query();

    if (orderGR.next()) {

        var totalHours = parseInt(orderGR.u_amc_hr || "0");

        var csrHours = 0;

        var timeGR = new GlideRecord('task_time_worked');

        timeGR.addQuery('task.sys_id', current.sys_id);

        timeGR.query();

        while (timeGR.next()) {

            var duration = timeGR.time_worked.getGlideObject().getNumericValue();

            duration = duration / 1000;

            csrHours += duration / 3600;

        }

        var remHours = orderGR.u_remaining_no_of_amc_hours;

        if (remHours != '') {

            var remAmcHours = remHours - csrHours;

            var remainingAmc = parseInt(remAmcHours);

            orderGR.u_remaining_no_of_amc_hours = remainingAmc.toString();

            orderGR.update();

        } else {

            var remaining = totalHours - csrHours;

            var remaininghours = parseInt(remaining);

            //var hours = remaininghours.toString();

            orderGR.u_remaining_no_of_amc_hours = remaininghours.toString();

            orderGR.update();

        }

    }

})(current, previous);


Regards,

Tulasi.

[Nithin_G]

You may need to implement a Business Rule on the task_time_worked table. The rule should trigger whenever the time worked value is updated and should compare the previous and current values. Based on the difference, the corresponding hours should be added to or subtracted from the Remaining AMC Hours in the csm_order table.

For example, if the initial time worked entered is 5 hours and the total AMC hours on the order are 20, then upon case closure the remaining hours become 15 (20 − 5). If the time worked is later updated from 5 hours to 10 hours, the difference is 5 hours, so the remaining AMC hours should be recalculated as 15 − 5 = 10.

[tulasi8]

@Nithin_G we had already written business rule on task time worked , as after insert , update and time worked value changes with this script , but it's not working

Script :

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

    var taskSysId = current.getValue('task');

    var caseRecord = new GlideRecord('sn_customerservice_case');

    if (caseRecord.get(taskSysId)) {

        var asset = new GlideRecord('u_engineering_assets');

        if (asset.get(caseRecord.asset)) {

            var order = new GlideRecord('csm_order');

            if (order.get(asset.u_order)) {

                order.u_remaining_no_of_amc_hours = current.time_worked;

                order.update();

            }

        }

    }

})(current, previous);

[Nithin_G]

Hi @tulasi8 , 
I believe the issue may be with current.time_worked, since Time Worked is a Duration type field. I’m also not certain about the data type of the AMC Hours field. You could try updating your code as shown below and see if that resolves the issue.

 

(function executeRule(current, previous /*null when async*/ ) {
    var taskSysId = current.getValue('task');
    var caseRecord = new GlideRecord('sn_customerservice_case');
    if (caseRecord.get(taskSysId)) {
        var asset = new GlideRecord('u_engineering_assets');
        if (asset.get(caseRecord.asset)) {
            var order = new GlideRecord('csm_order');
            if (order.get(asset.u_order)) {
                var csrHours = 0;   
 var currentDuration  = current.time_worked.getGlideObject().getNumericValue();
    var previousDuration = previous.time_worked.getGlideObject().getNumericValue();

    // Calculate the difference (positive or negative)
    var duration = currentDuration - previousDuration;
   duration = duration / 1000;
            csrHours += duration / 3600;
  var remHours = orderGR.u_remaining_no_of_amc_hours;
   var remAmcHours = remHours - csrHours;
            var remainingAmc = parseInt(remAmcHours);
  orderGR.u_remaining_no_of_amc_hours = remainingAmc.toString();
            orderGR.update();
            }
        }
    }
})(current, previous);

[tulasi8]

@Nithin_G it's type is glide_duration