Create a report on emails triggered date on the rimts which are in requested state for 3,5,7 days

[sainath reddy]

HI Team,

I have a requirement to create a report. I have to show the approvals which are still in requested state for so long and there is a email triggered from flow designer for 3,5,7 days. And I want to show those RITMS which are in requested state and also I want to show the email triggered dates from flow designer. 

I have created a database view between sysapproval_approver and sys_email table. And I'm using this database view as a table in the report. But when i'm giving filters to get those reports, The data is not getting genetrated and some times it only shows the emails which created by system like apart from the flow designer created emails other emails are coming to the report view.

I want to show the flow designer created emails in my report. Is it possible. And i want this to be generated in one consolidated report.

Thanks

[vaishali231]

Hey @sainath reddy 

Yes, this is possible, but I would not recommend building this report directly by joining sysapproval_approver and sys_email.

sys_email is not always a reliable reporting source for this use case because Flow Designer emails are usually created as system-generated emails. If the report only depends on subject/body/created by filters, it may also pick up other system emails and give incorrect results.

Approach:

Create a custom reminder log table

Create a custom table, for example:

u_approval_reminder_log

Suggested fields:

1. RITM reference
2. Approval reference
3. Approver
4. Reminder type, for example 3 days, 5 days, 7 days
5. Email triggered date/time
6. Flow name
7. Email status  
   
   Update the Flow Designer flow

In your Flow Designer flow, after each email action, add one more action to create a record in the custom log table.

Example:

1. Send 3-day reminder email
2. Create record in u_approval_reminder_log
3. Store RITM, approval record, approver, reminder type, and current date/time

Do the same for 5-day and 7-day reminders.

Build the report from the custom table

Once the log table is populated, create a report on u_approval_reminder_log.

You can filter it like this:

1. Approval state is Requested
2. RITM approval is still pending
3. Reminder type is 3 days / 5 days / 7 days
4. Email triggered date is not empty

This will give you one consolidated report with:

1. RITM number
2. Approver
3. Approval requested date
4. Current approval state
5. 3-day reminder sent date
6. 5-day reminder sent date
7. 7-day reminder sent date
   
   Why database view with sys_email is not ideal

A database view between sysapproval_approver and sys_email may not return accurate data because there is no strong direct relationship between the approval record and the email record. Also, multiple system emails may have similar subjects or body content, so the report can include emails that were not triggered by your Flow Designer reminder logic.

Alternative if you still want to use sys_email

If you cannot create a custom table, then make the Flow Designer email subject/body very unique and filter sys_email using that unique text.

Example:

1. Subject contains Approval Reminder - 3 Days
2. Subject contains Approval Reminder - 5 Days
3. Subject contains Approval Reminder - 7 Days
4. Type is sent
5. Created by is system

However, this is less reliable than logging the reminder action into a custom table.

Best practice:

Use a custom reminder log table and insert records from Flow Designer whenever the reminder email is sent. This gives you accurate, clean, and audit-friendly reporting instead of depending on sys_email.

*************************************************************************************************************************************

If this response helps, please mark it as Accept as Solution and Helpful.

Doing so helps others in the community and encourages me to keep contributing.

Regards

Vaishali Singh

Servicenow Developer
Linkedin - https://www.linkedin.com/in/vaishali-singh-2273361bb