Sel Limit via sys_properties

[Rooma1]

Hi All,

I have a requirement to create a cleanup job on 'sysapproval_approver' table so that it can clean the records pending for more than a year. I want to set the limit to 100 via system property and want to call that property in the script. I do not want to use setLimit method.

Given below is the snippet code:

var gr = new GlideRecord('sysapproval_approver');

gr.addEncodedQuery('sysapproval.number=RI0439973^state=requested');

gr.query();

while (gr.next()) {

    var RITMgr = new GlideRecord('sc_req_item');

    RITMgr.addQuery('sys_id', gr.document_id);

    RITMgr.addQuery('state', '!=', '5');

    RITMgr.query();

    while (RITMgr.next()) {

        RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';

        RITMgr.update();

        RITMgr.stage = 'Request Cancelled';

        RITMgr.state = '5';

       RITMgr.setWorkflow(false);

        RITMgr.update();

        var workflow = new global.Workflow();

        workflow.cancel(RITMgr);

        gs.print('Numbers are '+ RITMgr.number);

    }

[Amit Gujarathi · Accepted solution]

HI @Rooma1 ,
I trust you are doing great.
You can modify the code as follow :

// Get the cleanup limit from the system property
var cleanupLimit = gs.getProperty('your_system_property_name', 100);

// Create a date object representing one year ago
var oneYearAgo = new GlideDateTime();
oneYearAgo.subtractYearsLocalTime(1);

// Create a GlideRecord query on 'sysapproval_approver' table
var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('sysapproval.state', 'requested');
gr.addQuery('sysapproval.sys_created_on', '<=', oneYearAgo);
gr.setLimit(cleanupLimit); // Setting the limit
gr.query();

while (gr.next()) {
    // Your existing logic to process each record
    var RITMgr = new GlideRecord('sc_req_item');
    RITMgr.addQuery('sys_id', gr.document_id);
    RITMgr.addQuery('state', '!=', '5');
    RITMgr.query();

    while (RITMgr.next()) {
        // Your existing logic to update and cancel request items
        RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';
        RITMgr.update();

        RITMgr.stage = 'Request Cancelled';
        RITMgr.state = '5';
        RITMgr.setWorkflow(false);
        RITMgr.update();

        var workflow = new global.Workflow();
        workflow.cancel(RITMgr);
        gs.print('Numbers are ' + RITMgr.number);
    }
}

[Rooma1]

@Amit Gujarathi I am including the script for sc_request as well in the same script to clean up the approval records. The script is setting the approval state to cancel but it is not updating the sc_request record.

var HousekeepingRequests = Class.create();

HousekeepingRequests.prototype = {

    initialize: function() {},

    cleanup: function() {

        // log

        gs.log("Start housekeeping", "RI/RQ Housekeeping");

        var cleanupLimit = gs.getProperty('housekeeping_cleanup.max_approval_record');

        // loop through all sc_req_items

        var gr = new GlideRecord("sysapproval_approver");

        gr.addEncodedQuery("state=requested^source_table=sc_req_item^ORsource_table=sc_request");

        gr.setLimit(cleanupLimit);

        gr.query();

        while (gr.next()) {

            // if it gets here, then housekeeping is required on this item          

            // Set state = 5 (Closed Cancelled) and stage = "Request Cancelled" if it's not already cancelled

            var RITMgr = new GlideRecord("sc_req_item");

            RITMgr.addQuery("sys_id", gr.document_id);

            RITMgr.addQuery("state", "!=", 5);

            RITMgr.query();

           // var RITMApproved = false;

            while (RITMgr.next()) {

                // set all request items for this request to closed cancelled

                var message = gs.getMessage('RITM has been cancelled as the approval was not given within 90 days');

                gs.addInfoMessage(message);

                RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';

                RITMgr.update();

                RITMgr.stage = "Request Cancelled";

                RITMgr.state = "5";

                RITMgr.setWorkflow(false);

                RITMgr.update();

                var workflow = new global.Workflow();

                workflow.cancel(RITMgr);

            }

            // Cancel the approval request

            gr.state = "cancelled";

            gr.setWorkflow(false);

            gr.update();

        }

        //loop through all sc_requests

        var gr2 = new GlideRecord("sysapproval_approver");

        gr2.addEncodedQuery("state=requested^source_table=sc_req_item^ORsource_table=sc_request");

        gr2.setLimit(cleanupLimit);

        gr2.query();

        while (gr2.next()) {

            // if it gets here, then housekeeping is required on this item

         // Set request_state = "closed_cancelled" and stage = closed_incomplete

            var REQgr = new GlideRecord("sc_request");

            REQgr.addQuery("sys_id", gr2.document_id);

            REQgr.addQuery("request_stateNOT INclosed_complete,closed_incomplete,closed_cancelled,closed_rejected,closed_skipped");

            REQgr.query();

            while (REQgr.next()) {

                // set the request to closed cancelled

                var message1 = gs.getMessage('RITM has been cancelled as the approval was not given within 90 days');

                gs.addInfoMessage(message1);

                var work_flow = new global.Workflow();

                work_flow.cancel(REQgr);

                REQgr.request_state = 'closed_cancelled';

                REQgr.approval = 'rejected';

                REQgr.state = 5;

                REQgr.update();

            }

            // Cancel the approval request

            gr2.state = "cancelled";

            gr2.setWorkflow(false);

            gr2.update();

        }

        gs.log("End housekeeping", "RI/RQ Housekeeping");

    },

    type: 'HousekeepingRequests'

};

[Maddysunil]

@Rooma1 

I think you can implement your own counter to keep track of the number of records processed, below is the code:

var cleanupLimit = gs.getProperty('your_system_property_name'); // set to 100

var gr = new GlideRecord('sysapproval_approver');
gr.addEncodedQuery('sysapproval.number=RI0439973^state=requested');
gr.query();

var recordsProcessed = 0; // Counter to keep track of processed records

while (gr.next() && recordsProcessed < cleanupLimit) {
    var RITMgr = new GlideRecord('sc_req_item');
    RITMgr.addQuery('sys_id', gr.document_id);
    RITMgr.addQuery('state', '!=', '5');
    RITMgr.query();

    while (RITMgr.next() && recordsProcessed < cleanupLimit) {
        RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';
        RITMgr.update();

        RITMgr.stage = 'Request Cancelled';
        RITMgr.state = '5';
        RITMgr.setWorkflow(false);
        RITMgr.update();

        var workflow = new global.Workflow();
        workflow.cancel(RITMgr);

        gs.print('Number is ' + RITMgr.number);

        recordsProcessed++; // Increment the counter
    }
}

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Rooma1]

