Date/Time Stamping a Scheduled Report's Attachment(s)

[Jim Coyne]

I've seen a couple requests to add a date/time stamp to the attachment of a Scheduled Report.   Capa JC shows us how to add this to the email's subject in this post (When configuring a Scheduled), but doing it on an attachment is still not possible.   Until now.

A nice little workaround with a Business Rule on the Email table is all you need.   When you configure your Scheduled Report, add "~!~rename_attachment~!~" as a prefix to the Subject to act as a trigger for the Business Rule (e.g. "~!~rename_attachment~!~Active Change Requests").

Next, add the following Business Rule:

Name: Custom - Rename Scheduled Report Attachments
Table: Email [sys_email]
When: before
Insert: checked
Condition: current.subject.indexOf("~!~rename_attachment~!~") > -1 && current.hasAttachments()
Script:


(function(){
         var splitter = "~x~y~z";
         current.subject = current.subject.replace("~!~rename_attachment~!~", "");
         var gr = new GlideRecord("sys_attachment");
         gr.addQuery("table_name", "sys_email");
         gr.addQuery("table_sys_id", current.getValue("sys_id"));
         gr.query();
         while (gr.next()) {
                   var fileName = gr.getValue("file_name");
                   var parts = fileName.split(".");
                   var dateCreated = splitter + gr.getValue("sys_created_on");
                   parts.splice(-1, 0, dateCreated);
                   fileName = parts.join(".").replace("." + splitter, "-");
                   gr.file_name = fileName;
                   gr.update();
         }
})();

The Business Rule queries the attachments, grabs each file name, splits it apart, inserts the created date, joins it back together and then finally updates the record with the new file name.

Simple

[mautry]

I did get this to work which is great but similar question to the gentleman above it added a number on the end which may be time??   File name ended up being:   Monthly Outage Report - response-2015-10-27 185404.csv   Is there a way to strip the number

[nyancer3]

Hi Marion,

Here is how I stripped out the time info:

var dateCreated = current.getValue("sys_created_on");

dateCreated = dateCreated.split(' ');

dt = dateCreated[0];

Split it into an array, then take the first member.

Hope that helps!

[nyancer3]

Here's the entire script I used, to be clear where the variables should go (you can see we also added the date to the email subject):

    (function(){  
        var splitter = "~x~y~z";  

        var dateCreated = current.getValue("sys_created_on");
  dateCreated = dateCreated.split(' ');
  dt = dateCreated[0];

  current.subject = current.subject.replace("~!~file_date~!~", dt);  

  dt = splitter + dt;
     
        var gr = new GlideRecord("sys_attachment");  
        gr.addQuery("table_name", "sys_email");  
        gr.addQuery("table_sys_id", current.getValue("sys_id"));  
        gr.query();  
        while (gr.next()) {  
                  var fileName = gr.getValue("file_name");  
                  var parts = fileName.split(".");  
                  parts.splice(-1, 0, dt);  
                  fileName = parts.join(".").replace("." + splitter, "-");  
                  gr.file_name = fileName;  
                  gr.update();  
        }  
})();

[mehran]

Hi,

also I get a number instead of time in the attachment file name , how I can convert it to "time" and not stripped out it ?

which function I have to use?

Pls your advice

Mehran

[nyancer3]

I just tried this in Istanbul, and the result of current.getValue("sys_created_on") gives be back a time stamp that doesn't need to be converted. Not sure if this has changed in recent versions or not.

So, that would just mean that if you want to keep the time, then don't split dateCreated into an array. And if you just want the time, then split into an array and take the second element.