How to Count Variables with Value in MRVS

[jmiskey]

I have kind of a tricky situation.  I have a Catalog Item that when submitted (from the Service Portal), automatically creates and pre-populates a MRVS.  Then, via the workflow, a Task is assigned to a particular team to complete the filling out of the MRVS which has been pre-populated.

Before they close the Task, I want to make sure that they have populated a particular variable in all the records.  I tried to make that variable mandatory, but that is not invoked unless they edit the record.  If they choose NOT to edit it, it is not enforced, so they are able to close the Task without doing all the required work.

I first tried looping through all the records to see if I could find any that do not have a value in that particular field, but was unable to get that to work (question can be seen here: https://community.servicenow.com/community?id=community_question&sys_id=dafff530dbad1050190dfb243996...).

So, I thought maybe coming at this a different way.  Instead of trying to loop through and check each record, maybe it would be better just to get aggregate record counts.  I found this solution here by @Jace Benson : https://community.servicenow.com/community?id=community_question&sys_id=51bc6b44dba1bb0023f4a345ca96..., which shows how to get the total count of all the records in the MRVS.  Is there a way to easily get the total count of all records where a patricular variable is not empty/blank?

If I were table to get those two counts, then I could just compare them, and require those numbers to be equal before allowing them to close the Task.

Thanks

[Brad Bowman]

I haven't determined the rhyme or reason, but sometimes in the mrvs JSON I get sys_ids instead of variable names.  Add an alert like this after your var obj to confirm this

alert(JSON.stringify(obj));

[jmiskey]

Yes, it is showing the sys_id for the "has_account" variable.  In earlier testing I was doing, I noticed that if I returned those alerts on the Catalog Item submission (on the Service Portal), it would show the Variable name.  But when I return those alerts on the Catalog Task, it shows their sys_ids.

So, knowing that, what adjustments do we need to make to get this to run? 

[Brad Bowman · Accepted solution]

I know I've used the array of one variable before, but I can't remember where - might have been server-side where everything MRVS is easier.  I can't find the right syntax now, so new approach.  First, this will get you the JSON with the variable name in place of the sys_id

var secApp = g_form.getValue('security_applications');
secApp = secApp.toString().replace(/variable_sys_id/g,'has_account');

//get total record count
var obj = JSON.parse(secApp);

Then just loop through the array of objects to count the populated values

var count = 0;
for(var i=0;i<obj.length; i++){  
 if(obj[i].has_account != ''){
  count ++;
 }
}
alert (count);

[jmiskey]

Unfortunately, it is still not working.  I have 36 records in the MRVS, and I updated just the first 3 and tried to close the Task.  It returns 36 total records (which is correct), but then it also returned that I had 36 populated records (incorrect, should be three).

So, I tried to do some debugging, and had it pop-up an alert for every records with the value, like this:

function onSubmit(){

	var secApp = g_form.getValue('security_applications');
	secApp = secApp.toString().replace(/variable_sys_id/g,'has_account');

	//get total record count
	var obj = JSON.parse(secApp);
	var totRec = obj.length;
	alert('Total Count: ' + totRec);

	var totPop = 0;
	for(var i=0;i<obj.length; i++){  
		if(obj[i].has_account != ''){
			alert('Value: '+ obj[i].has_account);
			totPop ++;
		}
	}
	alert("Total populated: " + totPop);

}

and it returned "undefined" for every record, even the ones that I did update.

This "has_account" variable is a Multiple Choice variable, with no default value selected.  I was wondering if that variable type might be part of the problem, so I tried changing it to use my "user_id" field, which is a Single Line Text field.  That was not any better, it was returning that all 36 records were being populated, even though I only updated 2.

[Brad Bowman]

You did replace 'variable_sys_id' inside the slashes with the sys_id of has_account, correct?  Sorry I forgot to point that out.  If you did, or if you're still getting undefined after you do, put the alert(JSON.stringify(obj)); back in to see why it can't find the value of each has_account name.  I didn't test this with a multiple choice variable, but it should at least be showing whatever the value is.