Working with Attachment Data

Emerce2000 · ‎07-17-2013

As we expand into more and more areas with Servicenow as a company we find ourselves looking for ways to automate things that used to require manual intervention in the old system. Today we came across a process that involves imports of data in XLS files into the system. The concept we are trying to work out is as follows. We want to create a table to keep track of some details of the transaction as well as the attachment. Our goal is to be able to either via workflow or script, automatically import that data in the attachment into a specific table. I'm looking for suggestions on the best way to accomplish this hoping someone has already built out a similar process.

Thanks in advance.

BHSCorp · ‎07-19-2013

Good afternoon. This is becoming more and more common, and we have a script that will automate the import of an attachment. Basically the process is this:

Copy the attachment to a Data Source (needs to be setup previously), should be setup as File Retrieval Method of Attachment
Automatically run the import

One of the most common cases is to use email attachments as an import mechanism. Anyway, I am being long winded. The script looks like this:



var AttachmentImportProcessor = Class.create();
AttachmentImportProcessor.prototype = {
    record: null,
    scheduledImportRecord: null,
    dataSourceRecord: null,
    attachmentTypes: [],
    debug: true,
    initialize: function(record, dataSourceSysId) {
        this.record = record;
        this.getDatasource(dataSourceSysId);
    },
  
    setAttachmentTypes: function(types){
        for (var i = 0; i < types.length; i++) {
            this.attachmentTypes.push(types<i>);
        }
    },

    getDatasource: function(dataSourceSysId){
        this.dataSourceRecord = new GlideRecord("sys_data_source");
        this.dataSourceRecord.get(dataSourceSysId);
    },

    processAttachments: function (isSynchronous) {
        var sourceTable = this.record.sys_class_name.toString();
        var sourceID = this.record.sys_id.toString();
        var targetTable = this.dataSourceRecord.getTableName();
        var targetID = this.dataSourceRecord.sys_id.toString();

        var gr = this.getAttachments(sourceTable, sourceID);
        this.logDebug("Num Attachments sent: " + gr.getRowCount());
        this.logDebug("Valid Attachment Types: " + this.attachmentTypes);
        while (gr.next()) {
            if (this.isAttachmentValidType(gr)) {

                this.copyAttachment(gr, targetTable, targetID);
                this.logDebug("Processing attachment: " + gr.file_name);
                if (isSynchronous) {
                    this.processImportSynchronous();
                }
            }
        }
    },

    isAttachmentValidType: function(gr){
        if (this.attachmentTypes == null || this.attachmentTypes.length == 0) {
            return true;
        } else {
            var isValid = false;
            var type = gr.content_type.toString();
            for (var i = 0; i < this.attachmentTypes.length; i++) {
                if (this.attachmentTypes<i> == type) {
                    isValid = true;
                    break;
                }
            }
            if (!isValid) {
                this.logDebug("Rejected Attachment Type: " + type);
            }
            return isValid;
        }

        return false;
    },

    copyAttachment: function (gr, targetTable, targetID) {
        var attachment = Packages.com.glide.ui.SysAttachment();
        attachment.deleteAll(this.dataSourceRecord);
      
        gr.table_name = targetTable;
        gr.table_sys_id = targetID;
        var oldid = gr.sys_id.toString();
        var newid = gr.insert();

        var doc = new GlideRecord("sys_attachment_doc");
        doc.setWorkflow(false);
        doc.addQuery("sys_attachment", oldid);
        doc.query();
        while (doc.next()) {
            doc.setValue("sys_attachment", newid);
           doc.insert();
        }
    },

    getAttachments: function(sourceTable, sourceID){
        var gr = new GlideRecord("sys_attachment");
        gr.addQuery("table_name", sourceTable);
        gr.addQuery("table_sys_id", sourceID);
        gr.query();
        return gr;
    },
  
    getImportRecord: function () {
        this.scheduledImportRecord = new GlideRecord("scheduled_import_set");
        this.scheduledImportRecord.addQuery("name", this.dataSourceRecord.name);
        this.scheduledImportRecord.addQuery("data_source", this.dataSourceRecord.sys_id);
        this.scheduledImportRecord.query();
        if (!this.scheduledImportRecord.next()) {
            this.scheduledImportRecord = new GlideRecord("scheduled_import_set");
            this.scheduledImportRecord.initialize();
            this.scheduledImportRecord.name = this.dataSourceRecord.name.toString();
            this.scheduledImportRecord.active = false;
            this.scheduledImportRecord.data_source = this.dataSourceRecord.sys_id.toString();
            this.scheduledImportRecord.post_script_bool = true;
            this.scheduledImportRecord.post_script = "";
            this.scheduledImportRecord.insert();
            this.logDebug("Scheduled Import created.");
        } else {
            this.logDebug("Scheduled Import set found: " + this.scheduledImportRecord.sys_id.toString());
        }
    },
  
    processImportSynchronous: function () {
        var retVal = false;
        this.getImportRecord();
        if (this.scheduledImportRecord != null &amp;&amp; this.scheduledImportRecord.isValidRecord()) {
            var trigger = "";
            var triggerGr = new GlideRecord("sys_trigger");
          
                trigger = Packages.com.snc.automation.TriggerSynchronizer.executeNow(this.scheduledImportRecord);
                triggerGr.get(trigger);
                while (triggerGr.isValidRecord()) {
                    triggerGr = new GlideRecord("sys_trigger");
                    triggerGr.get(trigger);
                }
                retVal = true;
           
        } else {
            //Log
        }
        return retVal;
    },

    logDebug: function(msg){
        if (this.debug) {
            var log = new GSLog("com.ally.ecm.log", "AttachmentImportProvider");
            log.logDebug(msg);
        }
    },

    logError: function (msg) {
        var log = new GSLog("com.ally.ecm.log", "AttachmentImportProvider");
        log.logErr(msg);
    },
  
    type: 'AttachmentImportProvider'
}

This script include allows for multiple attachments on a record, and in the constructor of the object merely takes the record you want to copy the attachments from, and the dataSourceSysId of the Data Source you setup (as mentioned above).

Let me know if this helps!

View solution in original post

Daniel Draes · ‎01-27-2014

Looks like you want to import certain CMDB data via catalog item. Why not completely automate and have a scheduled import picking up these files somewhere?

plynch · ‎01-27-2014

Actually,

I do need an automated import

But the criteria is based on Sales team

They have customers we onboard

When we receive the customers data from the sales team it is in 1 spreadsheet with multiple tabs (apprx 15)

This is a manual process

I need a way to have the sales team open an incident, attach the file(s) and have the system automatically upload

The file names do not stay the same

They are different based on the customer

Daniel Draes · ‎01-29-2014

Have a look here UPDATED - Use an email with an attachment to create a Data Source, Load Data, and Run Transform, you should be able to find the code required to automate it.

mmachin01 · ‎05-16-2014

Hi,

We are having the same problem. We need to load automatically and to do it for only one sheet was easy (thanks for the notes). But if we try to load another sheet that not is the first one, we can't.

Some suggestion about what could be the parameter to use in order to change the sheet to load (as the Load Data module does).

We tried "sheet" but it's not going to be so easy.

Many thanks!

Daniel Draes · ‎06-23-2014

I have checked the blog post again. Seems even jason did not find a way to achieve this - unfortunatly. Looks like a dead-end for you then. sorry.