How to get a count of fields with same choice selected on a form?

[Ronak3]

Hi Friends,

I am working on a requirement to do percentage calculation out of the choice field values selected. I need to get the count of all the fields with choice selected as N/A and I want to use a business rule for this. If I can get this count, I can get do average and also percentage calculation.

I have tried to find solution in our community but failed. I haven't worked on such count before so please shed some light on how to achieve this.

[DrewW]

Ah, ok then you just need to add to the code sample the total and then do a total/count at the end.

So something like

//This code assumes that the value for the "N/A" selection is a blank string.
var count = 0;
var total = 0;
if(!current.CHOICE_FIELD1){
	count += 1;
	total += parseInt(current.getValue("CHOICE_FIELD1"));
}
if(!current.CHOICE_FIELD2){
	count += 1;
	total += parseInt(current.getValue("CHOICE_FIELD2"));
}

if(!current.CHOICE_FIELD2){
	count += 1;
}	total += parseInt(current.getValue("CHOICE_FIELD3"));

//.....

if(!current.CHOICE_FIELD10){
	count += 1;
	total += parseInt(current.getValue("CHOICE_FIELD10"));
}

//You  may want to do some rounding here
current.setValue("% FIELD", (total / count * 100));

[Abilash Kankip1]

When you say

if(!current.CHOICE_FIELD1)

is it checking for current choice field name or the choice of that field?
Why I am asking because the value of choice "N/A" is not empty string but for ex. "field N/A"

[DrewW]

CHOICE_FIELD1 is the name of the field.  So like I said in the code comments I was assuming that "N/A" had a blank value and the label was "N/A".  If that is true then the if statement will check to see if CHOICE_FIELD1 is null or blank and if not include it in the calculation.  If "N/A" has a value set then you will need to check that CHOICE_FIELD1 is not that value.

I also thought of a different way to write the code that may or may not be better.

//This code assumes that the value for the "N/A" selection is a blank string.
var fields = ["CHOICE_FIELD1", "CHOICE_FIELD2", "CHOICE_FIELD3",...., "CHOICE_FIELD10"]
var count = 0;
var total = 0;
fields.forEach(function(field, index){
    //Use this if N/A has a blank value
    if(!current[field]){
        count += 1;
        total += parseInt(current.getValue(field));
    }
    //Otherwise use this
    if(current.getValue(field) != "N/A_OR_WHAT_EVER_IT_IS"){
        count += 1;
        total += parseInt(current.getValue(field));
    }
});

//You  may want to do some rounding here
current.setValue("% FIELD", (total / count * 100));

[Bert_c1]

As far as choice values for "N/A" go

https://[instance].service-now.com/sys_choice_list.do?sysparm_query=GOTOvalue%3DN%2FA&sysparm_view=

only show one table and field where "N/A" is defined as a value in my instance. that is the 'status' field on the 'one_api_service_plan_feature_invocation ' table.  Or do you want to count records from a table that has a Choice field with "Dropdown with --None--" configured, see list:

https://[instance]/sys_dictionary_list.do?sysparm_query=GOTOinternal_type.label%3DChoice%5Echoice%3D...

1 is the value used. See:

https://[instance].service-now.com/sys_choice_list.do?sysparm_query=element%3Dchoice&sysparm_view=

The above may not be needed, all you need is the specific table and field, and use what DrewW says above on using GlideAggregate. You can get AVG, COUNT and SUM and calculate %.

[ruhee]

(function executeRule(current, previous /*null when async*/) {
    var choiceValue = current.field_name; 
    var ga = new GlideAggregate('table_name'); 
    ga.addQuery('field_name', choiceValue); 
    ga.addAggregate('COUNT');
    ga.query();
    var count = 0;
    if (ga.next()) {
        count = ga.getAggregate('COUNT');
    }
    gs.addInfoMessage('Number of fields with choice "' + choiceValue + '": ' + count);
})(current, previous);

Hi Ronak,

Try to use above script in BR