Calculation using business rule

akin9 · ‎09-11-2024

Hello Experts,

We need to calculate the incident state how much minutes "In progress" and store in "u_state_calc" field

for below code is working fine.

but whenever any other field is updated its capturing that time duration instead of WIP.

Requirement

1.How much minutes state was inprogress.

After - insert

Condition - stage changesfrom WIP

Field name - "u_state_calc"

Field type - "Integer"

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

if (previous.stage == '2' && current.stage != '2') {

var wipStart = previous.sys_updated_on;

var wipEnd = new GlideDateTime();

var durationMillis = wipEnd.getNumericValue() - new GlideDateTime(wipStart).getNumericValue();

var durationMins = Math.floor(durationMillis / (1000 * 60));

current.u_state_calc = durationMins;

}

})(current, previous);

Please support!

Rohit99 · ‎09-12-2024

Hi @akin9,

Try with following BR.

Type : After -> Update
condition : state -> changes from -> in progess

code :

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

var wipStateCode = '2';

var sum = 0;

var startAuditGR = new GlideRecord('sys_audit');

startAuditGR.addQuery('fieldname', 'state');

startAuditGR.addQuery('documentkey',current.sys_id);

startAuditGR.addQuery('newvalue', wipStateCode);

startAuditGR.orderBy('sys_created_on');

startAuditGR.query();

while (startAuditGR.next()) {

var startTime = new GlideDateTime(startAuditGR.sys_created_on);

var endAuditGR = new GlideRecord('sys_audit');

endAuditGR.addQuery('fieldname', 'state');

endAuditGR.addQuery('documentkey',current.sys_id);

endAuditGR.addQuery('sys_created_on', '>', startTime);

endAuditGR.addQuery('oldvalue', wipStateCode);

endAuditGR.orderBy('sys_created_on');

endAuditGR.query();

gs.print(endAuditGR.getRowCount());

if (endAuditGR.next()) {

var endTime = new GlideDateTime(endAuditGR.sys_created_on);

gs.print(endTime);

var durationMillis = endTime.getNumericValue() - startTime.getNumericValue();

gs.print(durationMillis);

if (!isNaN(durationMillis) && durationMillis >= 0) {

var durationMins = Math.floor(durationMillis / (1000 * 60));

sum = sum + durationMins;

gs.info("hello +"+ durationMins);

gs.info("test "+sum);

}

var incidentGR = new GlideRecord('incident');

if (incidentGR.get(current.sys_id))

{

incidentGR.u_state_calc = sum;

incidentGR.update();

gs.addInfoMessage(sum);

}

})(current, previous);

on incident form store field type is integer.

Please mark my response as correct and helpful if it helped solved your question.

Thanks,

Rohit Suryawanshi

View solution in original post

Sid_Takali · ‎09-11-2024

Hi @akin9

Record the start time when the state is set to "In Progress".
Calculate the duration only when transitioning out of the "In Progress" state.

try below script

(function executeRule(current, previous /*null when async*/) {
    if (current.operation() == 'update') {
        // state has changed from "In Progress" (2) to other state
        if (previous.stage == '2' && current.stage != '2') {
            // fetch the start time from the previous record
            var wipStartTime = new GlideDateTime(previous.u_wip_start_time);
            var wipEndTime = new GlideDateTime();

            // Calculate the duration in milliseconds
            var durationMillis = wipEndTime.getNumericValue() - wipStartTime.getNumericValue();
            var durationMins = Math.floor(durationMillis / (1000 * 60));

            current.u_state_calc = durationMins;
        } 
        // Check if the state has changed to "In Progress" (2)
        else if (previous.stage != '2' && current.stage == '2') {
            // Record the start time when entering "In Progress"
            current.u_wip_start_time = new GlideDateTime().getValue();
        }
    }
})(current, previous);

akin9 · ‎09-11-2024

Hi @Sid_Takali

Thank you for the Quick reply i tried the above script but no luck.

I tried with 3 minutes out from inprogress but result "u_state_calc = 28,768,656".

We are not created this fied - "u_wip_start_time".

we have "u_statrt" field its capturing WIP start minutes from the sys_created_on using seperate BR.

Can we use this or any other approach .

Rohit99 · ‎09-11-2024

Hi @akin9,

You may follow the following thread.
https://www.servicenow.com/community/developer-forum/calculating-how-much-time-elapsed-on-each-state...

Please mark my response as correct and helpful if it helped solved your question.

Thanks,

Rohit Suryawanshi

akin9 · ‎09-11-2024

Hi @Rohit99

Thank you for the reply.

I Saw the solution using creating two custom fields.

We have created two custom fields.( both are Integer type to store minutes)

u_statrt - (to store Only WIP start minutes from the incident created.)

u_state_calc - (to store Only how long WIP minutes from the incident changed to WIP.)

Do you have any approach to this.Thank you!