How to Send an On Demand Report with Customized Information in Email

[nluoma]

We have a custom app that tracks audits on shipments we receive. This requires the audit team to communicate with different vendors and shippers with updates on how the audit is going. The team was spending a great deal of time putting these notifications together, usually doing it out of their personal email. We wanted to simplify the process and keep these notifications attached to the ticket for a complete record of the audit.

We discussed several possible solutions:

• Having the team cc the Snow instance so the updates were attached would keep the emails tied to the ticket, but it didn’t do anything about how long it took to create the emails. In fact, it added one more step to the process.

• Creating an on-demand report would let them set the data up consistently, but the process of customizing the on-demand report required several steps and if not done correctly could end up with proprietary information sent to the wrong vendors. It also wouldn't let us pull data from the record and include it in the email.

We needed something extremely simple, preferably from a single button, and needed to be extremely flexible as the number of recipients would vary, based on the related records.

My solution is a little complicated on the back-end but resulted in a UI action that would let the team simply click and send the update when needed.

Overview

To make this work, you need three parts:

1. UI Action to trigger the process
2. A report that displays the information you want to be sent.
3. An On-Demand Schedule for that report

Start with the Report

I started with the Report, setting it up for a single record and pulling in the related information needed. We tested this to make it look the way the team wanted. You’ll need the Title (or the sys_id) of this record later.

The UI Action is going to change the following fields (you can adjust this to match what you need):

• filter

Set up On-Demand Report

The On-Demand Report was then set up. You’ll need the Title (or the sys_id) of this record later.

The UI Action is going to update or change the following fields (you can adjust this as needed—those were the fields we wanted to be customized. For example, you can add dates to the subject if you want.):

• Email Addresses
• Introductory Message

Set up the UI Action

Finally, we set up the UI Action.

The UI action is going to:

1. Get the sys ID of the current audit. This will be used in step 4 to set the filter for this audit.
2. Get the email addresses this will be sent to from the current record, and combine them into a single string. These will serve as the recipients.
3. Update the message for the scheduled report that is sent out. This can be anything you want in HTML format. Not the most elegant way to craft an email, but it lets us customize the message on the scheduled report with data from the current record.
4. It will then get the record for the report you will be running. You can do this by sys_id, but I usually use the Title—which makes it easier later to figure out what report I used. (Of course, if I change the title of the report I have to remember to revise UI Action.)
5. It will get the record for the scheduled report and updates the email addresses and the message to send. Again, I used the Title but you can use sys_id.
6. It returns to the audit record (where the UI action was triggered) and adds a note that an audit update was sent and who it was sent to.
7. The final piece triggers the actual scheduled job.

As I said, the process is a little complicated. The report and the scheduled report record are pretty straightforward, so here’s the UI Action code you really need to make it work. (This was a lot of trial and error, so please excuse the AddInfoMessage stuff I commented out. That’s my debugging and I leave it in for future testing needs.)

//*********************************************
// Retrieves a scheduled report, updates with
// data from the current audit record, then
// executes the report.
//
//*********************************************

//get the current audit sys_id for future reference
var audit = current.sys_id; 
//gs.addInfoMessage('In the loop');

//Combine all the email addresses into one string
var email = '';
if (current.u_factory_email)          //If there is a factory email, add to string
       {email += current.u_factory_emai + ',';}
if (current.u_manager_email)     //If there is a manager email, add to string
	{email += current.u_manager_email + ',';}
email += ‘default.email.com’;  //add default emails you want to include


//Next, set the message to the correct HTML code
var msg = '<p>Dear  ' + current.u_factory_representative + ', </p>';
msg +=  '<p> The attached Corrective Action Plan (CAP) has been initiated for ' + current.u_factory.u_name + ' as a result of non-conformances discovered during the CTPAT audit conducted by ';
msg += current.u_auditor_department + ' on '+ current.u_audit_date + '.</p>';

// Get the report record that we want for this project and edit it
//gs.addInfoMessage('In update report loop');
var rep = new GlideRecord('sys_report');
rep.get('title’,’Your Report Title);
//rep.get('36031a4bdb42d45092523562399619af');  //Alternate method, using sys_id. Obviously, replace with report sys_id
//gs.addInfoMessage('Got report ' + rep.filter);
rep.filter = 'u_c_tpat_audit='+current.sys_id; //Set the filter to point to the current audit.  In this case, u_c_tpat_audit is the name of the table, so filter is showing only a single record
rep.update();



// Get the scheduled report that we want for this project
var rec = new GlideRecord('sysauto_report');
rec.get('name','Scheduled Execution of Your Report Title’);
//rec.get('2491568c1b1e5c501be686e8cd4bcb9c'); // Alternate method by sys_id. Obviously replace with sys_id of your scheduled report record
//set the address list to the emails on the current audit record
rec.address_list = email;


//copy the message to the correct field
rec.report_body = msg;
//Update the record
rec.update();

//gs.addInfoMessage('past update');

//Now we are going to update the audit record to ensure that the data is correct there
current.work_notes = 'Email of corrective actions sent to ' + email;
current.update();
//gs.addInfoMessage('Updated record');

// Trigger the "Execute Now" button on the scheduled report form.
if (typeof SncTriggerSynchronizer != 'undefined')
   SncTriggerSynchronizer.executeNow(rec);
else
   Packages.com.snc.automation.TriggerSynchronizer.executeNow(rec);

action.setRedirectURL(current);

Hope you find this useful.

[PLakin1]

Very useful information. Thank you!