Scripting the Multi-row Variable Set

[Brad Tilton]

In my last post, we created a multi-row variable set, added variables, then showed it in action by filling out the variables and checking out. In this post we'll use some of the new methods described here to access and set values. For the purposes of this post we're going to be using background scripts (which received some new functionality in London) in the global scope. 

The first thing we'll look at is the JSON object returned by referencing the MRVS. In the following script we're using the sys_id of therequest item ordered in the last post to get the GlideRecord object for that request item. Then we're populating the mrvs variable with the access_list variable set.

var mrvs;
var itemID = '70506da8db002300e69dfbef2996194a';
var ritmGR = new GlideRecord('sc_req_item');
if (ritmGR.get(itemID)) {
    mrvs = ritmGR.variables.access_list;
}
gs.print(mrvs);

Returns:

*** Script: [ {
  "application" : "829e953a0ad3370200af63483498b1ea",
  "access_level" : "read"
}, {
  "application" : "2811a2efc0a8000b0069bb464f215ff5",
  "access_level" : "full",
  "business_justification" : "This field is mandatory because I selected full access"
}, {
  "application" : "28110ea1c0a8000b003abee48ecbc3fa",
  "access_level" : "read_write"
} ]

The first thing I noticed was that it appears that only variables with values are included in the json object as the first and third elements don't have a business_justification. From here, we can get more granular.

NOTE: In order to save space the following scripts will assume we have the variable mrvs from the first script above that represents the multi-row variable set. 

//get all the values in a single column
gs.print(mrvs.application);
//get the number of rows in the multi-row variable set
gs.print(mrvs.getRowCount());

Returns:

*** Script: [829e953a0ad3370200af63483498b1ea, 2811a2efc0a8000b0069bb464f215ff5, 28110ea1c0a8000b003abee48ecbc3fa]
*** Script: 3

Based on this we see that we can use the variableset.variablename will give us all of the values from the variables column in an array. This could be useful if we wanted to do some sort of check against the applications before looking at the level of access needed. We also see that we have the familiar getRowCount() to use to see how many rows are in the MRVS. We can also usevariableset = <JSON Object> and variableset.variablename = <Array of values> to set values or the whole variable set or all rows in a column.

Now we'll use getRowCount and getRow to iterate through the rows, and then getRow to print the values of our application and access_level variables:

var rowCount = mrvs.getRowCount();
for (var i = 0; i < rowCount; i++) {
	var row = mrvs.getRow(i);
	var app = row.application;
	var accessLevel = row.access_level;
	gs.print(app + ' ' + accessLevel);
}

Returns:

*** Script: 829e953a0ad3370200af63483498b1ea read
*** Script: 2811a2efc0a8000b0069bb464f215ff5 full
*** Script: 28110ea1c0a8000b003abee48ecbc3fa read_write

Now we're going to use addRow to add a row and populate the variables in the row using setCellValue and setting it directly using the row objects property for the variable:

var newRow = mrvs.addRow();
//set the value using row.setCellValue('<var_name>',value)
newRow.setCellValue('application', '829e953a0ad3370200af63483498b1ea');
//set the value using row.<var_name> = value
newRow.access_level = 'read_write';
gs.print(mrvs);

Returns:

*** Script: [ {
  "application" : "829e953a0ad3370200af63483498b1ea",
  "access_level" : "read"
}, {
  "application" : "2811a2efc0a8000b0069bb464f215ff5",
  "access_level" : "full",
  "business_justification" : "This field is mandatory because I selected full access"
}, {
  "application" : "28110ea1c0a8000b003abee48ecbc3fa",
  "access_level" : "read_write"
}, {
  "application" : "829e953a0ad3370200af63483498b1ea",
  "access_level" : "read_write"
} ]

We now have a fourth row in the catalog table variable with the values we set in the script. Notice that both methods worked to set the values. There's also a method for deleting a row, but I'm usually against deleting anything in ServiceNow so I'm not going to use it here. 

Lastly, I wanted to post the notes and limitations from the docs article here as they're important to know when working with the MRVS:

1. You can only set a variable in a before business rule. Variables set in an after rule are not written to the database.
2. There is nothing in place to prevent namespace collision with variables. Creating two variables named computer_speed would result in only one of them showing up; the second one would overwrite the first one.
3. Date/time variables use the same time zone formatting and storage rules as all other dates in the system. They are stored internally in GMT, but translated into the user's local time zone and format for display.

I hope this post was helpful as you're exploring how to use the values from London's new Multi-row variable set in your workflows, scheduled jobs, and business rules.

[cloudyrobert]

Did you get the functionality in this post working? I've got an adaptation of it running onChange, and it seems to be behaving as desired in the service portal. Now, to figure out how to present a total... don't suppose you've solved that one in your financial services use cases? I'm not seeing any clean options yet.

[dianemiro]

Hi Rick,

How do you display the display value of a row? I used the script you provided above but it displays the value (sys_id) of the rows. Do you know how to print the display value instead? Thank you.

Diane

[brian_]

I have posted a script to get the display value, near the very bottom of this thread (it has grown quite long). It is currently the 2nd from the bottom reply.

[nyancer3]

If you want to do validation on the number of items in your multi-row variable set, you can run a client script on the Catalog Item (not within the MRV). You can use an OnSubmit client script on the item, use g_form.getValue() to get a JSON string representing the entire set (it is an array), parse the JSON, and then use hte .length attribute of the array to do your validate.

If you want to require at least one entry, then check that length > 0. If you want to set a limit, then check that the length is less than the limit you want to set.

For at least one entry, you can also use a UI Policy action targeting the variable set and make it mandatory if you don't want to use the scripting option.

So:

// Get the multi-row set data

var mrv = JSON.parse(g_form.getValue('[name of the variable set goes here]'));

// This should give you an array. If there are no rows, it's an empty array.

// To set a min:

if (mrv.length < 1){

  // Let the user know
  g_form.addErrorMessage('You must make at least one entry for [label goes here]');
  // Cancel submission
  return false;

}

// Set Max, in this example, 5.

var setLimit = 5;

if (mrv.length > setLimit){

  // let the user know
  g_form.addErrorMessage('You may not enter more than ' + setLimit + ' items into []label goes here].');
  return false;

}

Hope that helps.

[cloudyrobert]

Hi Howard--did you make any progress on this topic? Running into some challenges too.

[cloudyrobert]

Hi Adam--do you have a KB/PRB number for this, by chance? I'm wondering if this could be the source of the challenges I'm running into (Madrid Patch 2).

[dianemiro]

Thanks Bryan, got it.

[priyadharisini]

The content of the multi row variable set is not being displayed in an approval task or in the email notification. Is there a way to get them ?

[brian_]

The documentation states it is not available in the variable summarizers, so that is expected behavior. Unfortunate, but expected.

What we have done to workaround this is add a text box, and populate it through a script in the workflow (or an onSubmit could work as well). There are plenty of examples here, including one I've posted 2 posts up in this thread.

[Vika1]

Hi Kuldeep,

I have the same requirement.

Did you find the solution?