Accepting a file via inbound email attaching to existing data source and updating target table

anfield · ‎05-17-2019

Working on an integration to do the following - accept an inbound email with a certain subject and header and attach that email with spreadsheet to an existing datasource. Automatically trigger the transform map to transform that data into the servicenow target table (or run this from a scheduled job). Also will need to either delete an attachment if one already exists on that datasource (from the last job run).

Found the following URL with a description of how to do this, and followed the steps but this creates a new datasource each time. Does anyone know how to do this with using an existing datasource?

Here is the link I have been following - Ive setup the inbound action with script, script include, datasource, and the transform map

https://community.servicenow.com/community?id=community_blog&sys_id=908c26e1dbd0dbc01dcaf3231f9619c4

Tim Provin · ‎05-17-2019

If you want to use an existing Data Source, you should be able to use everything from that other thread, but for your inbound action you will want to do something like this.

(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {

       //Set the Data Source to be used
       var dataSourceID = "SYS ID OF THE DATA SOURCE YOU WANT TO USE";

       //Query the attachments for this data source and delete them
       var gr = new GlideRecord('sys_attachment');
       gr.addQuery('table_name','sys_data_source');
       gr.addQuery('table_sys_id',dataSourceID);
       gr.query();

       gr.deleteMultiple();

       //Copy attachment from the Email to the Data Source
 GlideSysAttachment.copy('sys_email',sys_email.sys_id,'sys_data_source',dataSourceID);

       /*
         * Schedule Load of Attachment
         *
         * This inbound email action will generate an import data source, however the attachment isn't copied to the data source until
         * after the insert of the record.   Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
         */

       new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs); 

})(current, event, email, logger, classifier);

View solution in original post

Lemajeur · ‎06-04-2019

hello Tim, this works well but in my case, the email has also some signature, a .png file, how do i not copy this ? I am only interested in the .csv file

toddpreece · ‎10-14-2019

This email import works perfectly but it seems I'm erroring out on the subsequent script include transform and not sure why. This was noted in the referenced article but doesn't appear anyone got an answer there. Given that thread dates back a couple of years I wonder if there is something not compatible in newer versions. Anyway here's the error I get:

org.mozilla.javascript.EcmaError: undefined is not a function.
Caused by error in sysevent_in_email_action.53908d3c1b200090b1f77661cd4bcb00.script at line 24

21: * after the insert of the record. Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
22: */
23:
==> 24: new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);
25:
26: })(current, event, email, logger, classifier);

Thoughts?

toddpreece · ‎10-15-2019

Ended up solving my own problem here:

1) Helps to have the right sys_id for the transform map (duh). Thats what was causing the error I noted.

2) However, I found that the transform was still not occurring but listing no errors. Appears as though this is because the GlideImportSetTransformerWorker does not work from within a scoped app. To solve this I changed the script include so that it was in global scope but left my inbound action w/in the scope.

khushboonro · ‎07-02-2024

Hello

I am currently using the above script to delete existing attachment on data source.

It is not deleting the attachments.

I have runned the script on background and then it works fine but when I send an email it does delete any attachment.

Any suggestions ?