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

sgodinho · ‎01-06-2020

Hello,

I have a similar requirement, but instead of 5 different attachments, I have a single Excel attachment with multiple tabs. Each tab contains data that will ultimately be imported to its own target table.

Do you have any suggestions as to how I might be able to alter this script in order to help it fit this need?

Thank you in advance!

Emerce2000 · ‎07-30-2013

Thanks for the help. 🙂

udayakumarsanga · ‎01-17-2018

Hi Jim Silvius,

Can you please tell me how to invoke this script include if possible in detail.