Flow Designer

[DianaBTX]

i am creating a flow looking up all the sysapproval_approver records that have not been approved > 40 days for requests.  

i want to send one email to each unique approver rather than sending the email for every record meeting the criteria.   

i dont want John Doe to receive 10 emails if he has 10 pending approvals.  i only want him to receive 1 email

Thanks for the help in advance

[arasn]

Trigger your flow based on a schedule (e.g., daily)

use the lookup records action to find all records in the sysapproval_approver table where state is requested and created is older than 40 days

use a for each to aggregate over the records and create a data structure using flow variables and set it using a script to group the records by the approver field. extract approver and append the record details (e.g., sys_id, approval, etc.) to a dictionary keyed by approver.

EXAMPLE :

(function execute(input, output) {
    var approverMap = {};
    var gr = new GlideRecord('sysapproval_approver');
    gr.addEncodedQuery('state!=approved^sys_created_on<=javascript&colon;gs.daysAgo(40)');
    gr.query();

    while (gr.next()) {
        var approver = gr.approver.toString();
        if (!approverMap[approver]) {
            approverMap[approver] = [];
        }
        approverMap[approver].push(gr.getValue('sys_id'));
    }
    output.approverMap = approverMap;
})();

note : i used glide record here but you can extract the records from the look up records step.

now you should have a map with each approver and their pending approvals.

finally use that to send emails from the flow.

please note this is a very high level potential solution it might not be perfect but it's where i would start.

[Runjay Patel]

Hi @DianaBTX ,

Use below script in your flow to get the unique approvers.

var approvers = {};
inputs.records.forEach(function(record) {
    approvers[record.approver] = true;
});
outputs.uniqueApprovers = Object.keys(approvers);

Before this script Lookup Records from sysapproval_approver  table with condition like

• State is not Approved.
• sys_updated_on is more than 40 days ago

Add foreach activity to send email using gs.eventQueue();

-------------------------------------------------------------------------

If you found my response helpful, please consider selecting "Accept as Solution" and marking it as "Helpful." This not only supports me but also benefits the community.

Regards
Runjay Patel - ServiceNow Solution Architect
YouTube: https://www.youtube.com/@RunjayP
LinkedIn: https://www.linkedin.com/in/runjay

-------------------------------------------------------------------------

[jMarshal]

This sounds very similar to how ServiceNow builds the oob Knowledge Management "article expiry" notification...it's pretty elaborate and builds a table in a script include which is called from a mail script....but you could probably reverse engineer that and build a custom mail script that does it for a custom notification on sysapproval table. You would also need a scheduled job that runs periodically to trigger an event for the notification.

It might be easier to run as a scheduled email report...the downside to that being that the recipient would just have an attached report (pdf, csv) for the approvals which are pending, not links to the approval records, so that they can handle them in-platform.