- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2024 10:16 AM
Hi All,
Wondering if is possible using an excel file submit multiple request for a catalog item.
Example: I have a catalog item with multiple questions, single lines, dropdown questions, mvrs etc. I want to import an excel file with the responses of those questions and create multiple one request and ritm for each row in the excel file.
I was reading that I could use a data source and create a transform map, but I'm confuse which table I need to use in the transform map. Also, was reading that I will need to create a transform script onAfter to parse the information and create the deferents RITM.
I have something like this, but this create just the RITM and the request and also not creating the variables:
(function runTransformScript(source, map, log, target /*undefined onStart*/) {
// Step 1: Get the sys_id of the created request item (RITM)
var reqItemSysId = target.sys_id;
// Step 2: Map standard single catalog item variables from the source table (u_bulk_return_request)
var standardVariables = {
"requested_for": source.requested_for,
"u_boxes": source.u_boxes,
"u_label": source.u_label,
"u_organization": source.u_organization,
"u_email": source.u_email,
"u_phone": source.u_phone,
"u_additional_info": source.u_additional_info
};
// Step 3: Insert the standard catalog variables into sc_item_option for this RITM
for (var varName in standardVariables) {
var varValue = standardVariables[varName];
if (varValue) {
var itemOption = new GlideRecord('sc_item_option');
itemOption.initialize();
itemOption.request_item = reqItemSysId; // Link to the request item
itemOption.item_option_new.setDisplayValue(varName); // Set the variable name
itemOption.value = varValue; // Set the variable value from the source table
itemOption.insert();
// Link the variable to the request item in sc_item_option_mtom
var itemOptionMtom = new GlideRecord('sc_item_option_mtom');
itemOptionMtom.initialize();
itemOptionMtom.request_item = reqItemSysId; // Link to the request item
itemOptionMtom.sc_item_option = itemOption.sys_id; // Link to the variable (sc_item_option)
itemOptionMtom.insert();
}
}
// Step 4: Loop through and insert MRVS data for 'please_add_all_the_equipment_s_you_are_returning_with_asset_tags_serial_numbers'
var equipmentData = []; // Array to hold equipment data for MRVS
if (source.u_equipment && source.u_asset_tag) {
// Assuming you can have multiple equipment and asset tags in the source data
equipmentData.push({
u_equipment: source.u_equipment,
u_asset_tag: source.u_asset_tag
});
}
// Insert equipment data into the MRVS
for (var i = 0; i < equipmentData.length; i++) {
var mrvsRow = new GlideRecord('sc_multi_row_question_answer');
mrvsRow.initialize();
mrvsRow.setValue('question_id', 'ae73427597915a10a4bc7e70f053af5e'); // Use actual question ID for equipment
mrvsRow.setValue('answer', equipmentData[i].u_equipment);
mrvsRow.setValue('asset_tag', equipmentData[i].u_asset_tag);
mrvsRow.setValue('request_item', reqItemSysId); // Link to the request item
mrvsRow.insert();
}
// Step 5: Loop through and insert MRVS data for 'person_address'
var addressData = []; // Array to hold address data for MRVS
if (source.u_address_1 && source.u_city) {
addressData.push({
u_address_1: source.u_address_1,
u_address_2: source.u_address_2,
u_city: source.u_city,
u_state: source.u_state,
u_zip: source.u_zip
});
}
// Insert address data into the MRVS
for (var j = 0; j < addressData.length; j++) {
var mrvsAddressRow = new GlideRecord('sc_multi_row_question_answer');
mrvsAddressRow.initialize();
mrvsAddressRow.setValue('question_id', 'df69567797924e101d8fb42ad053afb0'); // Use actual question ID for address
mrvsAddressRow.setValue('answer', addressData[j].u_address_1); // Adjust as necessary
mrvsAddressRow.setValue('address_2', addressData[j].u_address_2);
mrvsAddressRow.setValue('city', addressData[j].u_city);
mrvsAddressRow.setValue('state', addressData[j].u_state);
mrvsAddressRow.setValue('zip', addressData[j].u_zip);
mrvsAddressRow.setValue('request_item', reqItemSysId); // Link to the request item
mrvsAddressRow.insert();
}
})(source, map, log, target);
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-25-2024 01:06 PM
I was able to fix my issue by modifying the code to do a loop and pair each column. Here is the final code just in case someone has similar use case:
var ExcelToCart = Class.create();
ExcelToCart.prototype = {
initialize: function() {},
processExcelAndCreateCart: function(attachmentSysId, catalogItemSysId) {
try {
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream(attachmentSysId);
if (!attachmentStream) {
gs.error("Attachment not found or unable to read stream for sys_id: " + attachmentSysId);
return;
}
parser.setSource(attachmentStream);
if (parser.parse()) {
var headers = parser.getColumnHeaders();
while (parser.next()) {
var row = parser.getRow();
// Prepare equipment array for MRVS
var equipmentArray = [];
for (var i = 1; i <= 2; i++) {
var equipment = row['equipment_' + i];
var assetTag = row['asset_tags_serial_number_of_the_equipment_' + i];
if (equipment || assetTag) {
equipmentArray.push({
"equipment": equipment,
"asset_tags_serial_number_of_the_equipment": assetTag
});
}
}
var cartId = GlideGuid.generate(null);
var cart = new Cart(cartId);
var item = cart.addItem(catalogItemSysId);
// Set variables
var requestedForUserId = row.requested_for;
var userGr = new GlideRecord('sys_user');
userGr.addQuery('user_name', requestedForUserId);
userGr.query();
if (userGr.next()) {
gs.info("User found: " + requestedForUserId + ", sys_id: " + userGr.sys_id);
cart.setVariable(item, 'req_for_employee_name', userGr.sys_id);
} else {
gs.warn("User not found for userId: " + requestedForUserId);
continue;
}
var stateuser = row.state;
var userState = new GlideRecord('sys_report_map');
userState.addQuery('parent', '6ee51951d7320200bd4a4ebfae610354');
userState.addQuery('name', stateuser);
userState.query();
if (userState.next()) {
gs.info("User State is: " + stateuser + " - " + userState.sys_id);
cart.setVariable(item, 'state', userState.sys_id);
}
cart.setVariable(item, 'zip', row.zip);
cart.setVariable(item, 'do_you_want_the_label_mailed_or_shipped', row.do_you_want_the_label_mailed_or_shipped);
cart.setVariable(item, 'any_additional_information', row.any_additional_information);
cart.setVariable(item, 'city', row.city);
cart.setVariable(item, 'address_1', row.address_1);
cart.setVariable(item, 'do_you_need_boxes', row.do_you_need_boxes);
cart.setVariable(item, 'address_2', row.address_2);
cart.setVariable(item, 'which_organization', row.which_organization);
cart.setVariable(item, 'person_address', row.person_address);
cart.setVariable(item, 'email_address', row.email_address);
cart.setVariable(item, 'phone_number', row.phone_number);
this.createItemOptions(item, row, catalogItemSysId);
var rc = cart.placeOrder();
gs.info("Request created: " + rc.number);
var requestSysId = rc.sys_id;
if (!requestSysId) {
gs.error("Error: requestSysId is not defined for the created request.");
continue;
}
this.updateRequestAndRITM(requestSysId, userGr.sys_id);
// Pass the equipment array to createMRVS
this.createMRVS(item, equipmentArray, catalogItemSysId, requestSysId);
}
} else {
gs.error("Error parsing Excel: " + parser.getErrorMessage());
}
} catch (ex) {
gs.error("Exception caught while processing Excel: " + ex.message);
}
},
createMRVS: function(item, equipmentArray, catalogItemSysId, requestSysId) {
var varSet = '1b88b6421b0802907a7864a8624bcb22'; // Variable set Sys ID
var equipVar = '8a522246c3911210c662b7ddd40131e8'; // Equipment variable Sys ID
var assetVar = '58eab6c21b0802907a7864a8624bcbf4'; // Asset tag variable Sys ID
var ritmGr = new GlideRecord('sc_req_item');
ritmGr.addQuery('request', requestSysId);
ritmGr.query();
if (!ritmGr.next()) {
gs.warn("No RITM found for the given request: " + requestSysId);
return;
}
var itemOption = new GlideRecord('sc_item_option');
itemOption.initialize();
itemOption.setValue('request_item', ritmGr.sys_id);
itemOption.setValue('item_option_new', equipVar);
itemOption.setValue('value', equipmentArray[0].equipment);
var equipmentOptionSysId = itemOption.insert();
itemOption.initialize();
itemOption.setValue('request_item', ritmGr.sys_id);
itemOption.setValue('item_option_new', assetVar);
itemOption.setValue('value', equipmentArray[0].asset_tags_serial_number_of_the_equipment);
var assetOptionSysId = itemOption.insert();
var rowIndex = 0;
equipmentArray.forEach(function(row) {
var mrvsRecord = new GlideRecord('sc_multi_row_question_answer');
mrvsRecord.initialize();
mrvsRecord.setValue('variable_set', varSet);
mrvsRecord.setValue('parent_table_name', 'sc_req_item');
mrvsRecord.setValue('parent_id', ritmGr.sys_id);
mrvsRecord.setValue('item_option_new', equipVar);
mrvsRecord.setValue('value', row.equipment);
mrvsRecord.setValue('sc_item_option', equipmentOptionSysId);
mrvsRecord.setValue('row_index', rowIndex);
mrvsRecord.insert();
mrvsRecord.initialize();
mrvsRecord.setValue('variable_set', varSet);
mrvsRecord.setValue('parent_table_name', 'sc_req_item');
mrvsRecord.setValue('parent_id', ritmGr.sys_id);
mrvsRecord.setValue('item_option_new', assetVar);
mrvsRecord.setValue('value', row.asset_tags_serial_number_of_the_equipment);
mrvsRecord.setValue('sc_item_option', assetOptionSysId);
mrvsRecord.setValue('row_index', rowIndex);
mrvsRecord.insert();
rowIndex++;
});
gs.info("MRVS records created successfully.");
},
updateRequestAndRITM: function(requestSysId, requestedForSysId) {
var requestGr = new GlideRecord('sc_request');
if (requestGr.get(requestSysId)) {
requestGr.setValue('requested_for', requestedForSysId);
requestGr.setValue('opened_by', requestedForSysId);
requestGr.update();
gs.info("Request updated with requested_for: " + requestedForSysId + ", opened_by: " + requestedForSysId);
} else {
gs.warn("Request not found: " + requestSysId);
}
var ritmGr = new GlideRecord('sc_req_item');
ritmGr.addQuery('request', requestSysId);
ritmGr.query();
while (ritmGr.next()) {
ritmGr.setValue('requested_for', requestedForSysId);
ritmGr.setValue('opened_by', requestedForSysId);
ritmGr.update();
gs.info("RITM updated: " + ritmGr.sys_id);
}
},
type: 'ExcelToCart'
};
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2024 10:29 AM
Hi @Edxavier Robert ,
you can combine CartJS and GlideExcelParser to achieve this requirement
I'm adding the documentation links here you can refer these
https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server/sn_sc-namespace/c_CartJSScoped...
https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server/sn_impex/GlideExcelParserScope...
Thanks,
Chaitanya
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2024 10:44 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2024 10:43 PM
Just parse Each Row of the Excel Using GlideExcelParser and build a cartObject with use addtocart method and pass the cartObject to it
I have created a catalog item with two variables (Name and Age) and Uploaded an excel of 2 columns
you can make update it the script according to your need
var parser = new sn_impex.GlideExcelParser();
var attachment = new GlideSysAttachment();
var attachmentStream = attachment.getContentStream('6f94dcd683d11e90c4d11a30ceaad36a'); // attachment sysid
parser.parse(attachmentStream);
gs.info(headers);
var cart;
while (parser.next()) {
cart = new sn_sc.CartJS();
var row = parser.getRow();
var crtObj = {
'sysparm_id': '0081581283d11e90c4d11a30ceaad35c', // catalog item sysid
'sysparm_quantity': '1',
'variables': {
"name": row.Name,
"age": row.Age
}
}
cart.addToCart(crtObj);
gs.info(row.Name + ' ' + row.Age);
}
cart.checkoutCart();
Regards,
Chaitanya
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-18-2024 10:45 PM
or better you can create a multirow variable set and use Excel parser and upload the values to multirow variable set. you can eliminate creation of multiple RITMs