Counting instances of specific responses across many repeats

Question

I am building a survey that my technicians use to evaluate the compliance status of telecom towers. During the course of a tower inspection, a technician may encounter a multitude of deficiencies in a number of categories, such as the foundation, the base plates, structural members, wire tensions, finish, and so on. I use repeats of varying complexity to manage each particular category, but one common feature to all is an assessment of the asset's condition. Two of the responses for asset condition are "hazardous" and "poor". I would like to implement a counter for all instances of either of these choices across all repeats (and all records in every repeat), so that when the technician completes the survey, I can ensure that the number of deficiencies reported in the survey is at least the same as the number of things reported to be in poor or hazardous condition, by setting the number of repeats in that section to the counter.

I have tried multiple methods to implement this, including messy if(selected()) statements, count(selected()) statements, sum statements, and the like. I've consulted a number of posts on this forum regarding this topic, but nothing I have come across seems to do quite what I need. The closest I have gotten so far results in 'hazardous' being counted twice while 'poor' is counted once, but that number doesn't carry over beyond one repeat in any given category.

Sources consulted so far:
Solved: Repeat record counter in Survey123 - Esri Community
How To: Calculate the sum of repeats in ArcGIS Survey123 (esri.com)
Solved: Calculate sum of specific responses in repeat - Esri Community

The approach I have currently - it breaks the parser (probably because selected() returns Boolean values?), which I have not been able to sort out...

begin groupdefect_summary_s  integerbad_sectionBad Sectionssum(count(selected(${tia_obs_section_safety},'unsafe')) + count(selected(${tia_obs_section_safety},'questionable')))integerbad_foundationBad Foundationssum(count(selected(${tower_foundation_cond},'hazardous')) + count(selected(${tower_foundation_cond},'poor')))integerbad_groundingBad Groundingssum(count(selected(${tower_grounding_cond},'hazardous')) +   count(selected(${tower_grounding_cond},'poor')))integerbad_bpBad Baseplatesssum(count(selected(${tower_bp_cond},'hazardous')) +   count(selected(${tower_bp_cond},'poor')))integerbad_safety_climbBad Safety Climbssum(count(selected(${safety_climb_cond},'hazardous')) +   count(selected(${safety_climb_cond},'poor')))integerbad_guy_attach1Bad Guy Attach1sum(count(selected(${tower_ga_cond},'hazardous')) +   count(selected(${tower_ga_cond},'poor')))integerbad_guy_wireBad Guy Wiressum(count(selected(${tower_wire_cond},'hazardous')) +   count(selected(${tower_wire_cond},'poor')))integerbad_radBad Radssum(count(selected(${rad_mount_cond},'hazardous')) +   count(selected(${rad_mount_cond},'poor')))integerbad_equipBad Equipssum(count(selected(${sector_equipment_cond},'hazardous')) +   count(selected(${sector_equipment_cond},'poor')))integerbad_rodBad Rodssum(count(selected(${lightning_cond},'hazardous')) +   count(selected(${lightning_cond},'poor')))integerbad_antennaBad Antennassum(count(selected(${lone_antenna_cond},'hazardous')) +   count(selected(${lone_antenna_cond},'poor')))integerbad_lightBad Lightssum(count(selected(${light_cond},'hazardous')) +   count(selected(${light_cond},'poor')))integerbad_guy_attach2Bad Guy Attach2sum(count(selected(${guy_attachment_cond},'hazardous')) +   count(selected(${guy_attachment_cond},'poor')))end groupdefect_summary_e      hiddenissue_countNumber of issues reported:sum(${bad_section} + ${bad_foundation} + ${bad_grounding} + ${bad_bp} + ${bad_safety_climb} + ${bad_guy_attach1} + ${bad_guy_wire} + ${bad_rad} + ${bad_equip} + ${bad_rod} + ${bad_antenna} + ${bad_light} + ${bad_guy_attach2})

I would greatly appreciate the advice of guidance of folks smarter than myself!

JA7 · Answer

There's a lot going on in your survey. If your count or sum fields had a blank in it, your addition field would treat your values as a string and concat your values. If you had the following bad counts: 1, 0, 1, 1. Instead of adding them up and giving you 3, it would instead just put them all in a a line so you get 1011. To fix this, we need to ensure that we do not have any blanks.

When a field is not relevant, any calculations applied will not fire. To ensure that our count fields are still firing I moved your relevant statements out of the relevant column and into the body::esri:visible column. This still hides fields if they are not applicable, but still allows the calculations to fire. This fixed most of the issues, but your repeat count statements were also causing issues. It was possible to not have a repeat count value, so I changed your statements to always have at least 1 repeat available.

sum4.xlsx

dougbrowning · Answer

It is treating it as text as is the default in 123.  Set the bind type column to int on the if() rows.   If still stuck this has been posted before and they may help.

Hope that helps.

AndyMcClary · Answer

Hi Matt,

This might be a good situation to use custom javascript functions to simplify your form. You could concatenate all of your condition fields into one text string using the concatenate and join functions and then count the number of occurrences of "poor" or "hazardous" using a javascript function.

Your concatenated string field would be concat(join('',${rad_mount_cond}),join('',${sector_equipment_cond}), etc.)

The javascript function would look something like:

function countAnswer(answers, search_value) {

var answer_count = (answers.split(search_value).length-1)

return answer_count;

}

and you would call the function and return the number of instances where the answer was "poor" using: pulldata("@javascript","functions.js","countAnswer", ${concatenatedanswers},"poor")

There's also some good examples of how to count a specific answer to a question in a repeat using javascript in this blog: https://community.esri.com/t5/arcgis-survey123-blog/extending-survey123-smart-forms-with-custom-js/ba-p/898782 (Basically scroll down to the section with fruits and bananas). In the past I've found that method can get slow with large number of records but recent improvements to the javacript engine may have solved that.

Let me know if you have questions.

-Andy