Catalog Item - Bulk RITM Creation under 1 Request

[Danie3]

Hi All

I have a Catalog Item that allows users to request a correction to product codes, descriptions, suppliers details and delivery times. 

The requirement from the business is that if there are more than 5 Items to be changed the option of uploading an Excel file must be available instead of adding 5 items to the cart. The function of the Excel file will be to log a Request with corresponding RITM's for every row in the Excel file.    

The reason for separate requests and RITM's is due to workflow approvals that are linked to Item Code and Location combinations. 

This bulk upload option must be available to users without a ServiceNow role and on the Service Portal. 

Thanks in advanced 

Danie 

[Michael Ritchie]

You can definitely "consumerize" the data load process.  I created this blog post a few years ago describing the process:

"Consumerize" Data Imports to Non-ServiceNow Administrators

Now this said creating requests and the items does get tricky and you will need to use the Cart API and other custom code to make sure all the pieces and parts get created.  Search the community because there have been various posts over the years on bulk creation of requests.

[Danie3]

Hi Michael

Thank you for your feedback.

From your link and going down some rabbit holes I managed to get something together that creates a new Request and RITM from each row in the excel spreadsheet. Perfect very happy does what I need it to do. 

Yes there is a however. The variables are empty. The excel file does not bring over the data from the file and populate the variables. I confirmed this as even the sc_item_option_mtom table shows the RITM but the value is empty under Dependent Item however the item_option_new shows the variable question. 

How can I get the data to populate?

Here is all my code / setup

Transform Map

I do not have any field mappings in my Transform map. My target table is sc_req_item. 

I have the following onBefore Transform Script

{( function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

 var cartId = GlideGuid.generate(null);

var cart = new Cart(cartId);

var item = cart.addItem('3c61bf46db230010a694e9c948961946'); //sys_id of the catalog Item 

 // set variables

cart.setVariable(item, 'backordered', null);

cart.setVariable(item, 'backordered', null);

cart.setVariable(item, 'price', null);

cart.setVariable(item, 'quantity', 1);

cart.setVariable(item, 'assigned_to', source.u_assigned_to);

cart.setVariable(item, 'contact_type', source.u_contact_type);

cart.setVariable(item, 'delivery_plan', source.u_delivery_plan);

cart.setVariable(item, 'description', source.u_description);

cart.setVariable(item, 'impact', source.u_impact);

cart.setVariable(item, 'location', source.u_location);

cart.setVariable(item, 'opened', source.u_opened);

cart.setVariable(item, 'opened_by', source.u_opened_by);

cart.setVariable(item, 'priority', source.u_priority);

cart.setVariable(item, 'requested_for', source.u_requested_for);

cart.setVariable(item, 'short_description', source.u_short_description);

cart.setVariable(item, 'special_instructions', source.u_special_instructions);

cart.setVariable(item, 'state', source.u_state);

cart.setVariable(item, 'urgency', source.u_urgency);

cart.setVariable(item, 'work_end', source.u_work_end);

cart.setVariable(item, 'wf_activity', source.u_wf_activity);

cart.setVariable(item, 'comments_and_work_notes', source.u_comments_and_work_notes);

var request = cart.placeOrder();

var ritm_sysID = "";

// get the created child RITM record

var ritm = new GlideRecord('sc_req_item');

ritm.addQuery("request", request.sys_id);

ritm.query();

// update the RITM's fields accordingly

while (ritm.next()) {

ritm.opened_by = source.opened_by;

ritm.watch_list = source.watch_list;

///////// Populate Variable Ownership

var rec = new GlideRecord('sc_item_option_mtom');

rec.addQuery('request_item', current.sys_id);

rec.query();

while(rec.next()){

var itemVars = new GlideRecord('sc_item_option_mtom');

itemVars.addQuery('request_item', '!=', current.sys_id);

itemVars.addQuery('request_item.request', current.request);

itemVars.addQuery('sc_item_option.item_option_new.name', rec.sc_item_option.item_option_new.name.toString());

itemVars.query();

while(itemVars.next()){

copyVariableValue(itemVars.sc_item_option, rec.sc_item_option.value.toString());

}

}

////////////////////////////////////////////////////////////////////////////////////////////////////

ritm_sysID = ritm.update();

}

ignore = true;

function copyVariableValue(variable, value){

var itemVar = new GlideRecord('sc_item_option');

itemVar.get(variable);

itemVar.value = value;

 // Update our RITM record and capture its sys_id

itemVar.update();

}

})(source, map, log, target);}

