My requirement is to send email notification to user who has pending approvals/requests.
I built following query in analytics and linked to email template
SELECT distinct
SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS REQUESTID,
'Pending' AS REQUEST_STATUS,
ar.ENDPOINTASCSV AS APPLICATION_INSTANCE,
r.username AS REQUESTEE,
u.username,
GROUP_CONCAT(DISTINCT u.firstname ORDER BY u.firstname SEPARATOR ', ') AS M_FIRSTNAME,
aa.jbpm_activity_name AS APPROVAL_STAGE,
ar.REQUESTDATE AS REQUEST_DATE,
DATEDIFF(IFNULL(ar.DUEDATE, CURDATE()), ar.REQUESTDATE) AS DAYS_TO_EXPIRE
FROM ARS_REQUESTS ar
JOIN REQUEST_ACCESS ra ON ar.REQUESTKEY = ra.REQUESTKEY
JOIN ACCESS_APPROVERS aa ON ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
JOIN USERS u ON u.userkey = aa.approverkey AND u.statuskey = 1
JOIN USERS r ON r.userkey = ar.requestor
WHERE aa.STATUS = 1 AND ar.status != 4
GROUP BY REQUESTID
ORDER BY REQUESTID ASC
When I run in Data analyzer it is fetching correct columns and rows. but when I run analytics , it trigger only 1 email which has list of pending approval for that 1 user only, no other email triggers to other user.
Email Template code is
Name : Pending Request Manager Notification
To : ${userEmail}
Cc :
Subject : Pending Request for Request ID ${ANALYTICSDATA.'REQUESTID'[0]}
Bcc :
From :
Body<!DOCTYPE html> <html> <head> <style> img { max-width: 100%; height: auto; } table { width: 100%; border-collapse: collapse; } th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } th { background-color: #f2f2f2; } tr:nth-child(even) { background-color: #f2f2f2; } } </style> </head> <body style="padding-right: 400px; padding-left: 20px;"> Hi ${ANALYTICSDATA.'M_FIRSTNAME'[0]}, <br>Please find the below details of Pending Request :<br><br> <table> <th>Request ID</th> <th>Requestor </th> <th>Request Date</th> <% int count=Integer.parseInt("${ANALYTICSDATA.Application.size()}"); for(int i=0;i<count;i=i+1){%><tr> <td>${ANALYTICSDATA.'REQUESTID'[i]}</td> <td>${ANALYTICSDATA.'REQUESTEE'[i]}</td> <td>${ANALYTICSDATA.'REQUEST_DATE'[i]}</td> </tr> <% } %> </table> </body> </html>
please suggest solution for this, why email is not going to every user who has pending request/approvals.
