Auto-import attached file on RITM using workflow script

[dianemiro]

Hello Everyone,

Does anyone here already done an auto-import from an attached excel file on RITM using workflow script? We are trying to build a Service Catalog Item to automatically add/update/remove locations by auto-importing user's attached excel file. Reason why we want to use RITM so that Data Management team can verify if the data on the attached file is correctly filled out. After closing the catalog task, we want to run the script to auto-import the attached excel file. Thank you in advance.

Diane.

[Ankur Bawiskar]

Hi Diane,

Here is the approach:

1) Have business rule after insert on RITM table which runs only for that particular Catalog Item.

2) Have a data source, transform map and field map created as usual. coalesce etc will be present

3) Have scheduled import created with this data source with active= false, run as administrator since it will be triggered from script. In left nav type Scheduled Imports and create new

4) in the business rule condition check whether the RITM has attachment then only run the business rule and have following script

Business rule condition:

current.hasAttachments()

//extra check you can have is whether it is csv or xls based on data source attachment file type

Business rule script:

var dataSourceSysId = '<giveHere>'; // sys_id of the data source you created in step 2

var schImp_GR = new GlideRecord('scheduled_import_set');

schImp_GR.addQuery('data_source',dataSourceSysId);

schImp_GR.query();

if(schImp_GR.next()){

gs.executeNow(schImp_GR); // use this if it is in scoped app

//SncTriggerSynchronizer.executeNow(schImp_GR); // if in non-scope app i.e. global

// the above 2 lines will start the import process, etc automatically

}

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[dianemiro]

Hi Ankur,

I appreciate your quick response. Is this possible via workflow? We need to verify first the date before we run the auto-import. Thank you.

Diane.

[Ankur Bawiskar]

Hi Diane,

Yes this can be done in workflow as well. just ensure that workflow is on sc_req_item table

first copy attachment to data source using run script; then use workflow wait for timer activity and wait till that date then below run script

script:

GlideSysAttachment.copy('sc_req_item', current.sys_id, 'sys_data_source', '<dataSourceSysId>');

run script:

var dataSourceSysId = '<giveHere>'; // sys_id of the data source you created in step 2

var schImp_GR = new GlideRecord('scheduled_import_set');

schImp_GR.addQuery('data_source',dataSourceSysId);

schImp_GR.query();

if(schImp_GR.next()){

gs.executeNow(schImp_GR); // use this if it is in scoped app

//SncTriggerSynchronizer.executeNow(schImp_GR); // if in non-scope app i.e. global

// the above 2 lines will start the import process, etc automatically

}

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[dianemiro]

Hi Ankur,

Tried with your solution but it's not working. What am I doing wrong? Here's what I have done so far:

1. Created Data Source.

2. Created Transform Map.

3. Created Scheduled Import.

4. On my workflow I added Run Script activity:

var dataGr = new GlideRecord('sys_data_source');
var attach = new GlideSysAttachment();
dataGr.addQuery('sys_id','266fc888dbb9770013c7757a8c961954'); //sys_id of data source
dataGr.query();
while(dataGr.next()){
    attach.deleteAll(dataGr); //delete all attachments on the data source
}

attach.copy('sc_req_item', current.sys_id, 'sys_data_source', '266fc888dbb9770013c7757a8c961954'); //copy RITM attachment to Data Source

var dataSourceSysId = '266fc888dbb9770013c7757a8c961954';
var schedImp = new GlideRecord('scheduled_import_set');
schedImp.addQuery('data_source',dataSourceSysId);
schedImp.query();

if(schedImp.next()){
    gs.executeNow(schedImp);
}

But apparently, this is not working.

Diane.