--------------------------------------------------------------------------------------------------------

My Record Producer details

Connected to the Data Source [sys_data_source] table

The Script

var transformMapSysIDs = '23d3f8aedb630010a694e9c948961906';

current.name = gs.getUserName() + " UserImport at: " + new GlideDateTime();
current.import_set_table_name = 'u_dvh_version_2';//Name of your import table
current.file_retrieval_method = "Attachment";
current.type = "File";
current.format = "Excel";
current.header_row = 1;
current.sheet_number = 1;
current.insert();//Need this since we want to load and transform directly

//Now it time to load the excel file into the import table

var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

//Time to run the the transform with the transform map
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapSysIDs);
transformWorker.setBackground(true);
transformWorker.start();

//To avoid to create another data source we abort the RP insert.
current.setAbortAction(true);

[Danie3]

Hi Michael

Thank you for your feedback.

From your link and going down some rabbit holes I managed to get something together that creates a new Request and RITM from each row in the excel spreadsheet. Perfect very happy does what I need it to do. 

Yes there is a however. The variables are empty. The excel file does not bring over the data from the file and populate the variables. I confirmed this as even the sc_item_option_mtom table shows the RITM but the value is empty under Dependent Item however the item_option_new shows the variable question. 

How can I get the data to populate?

Here is all my code / setup

Transform Map

I do not have any field mappings in my Transform map. My target table is sc_req_item. 

I have the following onBefore Transform Script

{( function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

 var cartId = GlideGuid.generate(null);

var cart = new Cart(cartId);

var item = cart.addItem('3c61bf46db230010a694e9c948961946'); //sys_id of the catalog Item 

 // set variables

cart.setVariable(item, 'backordered', null);

cart.setVariable(item, 'backordered', null);

cart.setVariable(item, 'price', null);

cart.setVariable(item, 'quantity', 1);

cart.setVariable(item, 'assigned_to', source.u_assigned_to);

cart.setVariable(item, 'contact_type', source.u_contact_type);

cart.setVariable(item, 'delivery_plan', source.u_delivery_plan);

cart.setVariable(item, 'description', source.u_description);

cart.setVariable(item, 'impact', source.u_impact);

cart.setVariable(item, 'location', source.u_location);

cart.setVariable(item, 'opened', source.u_opened);

cart.setVariable(item, 'opened_by', source.u_opened_by);

cart.setVariable(item, 'priority', source.u_priority);

cart.setVariable(item, 'requested_for', source.u_requested_for);

cart.setVariable(item, 'short_description', source.u_short_description);

cart.setVariable(item, 'special_instructions', source.u_special_instructions);

cart.setVariable(item, 'state', source.u_state);

cart.setVariable(item, 'urgency', source.u_urgency);

cart.setVariable(item, 'work_end', source.u_work_end);

cart.setVariable(item, 'wf_activity', source.u_wf_activity);

cart.setVariable(item, 'comments_and_work_notes', source.u_comments_and_work_notes);

var request = cart.placeOrder();

var ritm_sysID = "";

// get the created child RITM record

var ritm = new GlideRecord('sc_req_item');

ritm.addQuery("request", request.sys_id);

ritm.query();

// update the RITM's fields accordingly

while (ritm.next()) {

ritm.opened_by = source.opened_by;

ritm.watch_list = source.watch_list;

///////// Populate Variable Ownership

var rec = new GlideRecord('sc_item_option_mtom');

rec.addQuery('request_item', current.sys_id);

rec.query();

while(rec.next()){

var itemVars = new GlideRecord('sc_item_option_mtom');

itemVars.addQuery('request_item', '!=', current.sys_id);

itemVars.addQuery('request_item.request', current.request);

itemVars.addQuery('sc_item_option.item_option_new.name', rec.sc_item_option.item_option_new.name.toString());

itemVars.query();

while(itemVars.next()){

copyVariableValue(itemVars.sc_item_option, rec.sc_item_option.value.toString());

}

}

////////////////////////////////////////////////////////////////////////////////////////////////////

ritm_sysID = ritm.update();

}

ignore = true;

function copyVariableValue(variable, value){

var itemVar = new GlideRecord('sc_item_option');

itemVar.get(variable);

itemVar.value = value;

 // Update our RITM record and capture its sys_id

itemVar.update();

}

})(source, map, log, target);}

