Select to view content in your preferred language

# Return a count of fields that dont have a null value

314
2
06-27-2023 03:55 AM
New Contributor III

I want to have a field that returns a count from 6 different fields based on if there is a value or not. Basically i have a report that has the possibility of collecting field data at 6 different locations but they dont always need to all be used. The field should calculate the number of testing areas based on if the test area name is complete and add them up. This isnt in a repeat so not sure if the count function would work.

1 Solution

Accepted Solutions
by
Occasional Contributor

You can see if a question has a value with the string-length() function.  For each question, if it has a string-length() that is greater than zero, return a 1, else return a zero.  Sum the values returned.

For this example survey:

``````if(string-length(\${TestArea1}) > 0, 1, 0)
+ if(string-length(\${TestArea2}) > 0, 1, 0)
+ if(string-length(\${TestArea3}) > 0, 1, 0)
+ if(string-length(\${TestArea4}) > 0, 1, 0)
+ if(string-length(\${TestArea5}) > 0, 1, 0)
+ if(string-length(\${TestArea6}) > 0, 1, 0)``````

Farley
2 Replies
by
Occasional Contributor

You can see if a question has a value with the string-length() function.  For each question, if it has a string-length() that is greater than zero, return a 1, else return a zero.  Sum the values returned.

For this example survey:

``````if(string-length(\${TestArea1}) > 0, 1, 0)
+ if(string-length(\${TestArea2}) > 0, 1, 0)
+ if(string-length(\${TestArea3}) > 0, 1, 0)
+ if(string-length(\${TestArea4}) > 0, 1, 0)
+ if(string-length(\${TestArea5}) > 0, 1, 0)
+ if(string-length(\${TestArea6}) > 0, 1, 0)``````

Farley
Esri Regular Contributor

Count would not work for that as it is not in a repeat. What I do is use a bunch of if statements to return 1 if each the field is populated or 0 if it is not and then sum them all together.

``if(string-length(\${field1})>0, 1, 0)``

While you can add all of the if statements into one large calculation, I recommend if you are new to Survey123 and/or calculations that you break it up. It is easier to troubleshoot and set up that way. Add a hidden integer question for each field you want counted. Add the if statement to calculate if it is populated or not. Then add a final hidden field to sum up all of the calculated fields you just added.

- Jen