Calculate business days in scheduled jobs

[Madhuri8]

Hi Community,

Need to update the state field of a custom table based on the state value (on approval table). i.e..,

Once the request is created on custom table an approval notification will be sent to manager of that user, in 6 business days this should be approved if not need to change the record state to cancelled on custom table. I am trying this using scheduled jobs but didn't find a solution. Hope someone would help me.

Thank you in Advance!!

[P-Rudenko-SN]

Hello, I assume the approval is created in 'sysapproval_approver' table. Here is a job you may use:

var grApp = new GlideRecord('sysapproval_approver');
grApp.addEncodedQuery('state!=approved^sys_created_on<javascript:gs.beginningOfLast8Days()'); // check for all records with the state not 'Approved' and created before last 8 calendar days (6 business days)
grApp.query();
while(grApp.next()) {
	var grCr = new GlideRecord('custom_table');
	grCr.addQuery('sys_id', grApp.sysapproval);  //the field 'Approval for'
	grCr.query();
	if(grCr.next()) {
		grCr.u_state = 'cancelled'; // set state to 'cancelled' on custom table	
        grCr.update();
    }
}

[Samama Butt · Accepted solution]

Hi Madhuri,

Please follow the below steps:

1. Create a Schedule(here you can exclude holidays as well) like below:

2. Create a Scheduled Job that will execute daily on your time choosing.

3. Paste the script below in the run script section.

var grAprvl = new GlideRecord('sysapproval_approver');

grAprvl.addEncodedQuery('state!=approved^sys_created_on'); // check for all records with the state not 'Approved'
grAprvl.query();
while (grApp.next()) {

    var grCustom = new GlideRecord('custom_table'); // find the appropriate record

    grCustom.addQuery('approval_field', grAprvl.sysapproval); //the field 'Approval for'

    grCustom.query();

    if (grCustom.next()) {

        var sched = new GlideSchedule('090eecae0a0a0b260077e1dfa71da828');

        var created = grCustom.sys_created_on;

        var gdt = new GlideDateTime();

        var gtime1 = new GlideDateTime(created); //passing created on date to the GlideDateTime object

        gtime1.addDays(6); //adding 6 days to the created on date

        var dur = GlideDateTime.subtract(gdt, gtime1); //the difference between todays date and date after 6 days record was created

        var result = dur.getNumericValue();

        if (sched.isInSchedule(gdt) /*checking if todays date is in schedule*/ && result < 0 /*checking if 6 days has passed*/ ) {

            grCustom.u_state = 'cancelled'; // set state to 'cancelled' on custom table    

            grCustom.update();
        }
    }
}

Please mark correct if find it helpful.

[Samama Butt]

@Madhuri If your question is answered, kindly mark the comment as a correct answer so that the question is moved to the solved list.

[P-Rudenko-SN]

@Madhuri have you had a chance to create a scheduled job with a script I've provided? Please let me know if it works.