--------------------------------------------------------------------------------------------------------

My Record Producer details

Connected to the Data Source [sys_data_source] table

The Script

var transformMapSysIDs = '23d3f8aedb630010a694e9c948961906';

current.name = gs.getUserName() + " UserImport at: " + new GlideDateTime();
current.import_set_table_name = 'u_dvh_version_2';//Name of your import table
current.file_retrieval_method = "Attachment";
current.type = "File";
current.format = "Excel";
current.header_row = 1;
current.sheet_number = 1;
current.insert();//Need this since we want to load and transform directly

//Now it time to load the excel file into the import table

var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(current);
var ranload = loader.loadImportSetTable(importSetRec, current);
importSetRec.state = "loaded";
importSetRec.update();

//Time to run the the transform with the transform map
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapSysIDs);
transformWorker.setBackground(true);
transformWorker.start();

//To avoid to create another data source we abort the RP insert.
current.setAbortAction(true);

[Sumedh Kharode1]

Hi Danie

You can create RITM for specific item by uploading excel by using below code in workflow run script

1. You can create a special catalog item for "bulk upload"

2.You need to create a transform map to sc_request

 - In transform script you can create RITM and map the field values to RITM variables

3. You can just refer the below code to use that transform map in workflow run script , below code is creating data source 

var transformMapIDs = "SysID of Transform Map";
var applicatonScope = "Global";
var abc = new GlideRecord("sys_data_source");
abc.initialize();

// Create the datasource record

abc.name = "File import from:" + current.number;
abc.file_retrieval_method = "Attachment";
abc.type = "File";
abc.format = "Excel"; // For Excel Files
//abc.format = "CSV"; // For CSV Files
abc.header_row = 1;
abc.sheet_number = 1;
abc.sys_package.setDisplayValue(applicatonScope);
abc.sys_scope.setDisplayValue(applicatonScope);
var dataSourceID = abc.insert();
workflow.scratchpad.dataSourceID = dataSourceID;
workflow.scratchpad.reqId = current.sys_id;
workflow.scratchpad.transformMapIDs = transformMapIDs;

workflow.info("data "+workflow.scratchpad.dataSourceID);
workflow.info("data2 "+workflow.scratchpad.reqId);

4. Attachment is uploaded to data source and it is processed using transform map

var dataSource = new GlideRecord("sys_data_source");
dataSource.get(workflow.scratchpad.dataSourceID);

var attachmentRec = new GlideRecord("sys_attachment");
attachmentRec.addQuery("table_sys_id", workflow.scratchpad.reqId);
attachmentRec.query();
if (attachmentRec.next()) {
var oldAttachmentID = attachmentRec.getValue("sys_id");
// var abc = new GlideSysAttachment.getContent(oldAttachmentID);
// workflow.info(abc);
var att = new GlideSysAttachment.copy('sc_req_item', workflow.scratchpad.reqId, 'sys_data_source', workflow.scratchpad.dataSourceID);

}

// Process data source file
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSource);
var ranload = loader.loadImportSetTable(importSetRec, dataSource);
importSetRec.state = "loaded";
importSetRec.update();

workflow.scratchpad.importset = importSetRec.sys_id.toString();
// Transform import set
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, workflow.scratchpad.transformMapIDs);
transformWorker.setBackground(true);
transformWorker.start();

Regards

Sumedh Kharode,

Dxsherpa Technologies Pvt Ltd