sysapproval_approver table - find all records where the "Approval For's" parent is the same

[Bill Wulff]

Can anyone assist me with why this query does not work?

   var requestID = '6f6416e5476c6610872475f4116d43d8';

    //Find all approvals where the parent is the same. 
    var approvalQuery = new GlideRecord('sysapproval_approver');
    approvalQuery.addQuery('sysapproval.parent', requestID);
    approvalQuery.query();
    while(approvalQuery.next())
    {
        gs.info(approvalQuery.sysapproval.sys_id);      
    }

I am looking to query the sysapproval_approver table to find all records where the "Approval For's" parent is the same.

Specifically, I am looking to group together the RITM approval records based on the parent request.

For further context, I am being tasked to consolidate individual RITM approver emails into one email.

My thinking is as follows.

Query sysapproval_approver table and find all records where the approver is the same, and where the parent request is the same.

If there is only one record, trigger event to send "single approval email".

If more than one record trigger event to send "multiple approvals email"

I am still debating if this is even possible within a business rule / script include. Since each record being inserted into the sysapproval_approver table will generate an instance of the rule, which wouldn't be a solution for this problem.

I am thinking that I might have to create a scheduled job to accomplish this, and then mark each record the scheduled job targets, so I don't re-run the approval process again, the next time the job triggers.

Any advice / tips would be most appreciated. Thank you in advance.

[Rohit Singh]

Hi @Bill Wulff ,

Please replace

gs.info(approvalQuery.sysapproval.sys_id);

With

gs.info(approvalQuery.sys_id);

If my response helped then please accept the solution and hit the thumbs up so that it benefits future reader.

Regards,

Rohit

[Bill Wulff]

This did not work.

[Ankur Bawiskar]

@Bill Wulff 

requestID -> holds REQ sysId?

For your requirement here is the background script which you can try

var count = 0;
var gr = new GlideRecord('sysapproval_approver');
gr.addQuery('approver', approverSysId);
gr.addQuery('approval_for.parent', parentSysId);
gr.query();
while (gr.next()) {
    count++;
}

if (count == 1) {
    gs.eventQueue('single_approval_email', gr, gr.sys_id, gr.approver);
} else if (count > 1) {
    gs.eventQueue('multiple_approvals_email', gr, gr.sys_id, gr.approver);
}

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

[Bill Wulff]

Yes, the requestID holds the sys_id of the request.

I was not able to get your script to work.

I don't think the system likes the gr.addQuery('approval_for.parent', parentSysId); query.

I think it has something to do with the "Approval For" field being a reference field of the Task table.