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

[Edxavier Robert]

@Chaitanya ILCR  - I was able to add the value to the fields, but having issues with the multi-row variable set. No able to pull the data into it. 

[Edxavier Robert]

@Chaitanya ILCR I have created this script included where I'm able to add the variables and just one to the mrvs. Since the user has the option to add multiples equipment I don't know how to handle the loop. 

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();
                    var cartId = GlideGuid.generate(null);
                    var cart = new Cart(cartId);

                    // Add the catalog item to the cart
                    var item = cart.addItem(catalogItemSysId);

                    // Handle req_for_employee_name field
                    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; // Skip this row if the user is not found
                    }

                    // Handle state field
                    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);
                    }

                    // Set other variables from the row data
                    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);

                    // Create sc_item_option records
                    this.createItemOptions(item, row, catalogItemSysId);

                    // Place the order
                    var rc = cart.placeOrder();
                    gs.info("Request created: " + rc.number);

                    // Get the sys_id of the newly created Request
                    var requestSysId = rc.sys_id;

					gs.info("Request SysID " + requestSysId + " RC: " +rc.sys_id);

                    // Check if requestSysId is defined
                    if (!requestSysId) {
                        gs.error("Error: requestSysId is not defined for the created request.");
                        continue; // Skip further processing if requestSysId is not available
                    }

                    // Update the Request's requested_for and opened_by fields
                    this.updateRequestAndRITM(requestSysId, userGr.sys_id);

					// Handle MRVS for 'please_add_all_the_equipment_s_you_are_returning_with_asset_tags_serial_numbers'
                    this.createMRVS(item, row.equipment, row.asset_tags_serial_number_of_the_equipment, catalogItemSysId, requestSysId);
                }
            } else {
                gs.error("Error parsing Excel: " + parser.getErrorMessage());
            }
        } catch (ex) {
            gs.error("Exception caught while processing Excel: " + ex.message);
        }
    },
//Handle the MRVS and the variables on RITM
createMRVS: function(item, equipment, assetTag, catalogItemSysId, requestSysId) {

	// MRVS Data
    var mrvsData = [];
    if (equipment && assetTag) {
        mrvsData.push({
            "equipment": equipment,
            "asset_tags_serial_number_of_the_equipment": assetTag
        });
    } else {
        gs.warn("Missing equipment or asset tag data.");
        return;
    }

    // Fetch the RITM based on the requestSysId from updateRequestAndRITM
    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;
    }

    // Get variable set and options
    var varSet = '1b88b6421b0802907a7864a8624bcb22'; // Variable set Sys ID - Please add all the equipment(s) you are returning with asset tags/serial numbers
    var equipVar = '8a522246c3911210c662b7ddd40131e8'; // Equipment variable Sys ID Equipment Variable
    var assetVar = '58eab6c21b0802907a7864a8624bcbf4'; // Asset tag variable Sys ID Asset tags/serial number of the equipment

    // Create sc_item_option for "equipment" (once for all rows)
    var itemOption = new GlideRecord('sc_item_option');
    itemOption.initialize();
    itemOption.setValue('request_item', ritmGr.sys_id); // Set RITM
    itemOption.setValue('item_option_new', equipVar); // Associate with "equipment" variable
    itemOption.setValue('value', mrvsData[0].equipment); // Store the first equipment value
    var equipmentOptionSysId = itemOption.insert(); // Insert record and reuse this Sys ID for all rows

    // Create sc_item_option for "asset_tags_serial_number_of_the_equipment" (once for all rows)
    itemOption.initialize();
    itemOption.setValue('request_item', ritmGr.sys_id); // Set RITM
    itemOption.setValue('item_option_new', assetVar); // Associate with "asset tag" variable
    itemOption.setValue('value', mrvsData[0].asset_tags_serial_number_of_the_equipment); // Store the first asset tag value
    var assetOptionSysId = itemOption.insert(); // Insert record and reuse this Sys ID for all rows

    // Process each row for MRVS
    var rowIndex = 0;
    mrvsData.forEach(function(row) {
        // Create MRVS record for equipment
        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); // Use fetched RITM Sys ID
        mrvsRecord.setValue('item_option_new', equipVar); // Set "equipment" variable
        mrvsRecord.setValue('value', row.equipment);
        mrvsRecord.setValue('sc_item_option', equipmentOptionSysId); // Reuse sc_item_option for equipment
        mrvsRecord.setValue('row_index', rowIndex); // Associate with correct row index
        mrvsRecord.insert();

        // Create MRVS record for asset tag
        mrvsRecord.initialize();
        mrvsRecord.setValue('variable_set', varSet);
        mrvsRecord.setValue('parent_table_name', 'sc_req_item');
        mrvsRecord.setValue('parent_id', ritmGr.sys_id); // Use fetched RITM Sys ID
        mrvsRecord.setValue('item_option_new', assetVar); // Set "asset tag" variable
        mrvsRecord.setValue('value', row.asset_tags_serial_number_of_the_equipment);
        mrvsRecord.setValue('sc_item_option', assetOptionSysId); // Reuse sc_item_option for asset tag
        mrvsRecord.setValue('row_index', rowIndex); // Associate with correct row index
        mrvsRecord.insert();

        // Increment rowIndex for each row
        rowIndex++;
    });

    gs.info("MRVS records created successfully.");
},

// end of code

    updateRequestAndRITM: function(requestSysId, requestedForSysId) {
		gs.log("Inside updateRequestAndRITM: " + requestSysId + "Another: "+ 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'
};

[Edxavier Robert]

Hi @Chaitanya ILCR , 

Thanks for your suggestion, where I should this example code? in Flow Designer?

[Edxavier Robert · 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'
};