Working with Attachment Data

[Emerce2000]

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.

[harikrish_v]

Hi,

I am not sure on what the exact requirement is, but as far as I understand, what you need is a Data Source.You can find it under System Import Sets->Data source in the left nav. Now if the excel is something that you manually have to upload, you can attach it to the data source and choose the file retrieval method as Attachment and execute it. If its something that you get from a different vendor, best thing is to have them upload it via FTP/ SFTP. You can then choose the file retrieval method as that, enter the filepath & credentials and execute it.

You can add a transform map to this data source and do the appropriate field maps/ run transform scripts based on your need and map it to your table [you can read upon Import Sets from SNC wiki for more details].

Now lets say that this activity needs to be performed on a regular basis and you want this work automated, goto Scheduled Data Import, you can find it under System Import Sets->Scheduled Imports in the left nav. Add this Data Source in the scheduled data import and set the schedule as per the requirement.

Hope this helped 🙂

Regards,

Harikrishnan Vijayan
[img=180x60]http://analyticsindiamag.com/wp-content/uploads/2013/02/ibm_logo.jpg[/img]
ServiceNow Certified System Admin | ITIL V3 Foundation Certified

[BHSCorp · Accepted solution]

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!

[plynch]

Help,

I am not that savvy with coding

If I have 5 attachments that need to go to 5 different datasources that I have setup

Where do I specify my datasources in the code

[plynch]

I don tunderstand what this means:

"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)."

And how do I set this up for multiple data sources and attacchments

So I have 5 attachments

Contacts

Locations

Switches

Routers

Circuits

I have 5 data sources for each

How do I match the attachment to the correct data source