Oscar Lopez
Mega Guru
Mega Guru

Business Requirement

One of my customers needed a weekly report containing all the pending approvals. The report should be sent via email to the approver attaching the list of records pending approvals; this report should be sent every Friday at noon as a reminder for the approver to acknowledge that they have pending tasks to be approved or rejected.

 

The Solution

1. You need to create a report to query the Approval [sysapproval_approver] table where State is "Requested" and "Approver is (dynamic) Me". Make sure to publish the report Public or share it with everyone.

find_real_file.png

2. Now you will need a Scheduled Job [sysauto_script] with the following details:

  • Name: Weekly Pending Approvals Report
  • Run: Weekly
  • Day: Friday
  • Time: 12:00
  • Script:
execute();

function execute() {
	/* query approval table with GlideAggregate*/
    var approval = new GlideAggregate('sysapproval_approver');
	/* select only requested records */
    approval.addEncodedQuery('state=requested');
	/* group by approver in orer to get unique values (one distinct user at a time) */
	approval.groupBy("approver");
    approval.query();

    while (approval.next()) {
		/* Create a unique Scheduled Report */
		createScheduleReport(approval.approver);
    }
}

function createScheduleReport(user) {
	/* set the appropiate sys_id of your 'Weekly Pending Approvals Report' report */
    var TARGET_REPORT = "9b32139c1b898410a3faddbcdd4bcb69";

	/* create the scheduled report */
    var scheduled_report = new GlideRecord('sysauto_report');
    scheduled_report.initialize();

	/* set all the values */
	scheduled_report.active = true;
    scheduled_report.name = "WeeklyAutoApprovalReminder";
    scheduled_report.run_as = user.getValue("sys_id");
    /* this will replace "is (dynamic) Me" with the real name of the user */
    scheduled_report.user_list = user.getValue("sys_id");
	/* the scheduled report it's unique, hence it should run only once */
    scheduled_report.run_type = 'once';
    scheduled_report.report_title = "Your Weekly Pending Approvals Report";
    scheduled_report.report_body = "Find attached the your weekly Pending Approvals Report";
    scheduled_report.report = TARGET_REPORT;
    scheduled_report.omit_if_no_records = true;
	/* it will attach the records using a spreadsheet attached to the email notification; check the choice list to see other options */
	scheduled_report.output_type = "XLSX";
    var answer = scheduled_report.insert();
}

run_type = 'once'

The scheduled job (script above) will create a unique Scheduled Report for each user that has pending approvals [state='requested'] by the current week. This means that every Friday at noon, dozens or hundreds of Scheduled Reports might get created and all of them will run immediately querying all the pending approvals per user, this is why in the Report of step #1 you have to use the operator "Approver is (dynamic) Me". When the Scheduled Report is created immediately replaces the "is (dynamic) Me" with the name of the dynamic user in question.

Clean up

I recommend to create another scheduled job to delete the used Scheduled Reports generated in the past, remember that they are unique and they run one time only.

 

 

 

Comments
kalindaskalov
Giga Contributor

Thank you very much. 
This was most usefull!

Edxavier Robert
Mega Sage

Hi @Oscar Lopez , 

 

Excellent post! Quick question, how we can leverage the clean up? 

 

"Clean up

I recommend to create another scheduled job to delete the used Scheduled Reports generated in the past, remember that they are unique and they run one time only."

Edxavier Robert
Mega Sage

I got it! 

I create this and it works. This is to delete all the reports with the name WeelkyAutoApprovalReminder that was not created on the same day.

var gr = new GlideRecord('sysauto_report');
gr.addEncodedQuery('name=WeeklyAutoApprovalReminder^sys_created_onNOTONToday@javascript:gs.beginningOfToday()@javascript:gs.endOfToday()');
gr.deleteMultiple();

 

Val Aur
Tera Contributor

Is it also possible to report all Knowledge Articles (unique records only) whose state is Requested and grouped by Approver?  

Peter Williams
Kilo Sage

Is it possible to include the links to the request of the approvals in the body of the email?

Jon Epstein
Giga Guru

Did anybody get this to work with PLATFORM ANALYTICS reports and the SCHEDULED EXPORTS that get created by the script?  I'm not seeing any emails actually fire, though the jobs get queued up.


Thanks,

Jon

Version history
Last update:
‎05-27-2020 07:05 PM
Updated by: