Send the Notification once all RITMs of a Request reach to a specific stage.

[Sara Ardalan]

The requirement is to send a notification to "Opened By" user, as soon as all RITMs of a Request reach to the stage of "Complete" or "Rejected by Approver".

The notification is on "SC_Request" table, with the Event and BRs (both on sc_request table). 

But the BRs doesn't work: 

• on "sc_request" table
• before "update"

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

var RITM = new GlideRecord('sc_req_item');
RITM.addQuery("request", current.request);
RITM.addEncodedQuery("stage!=complete"^ORstage!=rejected_by_approver"); //two particular stages 
RITM.query();
if (!RITM.next()){
gs.eventQueue('nfc.req.closed', current); //this event should trigger the notification. 
}

})(current, previous);

Another idea, was to count the number of RITMs first and the get the query, but this BR, runs as soon as the first RITMs set to "complete/ Reject by Approver" stage, which means it triggers the notification multiple times:

(function executeRule(current, previous /*null when async*/ ) {
    var cnt = 0;
    var chkComplete = 0;
    var chkRejectedBy = 0;
    var grRITM = new GlideRecord('sc_req_item');
    grRITM.addQuery("request", current.request);
    grRITM.query();
    grRITM.getRowCount();
    while (grRITM.next()) {
        if (grRITM.stage == 'complete') {
            chkComplete++;
        }
        if (grRITM.stage == 'rejected_by_approver') {
            chkRejectedBy++;
        }
    }
    cnt = grRITM.getRowCount();
    var total = chkComplete + chkRejectedBy;
    if (total == cnt) {
        gs.eventQueue('nfc.req.closed', grRITM.request); 
    }

I tried also the BR on "sc_req_item" table, but so far, no luck. 

[Uncle Rob]

I haven't had a chance to test it, but here's what I'd do in Flow Designer

In the RITM workflow have a wait for condition that waits for (your preferred stage)

When that happens, it looks up all other RITMS that are peers (children of the same REQ).

- the condition of the lookup is that the other RITMS must be a stage different than (your preferred stage)

If the count of the looked up records is 0, then you know "all ritms under this request are at this stage OR this is theo only ritm under the req).

At that point you can use whatever mechanism you want to notify the Requestor.

[Sara Ardalan]

Hi Robert,

Thanks for your reply.

Unfortunately, adding this solution to the Flow/WF, doesn't work for this customer, as they have a huge number of Flows for their Items, which updating them one by one is not possible. I should find a solution with BR and Event.