@Maddysunil 

I am including the script for sc_request as well in the same script to clean up the approval records. The script is setting the approval state to cancel but it is not updating the sc_request record.

var HousekeepingRequests = Class.create();

HousekeepingRequests.prototype = {

    initialize: function() {},

    cleanup: function() {

        // log

        gs.log("Start housekeeping", "RI/RQ Housekeeping");

        var cleanupLimit = gs.getProperty('housekeeping_cleanup.max_approval_record');

        // loop through all sc_req_items

        var gr = new GlideRecord("sysapproval_approver");

        gr.addEncodedQuery("state=requested^source_table=sc_req_item^ORsource_table=sc_request");

        gr.setLimit(cleanupLimit);

        gr.query();

        while (gr.next()) {

            // if it gets here, then housekeeping is required on this item          

            // Set state = 5 (Closed Cancelled) and stage = "Request Cancelled" if it's not already cancelled

            var RITMgr = new GlideRecord("sc_req_item");

            RITMgr.addQuery("sys_id", gr.document_id);

            RITMgr.addQuery("state", "!=", 5);

            RITMgr.query();

           // var RITMApproved = false;

            while (RITMgr.next()) {

                // set all request items for this request to closed cancelled

                var message = gs.getMessage('RITM has been cancelled as the approval was not given within 90 days');

                gs.addInfoMessage(message);

                RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';

                RITMgr.update();

                RITMgr.stage = "Request Cancelled";

                RITMgr.state = "5";

                RITMgr.setWorkflow(false);

                RITMgr.update();

                var workflow = new global.Workflow();

                workflow.cancel(RITMgr);

            }

            // Cancel the approval request

            gr.state = "cancelled";

            gr.setWorkflow(false);

            gr.update();

        }

        //loop through all sc_requests

        var gr2 = new GlideRecord("sysapproval_approver");

        gr2.addEncodedQuery("state=requested^source_table=sc_req_item^ORsource_table=sc_request");

        gr2.setLimit(cleanupLimit);

        gr2.query();

        while (gr2.next()) {

            // if it gets here, then housekeeping is required on this item

         // Set request_state = "closed_cancelled" and stage = closed_incomplete

            var REQgr = new GlideRecord("sc_request");

            REQgr.addQuery("sys_id", gr2.document_id);

            REQgr.addQuery("request_stateNOT INclosed_complete,closed_incomplete,closed_cancelled,closed_rejected,closed_skipped");

            REQgr.query();

            while (REQgr.next()) {

                // set the request to closed cancelled

                var message1 = gs.getMessage('RITM has been cancelled as the approval was not given within 90 days');

                gs.addInfoMessage(message1);

                var work_flow = new global.Workflow();

                work_flow.cancel(REQgr);

                REQgr.request_state = 'closed_cancelled';

                REQgr.approval = 'rejected';

                REQgr.state = 5;

                REQgr.update();

            }

            // Cancel the approval request

            gr2.state = "cancelled";

            gr2.setWorkflow(false);

            gr2.update();

        }

        gs.log("End housekeeping", "RI/RQ Housekeeping");

    },

    type: 'HousekeepingRequests'

};