Bulk Catalog item request using an excel file

[Edxavier Robert]

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);

[Chaitanya ILCR]

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

[Edxavier Robert]

Hi @Chaitanya ILCR , 

How can I achieve that, any suggestions? 

[Chaitanya ILCR]

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

 

[Chaitanya ILCR]

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