while loading data from excel to mrvs, it is not updating the mrvs.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-02-2023 05:48 AM - edited 11-02-2023 06:04 AM
(function executeRule(current, previous /*null when async*/ ) {
//Check for attachment - Query the sys_attachment table to see if current.sys_id has an attachment there
var tableAssociateAttachment = 'xyzsc_req_item';
//Check for attachment - Query the sys_attachment table to see if current.sys_id has an attachment there
var attach = new GlideRecord('sys_attachment');
attach.addQuery('table_name', tableAssociateAttachment);
attach.addQuery('table_sys_id', current.sys_id);
attach.query();
if (attach.next()) {
gs.log("Found attachment for record: " + attach.file_name);
attach.table_name = 'sc_req_item';
attach.update();
try {
processImportDatatoMRVS(current);
} catch (e) {
gs.addErrorMessage('Error. Cannot import data to MRVS');
}
} else {
gs.log("No attachment found, throwing error");
}
})(current, previous);
function processImportDatatoMRVS(current) {
var dataSourceTable = 'sys_data_source';
var importSetTableName = 'data_to_mrvs'; // import set table name
// Create data source from attachment - A data source is how ServiceNow imports data into an Import Set
var dataSource = new GlideRecord(dataSourceTable);
dataSource.name = "add_" + current.sys_id; //
dataSource.type = "File";
dataSource.format = "Excel";
dataSource.file_retrieval_method = "Attachment";
dataSource.import_set_table_name = importSetTableName;
dataSource.header_row = 1;
dataSource.sheet_number = 1;
var dataSourceId = dataSource.insert();
gs.log("Data source created: " + dataSourceId);
//- Copy attachment to data source - A data source with an excel attachment type expects an excel attachment to import from
gs.log("Copying attachment from current table: " + current.getTableName() + "." + current.sys_id + " to data source");
new GlideSysAttachment().copy(current.getTableName(), current.sys_id, dataSourceTable, dataSourceId);
gs.log("Done copying attachment, data source is now setup");
//Create import set from data source - Read the spreadsheet and insert the rows into an import set
gs.log("Importing data from data source into import set");
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSource);
var ranload = loader.loadImportSetTable(importSetRec, dataSource);
importSetRec.state = "loaded";
importSetRec.update();
gs.log("Loaded records into import set number: " + importSetRec.number + " and sys id " + importSetRec.sys_id);
//- Read import set table - We do not use a transform for this, rather we read the import set and build our data while looping over the rows
gs.log("Getting rows from import set we just loaded - creating array of rows for MVRS");
var mvrsArray = [];
var importSetRow = new GlideRecord(importSetTableName);
importSetRow.addQuery("sys_import_set", importSetRec.sys_id);
importSetRow.query();
while (importSetRow.next()) {
gs.log("Got row from import set, creating object for MVRS row and adding it to MVRS array");
var mvrsValue = {
name: importSetRow.u_host_name.getValue(), //name
ip_address: importSetRow.u_addci_ipaddress.getValue(), //ip_address
environment:importSetRow.u_addci_environment.getValue()
};
mvrsArray.push(mvrsValue);
}
//- Create MVRS data array - MVRS records are stored as an array of Javascript objects - each object is a row and the array is the table. Therefore, we generate an array of objects and JSON.stringify it to set the MVRS data on the SC item.
// gs.log("Got all rows, now setting the MVRS variable value on the SC item");
current.variables.mrvs_name = JSON.stringify(mvrsArray);
//Once that's done, we have our spreadsheet data imported into our record.
}
we are getting null values..
can anyone suggest..
thanks
0 REPLIES 0