Automate state changes from post release validation to complete after 14 working days(story table).

[pampapathi1]

Hi All,

I have below requirement,

"To achieve the automation of transitioning a story's state from "Post Release Validation" to "Complete" exactly 14 working days after it enters the "Post Release Validation" state using a Scheduled Job in ServiceNow while ensuring the job does not run on weekends"

I have tried to use below script but weekends also scheduled job running , could you please suggest me what's wrong in below script

Thanks in advance!!

Regards,

Pampa

[Bert_c1]

Hi Pampa,

Try posting your script using Insert/Edit code sample control here. The folks here can easily test without typing in all of your code.

[Community Alums · Accepted solution]

Hi @pampapathi1 ,

The issue in the script is in the logic within the getBusinessDays function and how it handles weekends.

In the context of JavaScript Date.getDay(), the days of the week are:
Sunday: 0
Monday: 1
Tuesday: 2
Wednesday: 3
Thursday: 4
Friday: 5
Saturday: 6

Source for the above- https://www.w3schools.com/jsref/jsref_getday.asp

Please find the updated script-

// Query records in the "Post Release Validation" state in rm_story table
var gr = new GlideRecord('rm_story');
gr.addQuery('state', -11); // Query for records in "Post Release Validation" state
gr.query();

// Define the threshold for automatic transition (14 working days)
var thresholdDays = 14;
gs.log('Story submitted days: ' + thresholdDays);

// Function to calculate business days between two dates, excluding weekends
function getBusinessDays(startDate, endDate) {
    var days = 0;
    var current = new GlideDateTime(startDate);
    var end = new GlideDateTime(endDate);
    
    while (current.before(end) || current.equals(end)) {
        var dayOfWeek = current.getDayOfWeek();
        if (dayOfWeek != 6 && dayOfWeek != 0) { // Check if it's not Saturday (6) or Sunday (0)
            days++;
        }
        current.addDays(1);
    }
    return days;
}

// Loop through each record found
while (gr.next()) {
    // Calculate business days since the state was set to 'Post Release Validation'
    var startDateTime = gr.sys_created_on; // Assuming sys_created_on is the relevant date/time field
    gs.log('Post release validation: ' + startDateTime);

    var now = new GlideDateTime();
    gs.log('Now: ' + now);
    
    var elapsedBusinessDays = getBusinessDays(startDateTime, now); // Calculate business days between startDateTime and now
    gs.log('Calculating business days: ' + elapsedBusinessDays);

    // Check if the record has been in "Post Release Validation" state for 14 working days
    if (elapsedBusinessDays >= thresholdDays) {
        gs.log('Story ' + gr.number + ' has exceeded 14 working days in post release validation state.');
        gr.state = 3; // Assuming 'Complete' is represented by state 3
        gr.update();
        gs.log('State updated to Complete for rm_story record: ' + gr.number);
    }
}

Changed dayOfWeek != 6 && dayOfWeek != 7(In your script)

to dayOfWeek != 6 && dayOfWeek != 0 (In the updated script)

If my response has resolved your query, please consider giving it a thumbs up ‌‌ and marking it as the correct answer‌‌!

Thanks & Regards,

Sanjay Kumar

[pampapathi1]

Hi @Community Alums ,

Sure, I will post the script here with the "insert/edit code sample" control.

Thank you very much for your help!!

The script works as expected after I changed it from 7 (weekend) to 0.
Regards,
Pampa

[Community Alums]

Thats great 😊