How to close parent record when all child records are completed

[mkdj12]

I'm using the script below to query all associated child work order records by the parent request item record. My goal is to automatically close the request item once all associated records are closed. The problem I'm running into is that the request item is closing as soon as you close one child record. Would anyone have any recommendations on how I can fix this business rule: 

Conditions: Status 'Changes To' Completed. 

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

    // Add your code here
    var wo = new GlideRecord("u_navision_sales_order");
    wo.addQuery("u_request_item", current.u_request_item);
    wo.orderBy('sys_created_on');
    wo.query();
    while (wo.next()) {
        if (wo.u_status != 1 || wo.u_status != 2 || wo.u_status != 3 || wo.u_status != 4 || wo.u_status != 6) {
            var req = new GlideRecord("sc_req_item");
            req.addQuery("number", current.u_request_item);
            req.addQuery('state', 'IN', '1 , 2');
            req.query();
            if (req.next()) {
                req.state = '3';
                req.update();
            }
        }
    }
})(current, previous);

[Ankur Bawiskar]

Hi,

you need to know whether all the tasks are closed or not based on count;

this should be after update business rule on u_navision_sales_order table with condition as state changes to completed

ensure you use proper state value for u_navision_sales_order table; I have used complete

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

    // Add your code here
    var wo = new GlideRecord("u_navision_sales_order");
    wo.addQuery("u_request_item", current.u_request_item);
    wo.query();
    var totalSalesOrder = wo.getRowCount();
    var completedCount = 0;
    while(wo.next()){
      if(wo.state == 3)
       completedCount = completedCount + 1;
    }      
    
    if(completedCount == totalSalesOrder){

    var ritm = current.u_request_item.getRefRecord();
    if(ritm.state == 1 || ritm.state == 3){
    ritm.state = 3;
    ritm.update();
    }

    }
})(current, previous);

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

[mkdj12]

Thanks Ankur,

Unfortuanately the completedCount remains at zero

[Ankur Bawiskar]

Hi,

I have used state=3 for completion; are you also using same state value?

add log inside while to check whether the state == 3 satisfies or not

Regards
Ankur

[Suseela Peddise]

Hi,

Try below:

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

// Add your code here
var wo = new GlideRecord("u_navision_sales_order");
wo.addQuery("u_request_item", current.u_request_item);
wo.addQuery("u_status", '!=', <<status value>>); //replace with completed value
wo.orderBy('sys_created_on');
wo.query();
if (wo.next()) {
//Don't close the parent record as one of the child is not completed .

gs.addInfoMessage('Parent can't be closed because one of the child is in Open');
}

else
{

var req = new GlideRecord("sc_req_item");
req.addQuery("number", current.u_request_item);
req.addQuery('state', 'IN', '1 , 2');
req.query();
if (req.next()) {
req.state = '3';
req.update();
}
}
})(current, previous);

If I have answered your question, please mark my response as correct and/or helpful.

Thanks,

Suseela P.