Bulk Catalog item request using an excel file

Edxavier Robert
Mega Sage

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);
1 ACCEPTED SOLUTION

@Chaitanya ILCR 

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'
};

View solution in original post

8 REPLIES 8

Hi @Chaitanya ILCR , 

 

How can I achieve that, any suggestions? 

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

 

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