Hi, Need to update the order of the sysapproval table to associated sysapproval_group table order

rajeshKongamudi
Tera Contributor

‎02-21-2025 03:25 AM

Hi Community, In the script below, we are working with the sysapproval_approver table to update the order field. The goal is to transfer the updated order value from the sysapproval_approver table to the corresponding order field in the sysapproval_group table. This ensures that the correct order value is used for notifications and for triggering approval processes. please help us to achieve this, Thanks in advance.
 
(function executeRule(current, previous /*null when async*/) {
    // Query the sc_req_item to get the RITM based on sysapproval (assuming sysapproval is related to the RITM)
    var gm = new GlideRecord('sc_req_item');
    gm.addEncodedQuery('cat_item=3c1f0743879b9e90dc8b43730cbb3598^sys_id=' + current.sysapproval);
    gm.query();
   
    if (gm.next()) {
        var ritm_id = gm.sys_id; // RITM ID found
        gs.log("The RITM ID is: " + ritm_id);
       
        // Now, query sysapproval_group to get the group order
        var appr = new GlideRecord('sysapproval_approver');
        appr.addEncodedQuery('group.assignment_group=e8e71a671b536110d06b117a3b4bcbd1^ORgroup.assignment_group=09b80f5187ef9a10222aebd90cbb3559^sysapproval=' + ritm_id);
        appr.orderByDesc('order');
        appr.query();
        var count = 0;
       
        // If we find the group in sysapproval_approver, increment the order
        if (appr.next()) {
            var app_order = appr.order; // Get the current approval order
            gs.log("The current approval order is: " + app_order);

 

            count = app_order + 1; // Increment the order
            gs.log("The incremented approval order is: " + count);
           
            // Update the approval group order in sysapproval_approver table
            appr.order = count;
           // Save the updated order in sysapproval_approver
            gs.log("The updated approval group order is: " + count);

 

            // Now, find and update the sysapproval_group table using the sysapproval field from the appr record
            var approvalGroup = new GlideRecord('sysapproval_group');
            if (approvalGroup.get(appr.sysapproval)) {
                approvalGroup.order = count;  // Update the order field in sysapproval_group
                approvalGroup.update();  // Save the updated order in sysapproval_group
                gs.log("The updated sysapproval_group order is: " + count);
            } else {
                gs.log("Sysapproval group record not found for the specified RITM.");
            }

 

            // Trigger an event if needed
            gs.eventQueue('test.sample', current, current.group.assignment_group);
        }
        else {
            gs.log("No matching approval records found for the specified RITM.");
        }
    } else {
        gs.log("No RITM found for the specified sysapproval.");
    }

 

})(current, previous);
0 Helpfuls
  • 450 Views
6 REPLIES 6

Debasis Pati
Tera Guru

‎02-21-2025 03:30 AM

Hello @rajeshKongamudi ,

Can you please try the below script

(function executeRule(current, previous /*null when async*/) {
// Query the sc_req_item to get the RITM based on sysapproval
var ritm = new GlideRecord('sc_req_item');
if (ritm.get(current.sysapproval)) { // Check if RITM exists
var ritm_id = ritm.sys_id.toString();
gs.log("The RITM ID is: " + ritm_id);

// Query sysapproval_approver table to get the highest order
var appr = new GlideRecord('sysapproval_approver');
appr.addEncodedQuery('sysapproval=' + ritm_id);
appr.orderByDesc('order');
appr.query();

var highestOrder = 0;

while (appr.next()) {
if (appr.order > highestOrder) {
highestOrder = appr.order;
}
}
highestOrder += 1; // Increment the order
gs.log("The incremented approval order is: " + highestOrder);

// Update the current approval record with the new order
current.order = highestOrder;
current.update();
gs.log("Updated sysapproval_approver order to: " + highestOrder);

// Now update the sysapproval_group table
var approvalGroup = new GlideRecord('sysapproval_group');
approvalGroup.addQuery('sysapproval', ritm_id);
approvalGroup.query();

if (approvalGroup.next()) {
approvalGroup.order = highestOrder; // Sync order
approvalGroup.update();
gs.log("Updated sysapproval_group order to: " + highestOrder);
} else {
gs.log("No sysapproval_group found for RITM ID: " + ritm_id);
}

// Optional: Trigger an event for notifications
gs.eventQueue('test.sample', current, current.group.assignment_group);
} else {
gs.log("No RITM found for sysapproval ID: " + current.sysapproval);
}

})(current, previous);

If this helps you kindly mark it as helpful/correct.

Regards,
Debasis

0 Helpfuls

sunil maddheshi
Tera Guru

