To auto-populate data in MRVS (Multi-Row Variable Sets) in ServiceNow, you can use client scripts or server side script depending on your requirement. In this blog i will explain both way to auto populate MRVS.
Lets talk about first scenario that we have to auto populate MRVS data while loading the form.
We can write onload catalog client script and use below code.
function onLoad() { //Type appropriate comment here, and begin script below var data = [{ "MaterialID": "10000", "Description": "Test", "Quantity": 4 }, { "MaterialID": "100001", "DescriptionID": "test2", "Quantity": 1 } ]; // Get the MRVS variable name var mrvsVariableName = 'product_parameters'; // Replace with your actual variable name var newRecord = []; // Loop through the array and add records to the MRVS data.forEach(function(item) { // Create a new record in the MRVS newRecord.push({ "material_id": item.MaterialID, "description": item.Description, "quantity": item.Quantity }); // Push the new record to the MRVS g_form.setValue(mrvsVariableName, JSON.stringify(newRecord)); }); }
Explanation:
- First you have to create one array object and assign the key pair value. In this example i have taken “data” statically but you can get it dynamically while calling the script include but while returning data from script include you have to make sure that array data format should be same.
- Run for loop to push data in a array with actual key (material_id,description and quantity are variable name) value pair.
- Finally set the value using setValue() function while passing the mrvs name and array object.
Output:
How to read excel data in script include and pass to client script.
- Create one client script and call the script include using Ajax call and store the arrayData in a variable called “data”.
- Create one script include and use below code for reading the excel sheet.
var mrvs = Class.create(); mrvs.prototype = { initialize: function() {}, readExcelData: function() { var mrvsArray = []; var attid = this.getParameter('sysparm_attachment'); //newValue passed in from Client Script var imp = new sn_impex.GlideExcelParser(); var attachment = new GlideSysAttachment(); var attachmentStream = attachment.getContentStream(attid); imp.parse(attachmentStream); var headers = imp.getColumnHeaders(); while (imp.next()) { var row = imp.getRow(); mrvsArray.push({ 'material_id': row['MaterialID'], 'description': row['Description'], 'quantity': row['Quantity'], }); } return mrvsArray; }, type: 'mrvs' };
3. Now you are getting data dynamically from excel in required format, so you can avoid below code from your client script.
var data = [{ "MaterialID": "10000", "Description": "Test", "Quantity": 4 }, { "MaterialID": "100001", "DescriptionID": "test2", "Quantity": 1 } ];
4. Rest other code will work as expected.
Note: You can use script include code in any server side script like in Business rule. Based on your requirement you can leverage the code for reading excel data.