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

[Tanushree Maiti]

Hi @sainath reddy 

ServiceNow Flow Designer does not store the exact dates of emails sent as metadata on the RITM or Approval records. To report on these dates directly, you must create a custom logging table.

1. To capture and report on the 3, 5, and 7-day email triggers, update your Flow Designer flows:

• Create a custom table (like u_approval_reminder_log).
• Add columns for RITM (Reference), Approval Record (Reference), Approver (Reference), Reminder Type (String/Choice: 3 days, 5 days, 7 days), and Email Triggered Date/Time (Date/Time).
• Right after your 3, 5, and 7-day email actions in the Flow, add a Create Record action to log these exact details in the custom table.

2.Creating the Report :Once the data is logged, you can build your report in

• Data/Source: Set the Table to your new custom log table (u_approval_reminder_log).
• Type: List or Pivot.
• Conditions:
• Approval.State is Requested
• Approval.Task type is Request Item [sc_req_item]
• Email Triggered Date is not empty
• List View Configuration: Add fields like RITM, RITM.Short Description, Approval, Approver, Reminder Type, and Email Triggered Date to your list layout.