‎02-21-2025 03:30 AM

@rajeshKongamudi 

You are applying the wrong query itseems, please try with below updated code:

(function executeRule(current, previous /*null when async*/) {
    gs.log("Approval order update script started.");

    // Step 1: Fetch the RITM (sc_req_item) using sysapproval
    var gm = new GlideRecord('sc_req_item');
    gm.addQuery('sys_id', current.sysapproval); // Fixing incorrect query
    gm.query();

    if (gm.next()) {
        var ritm_id = gm.sys_id.toString(); // Get the RITM ID
        gs.log("The RITM ID is: " + ritm_id);

        // Step 2: Get the latest approval order from sysapproval_approver
        var appr = new GlideRecord('sysapproval_approver');
        appr.addQuery('sysapproval', ritm_id);
        appr.orderByDesc('order'); // Get the highest approval order
        appr.query();

        if (appr.next()) {
            var app_order = parseInt(appr.order, 10) || 0; // Ensure it's a number
            gs.log("The current approval order is: " + app_order);

            var newOrder = app_order + 1; // Increment the order
            gs.log("The incremented approval order is: " + newOrder);

            // Step 3: Update sysapproval_approver order
            appr.order = newOrder;
            appr.update();
            gs.log("Updated approval group order in sysapproval_approver: " + newOrder);

            // Step 4: Find and update sysapproval_group order
            var approvalGroup = new GlideRecord('sysapproval_group');
            approvalGroup.addQuery('sysapproval', ritm_id);
            approvalGroup.query();

            while (approvalGroup.next()) {
                approvalGroup.order = newOrder;
                approvalGroup.update();
                gs.log("Updated sysapproval_group order to: " + newOrder);
            }

            // Step 5: Trigger event if needed
            gs.eventQueue('test.sample', current, current.group.assignment_group);
        } else {
            gs.log("No matching approval records found for the specified RITM.");
        }
    } else {
        gs.log("No RITM found for the specified sysapproval.");
    }

    gs.log("Approval order update script completed.");
})(current, previous);

Please mark correct/helpful if this helps you, thanks

0 Helpfuls

Ankur Bawiskar
Tera Patron
Tera Patron

‎02-21-2025 03:39 AM

@rajeshKongamudi 

try this

(function executeRule(current, previous /*null when async*/) {
    // Query the sc_req_item to get the RITM based on sysapproval (assuming sysapproval is related to the RITM)
    var gm = new GlideRecord('sc_req_item');
    gm.addEncodedQuery('cat_item=3c1f0743879b9e90dc8b43730cbb3598^sys_id=' + current.sysapproval);
    gm.query();
   
    if (gm.next()) {
        var ritm_id = gm.sys_id; // RITM ID found
        gs.info("The RITM ID is: " + ritm_id);
       
        // Now, query sysapproval_approver to get the current order
        var appr = new GlideRecord('sysapproval_approver');
        appr.addEncodedQuery('sysapproval=' + ritm_id);
        appr.orderByDesc('order');
        appr.query();
       
        if (appr.next()) {
            var app_order = appr.order; // Get the current approval order
            gs.info("The current approval order is: " + app_order);

            var count = app_order + 1; // Increment the order
            gs.info("The incremented approval order is: " + count);
           
            // Update the approval group order in sysapproval_approver table
            appr.order = count;
            appr.update(); // Save the updated order in sysapproval_approver
            gs.info("The updated approval group order is: " + count);

            // Now, find and update the sysapproval_group table using the sysapproval field from the appr record
            var approvalGroup = new GlideRecord('sysapproval_group');
            if (approvalGroup.get(appr.sysapproval)) {
                approvalGroup.order = count;  // Update the order field in sysapproval_group
                approvalGroup.update();  // Save the updated order in sysapproval_group
                gs.info("The updated sysapproval_group order is: " + count);
            } else {
                gs.info("Sysapproval group record not found for the specified RITM.");
            }

            // Trigger an event if needed
            gs.eventQueue('test.sample', current, current.group.assignment_group);
        } else {
            gs.info("No matching approval records found for the specified RITM.");
        }
    } else {
        gs.log("No RITM found for the specified sysapproval.");
    }
})(current, previous);

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader
0 Helpfuls

Ankur Bawiskar
Tera Patron
Tera Patron
In response to Ankur Bawiskar

‎02-23-2025 04:09 AM

@rajeshKongamudi 

Hope you are doing good.

Did my reply answer your question?

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
✨ Certified Technical Architect  ||  ✨ 9x ServiceNow MVP  ||  ✨ ServiceNow Community Leader
0 Helpfuls