Join the #BuildWithBuildAgent Challenge! Get recognized, earn exclusive swag, and inspire the ServiceNow Community with what you can build using Build Agent.  Join the Challenge.

while loading data from excel to mrvs, it is not updating the mrvs.

sri8
Tera Contributor
(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