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

[sainath reddy]

Hi @vaishali231 ,

If i create a table and make changes in flow designer. Those changes will be captured from that day onwards. May i know if i can get the past data like last 4 months data which are in requested state and which are all emails created from flow.Can you please help me on this one.

[vaishali231]

hey @sainath reddy 

For the past 4 months data, you can get it only if the required information already exists in the system.

You can try below approach:

Get pending approvals

Use sysapproval_approver as the main table and filter:

State is Requested

Approval for is RITM & sc_req_item

Created date is within last 4 months

This will give you the approvals which are still pending.

Check historical emails

For the email triggered dates, you need to check sys_email.

You can filter sys_email with conditions like:

Type is Sent

Created date is within last 4 months

Subject contains your reminder email text

Body contains RITM number, if available

Recipient contains approver email

Important limitation

This will work only if the Flow Designer emails have some unique identifier, for example:

Unique subject

RITM number in subject &body

Approval details in body

Approver email as recipient

Specific reminder text like 3 days, 5 days, 7 days

If the Flow Designer emails do not have any unique text or reference to the RITM/approval record, then it will be difficult to accurately identify only those emails from sys_email.

Recommended solution

For future reporting, create a custom log table and insert a record from Flow Designer whenever the reminder email is sent.

For historical data, you can do a one-time backfill from sys_email into the custom table, but only if you can reliably match the email with the RITM/approval.

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

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

[vaishali231]

hey @sainath reddy 

Hope you are doing well.

Did my previous reply answer your question?

If it was helpful, please mark it as correct ✓ and close the thread . This will help other readers find the solution more easily.

Thankyou & Regards

Vaishali Singh

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