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

[sachin_namjoshi]

you can configure  onSubmit client script to check the number of rows added to a MRVS:

e.g This is a list of serial numbers, where serial_number is the internal name of the MRVS

var snlist = g_form.getValue('serial_number');
var obj = JSON.parse(snlist);
var length = obj.length;

Regards,

Sachin

[jmiskey]

Sachin,

That is the same thing as the link I referenced from Jace Benson.  That part is not the problem (I already got that part from Jace's post).  As I said, the part that I need to figure out now is: 

get the total count of all records where a patricular variable is not empty/blank?

For example, let's I have a MRVS named "security_applications", which has three variables in it, named "application","has_account", and "user_id", and it has data in it like this:

I can use the method that Jace (and you) proposed to count 5 total records in my MRVS.

However, I also need to know the number of records where "has_account" is not empty (or blank), which in the example above is 3.

It is the later number (3) that I am asking for help on.  How do you count records based on a specific criteria using JSON?

[Brad Bowman]

You can create a separate array containing all of one variable in a MRVS.

var mrvs = g_form.getValue('security_applications');
var accounts = mrvs.has_account;

Will give you an array = Yes, No, Yes, so you can then use accounts.length

[jmiskey]

Brad,

Thanks for the reply.  I think we are on the right track, but I cannot quite get it to work properly (I must confess that I am fairly new to JSON, so I am sure it is probably something I am doing wrong).  

Here is my script:

function onSubmit(){

	var secApp = g_form.getValue('security_applications');

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

	//get populated record count
	var accounts = secApp.has_account;
	var popRec = accounts.length;
	alert('Populate Row Count: ' + popRec);

}

When I go to close the Task, it correctly returns the first alert, telling me how many records I have in my MRVS.  However, I get an error on the second alert that says:

"Error MessageonSubmit script error: TypeError: Cannot read property 'length' of undefined:"

Any idea what I an doing wrong?

Thanks