Auto-import attached CSV file from RITM Table to datasource table

[Jyoti Malhotra]

Hi Team,

We are creating  service request with CSV attachment from portal, then one RITM request generated with that CSV attachment. From RITM number trying to import attached CSV  file into Target Table 'cmdb_ci_outage' . Everytime, if we will create new Service Request with CSV attachment from portal then previous attachment should get delete from Data source table and new attachment should get inserted in to data source table and attachment content should get import into target table i.e. 'Cmdb_ci_outage'. 

I'm following below approach:

Created a data source with the File type CSV.

Created transform map with field maps - Source, target and Coalesce and set the Target table as well.

Created Scheduled Data import.

Created Business Rule with below script.

(function executeRule(current, previous /*null when async*/) {

current.hasAttachments();

var dataSourceSysId = '8bbeb3f133389250b3e0b4023d5c7b8a';

var gr = new GlideRecord('sys_data_source');

gr.addQuery('sys_id', dataSourceSysId);

gr.query();

if(gr.next()){

//Deleting existing attachments while inserting new RITM attachment into the data source table

var attach = new GlideRecord('sys_attachment');

attach.addQuery('table_name', 'sys_data_source');

attach.addQuery('table_sys_id', dataSourceSysId);

attach.query();

if(attach.hasNext()){

while(attach.next()){

attach.deleteRecord();

}

//insert new attachment from the RITM table to into the data source table 

//var gsa = new GlideSysAttachment();

GlideSysAttachment.copy('sc_req_item', current.sys_id, 'sys_data_source', '8bbeb3f133389250b3e0b4023d5c7b8a');

}

}

// Add your code here

var dataSourceSysId = '8bbeb3f133389250b3e0b4023d5c7b8a';

var schimp_GR = new GlideRecord('scheduled_import_set');

schimp_GR.addQuery('data_source', dataSourceSysId);

schimp_GR.query();

if(schimp_GR.next()){

SncTriggerSynchronizer.executeNow(schimp_GR);

}

})(current, previous);

Previous attached file on datasource table getting deleted, But new attachment not getting insert into datasource table from RITM Table. Please help here. 

Thanks

JM

[Mani A]

(function executeRule(current, previous /*null when async*/) {

    // Define the data source sys_id

    var dataSourceSysId = '8bbeb3f133389250b3e0b4023d5c7b8a';

    // Check for existing attachments on the data source

    var attach = new GlideRecord('sys_attachment');

    attach.addQuery('table_name', 'sys_data_source');

    attach.addQuery('table_sys_id', dataSourceSysId);

    attach.query();

    // Delete existing attachments

    while (attach.next()) {

        attach.deleteRecord();

    }

    // Check for attachments on the RITM (Service Request Item)

    var ritmAttachment = new GlideRecord('sys_attachment');

    ritmAttachment.addQuery('table_name', 'sc_req_item');

    ritmAttachment.addQuery('table_sys_id', current.sys_id);

    ritmAttachment.query();

    // If RITM has attachments, copy the first one to the data source

    if (ritmAttachment.next()) {

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

    }

    // Trigger the scheduled import

    var schimp_GR = new GlideRecord('scheduled_import_set');

    schimp_GR.addQuery('data_source', dataSourceSysId);

    schimp_GR.query();

    if (schimp_GR.next()) {

        SncTriggerSynchronizer.executeNow(schimp_GR);

    }

})(current, previous);

[Jyoti Malhotra]

Hi @Mani A 

The above provided Script is working in my PDI. But not working in my Company Dev instance, till Delete existing attachments part it's working but unable to insert attachment from RITM to Data source Table.

Thanks

JM

[Mani A]

Which BR created ?

After insert/update BR on RITM table with state changes to open or something 

[Jyoti Malhotra]

Hi @Mani A 

I created After insert BR On RITM Table.

Thanks

JM