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

Jyoti Malhotra · ‎10-01-2024

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 · ‎10-03-2024

Can you put logs in BR for where exactly it's failing...? If attachment is available on RITM when BR is running or nor that we have to verify it...if it took sometime for attachment transfer from item to RITM then add gs.sleep(60) in BR at first line..

Jyoti Malhotra · ‎10-03-2024

Hi @Mani

I'm trying with below script but in logs showing "No attachments found on the current record."

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

gs.sleep(60);

gs.log('Executing business rule for attachment handling and data import.');

// Check if the current record has attachments

gs.log('Checking if the current record has attachments.');

var hasAttachments = current.hasAttachments();

if (hasAttachments) {

gs.log('The current record has attachments Found');

} else {

gs.log('No attachments found on the current record.');

}

var dataSourceSysId = '8bbeb3f133389250b3e0b4023d5c7b8a';

// Query the sys_data_source table

var gr = new GlideRecord('sys_data_source');

gr.addQuery('sys_id', dataSourceSysId);

gr.query();

if (gr.next()) {

gs.log('Found data source with sys_id: ' + dataSourceSysId + ', name: ' + gr.name);

// 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()) {

gs.log('Found existing attachments for data source: ' + dataSourceSysId + '. Deleting them now.');

while (attach.next()) {

gs.log('Deleting attachment with sys_id: ' + attach.sys_id);

attach.deleteRecord();

}

} else {

gs.log('No existing attachments found for data source: ' + dataSourceSysId);

}

// Insert new attachment from the RITM table into the data source table

gs.log('Copying attachment from sc_req_item: ' + current.sys_id + ' to sys_data_source: ' + dataSourceSysId);

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

gs.log('Attachment copied successfully.');

} else {

gs.log('No data source found with sys_id: ' + dataSourceSysId);

}

// Query the scheduled_import_set table

var schimp_GR = new GlideRecord('scheduled_import_set');

schimp_GR.addQuery('data_source', dataSourceSysId);

schimp_GR.query();

if (schimp_GR.next()) {

gs.log('Found scheduled import set for data source: ' + dataSourceSysId + '. Executing now.');

SncTriggerSynchronizer.executeNow(schimp_GR);

gs.log('Scheduled import set executed successfully.');

} else {

gs.log('No scheduled import set found for data source: ' + dataSourceSysId);

}

})(current, previous);

Thanks

JM

Mani A · ‎10-03-2024

So no attachment available on RITM

Please make sure attachment is added on item and it should available on RITM

Please send screenshot of RITM .

Jyoti Malhotra · ‎10-03-2024

Hi @Mani A

Please find the screen shot below:

Thanks

JM

Jyoti Malhotra · ‎10-05-2024

Hi @Mani A

when we are raising request from portal with attachment that attachment taking time to get attach from Item to on RITM Table. That' why in logs showing "No attachment found on RITM table".

I tried Retry Mechanism: The script retries up to 5 times (or any other number you set) to check for the attachment on the RITM. Each retry waits for 2 seconds (gs.sleep(2000)), allowing time for the attachment to be processed and linked to the record.
Conditional Import: The import process is only triggered if an attachment is successfully found within the retry loop.

How it Works:
The script checks if an attachment exists on the RITM.
If no attachment is found, it waits for 2 seconds and tries again.
This process repeats up to 5 times (or until an attachment is found).
If after all retries no attachment is found, it logs a failure message.

// Retry mechanism for checking attachments
var maxRetries = 5; // Number of retries
var retryCount = 0;
var attachmentFound = false;

// Loop until attachment is found or max retries reached
while (retryCount < maxRetries && !attachmentFound) {
gs.log('Attempt ' + (retryCount + 1) + ' to find attachments on RITM: ' + current.sys_id, 'Attachment Import Script');

// 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 (ritmAttachment.next()) {
gs.log('Attachment found on RITM after ' + (retryCount + 1) + ' attempt(s)', 'Attachment Import Script');
gs.log('Copying attachment from RITM: ' + ritmAttachment.sys_id + ' to data source: ' + dataSourceSysId, 'Attachment Import Script');
GlideSysAttachment.copy('sc_req_item', current.sys_id, 'sys_data_source', dataSourceSysId);
attachmentFound = true; // Break loop if attachment is found
} else {
gs.log('No attachments found on RITM: ' + current.sys_id + ' (Attempt ' + (retryCount + 1) + ')', 'Attachment Import Script');
retryCount++;
gs.sleep(2000); // Wait for 2 seconds before retrying
}
}

if (!attachmentFound) {
gs.log('Failed to find any attachments on RITM after ' + maxRetries + ' attempts', 'Attachment Import Script');
}

// Trigger the scheduled import if an attachment was found
if (attachmentFound) {
var schimp_GR = new GlideRecord('scheduled_import_set');
schimp_GR.addQuery('data_source', dataSourceSysId);
schimp_GR.query();

if (schimp_GR.next()) {
gs.log('Triggering scheduled import for data source: ' + dataSourceSysId, 'Attachment Import Script');
SncTriggerSynchronizer.executeNow(schimp_GR);
} else {
gs.log('No scheduled import set found for data source: ' + dataSourceSysId, 'Attachment Import Script');
}
}

But still the attachment not found on RITM Table. Any other approach we can try ?

Thanks

JM