Counting instances of specific responses across many repeats

[MattEdrich]

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 group	defect_summary_s
integer	bad_section	Bad Sections	sum(count(selected(${tia_obs_section_safety},'unsafe')) + count(selected(${tia_obs_section_safety},'questionable')))
integer	bad_foundation	Bad Foundations	sum(count(selected(${tower_foundation_cond},'hazardous')) + count(selected(${tower_foundation_cond},'poor')))
integer	bad_grounding	Bad Groundings	sum(count(selected(${tower_grounding_cond},'hazardous')) +   count(selected(${tower_grounding_cond},'poor')))
integer	bad_bp	Bad Baseplatess	sum(count(selected(${tower_bp_cond},'hazardous')) +   count(selected(${tower_bp_cond},'poor')))
integer	bad_safety_climb	Bad Safety Climbs	sum(count(selected(${safety_climb_cond},'hazardous')) +   count(selected(${safety_climb_cond},'poor')))
integer	bad_guy_attach1	Bad Guy Attach1	sum(count(selected(${tower_ga_cond},'hazardous')) +   count(selected(${tower_ga_cond},'poor')))
integer	bad_guy_wire	Bad Guy Wires	sum(count(selected(${tower_wire_cond},'hazardous')) +   count(selected(${tower_wire_cond},'poor')))
integer	bad_rad	Bad Rads	sum(count(selected(${rad_mount_cond},'hazardous')) +   count(selected(${rad_mount_cond},'poor')))
integer	bad_equip	Bad Equips	sum(count(selected(${sector_equipment_cond},'hazardous')) +   count(selected(${sector_equipment_cond},'poor')))
integer	bad_rod	Bad Rods	sum(count(selected(${lightning_cond},'hazardous')) +   count(selected(${lightning_cond},'poor')))
integer	bad_antenna	Bad Antennas	sum(count(selected(${lone_antenna_cond},'hazardous')) +   count(selected(${lone_antenna_cond},'poor')))
integer	bad_light	Bad Lights	sum(count(selected(${light_cond},'hazardous')) +   count(selected(${light_cond},'poor')))
integer	bad_guy_attach2	Bad Guy Attach2	sum(count(selected(${guy_attachment_cond},'hazardous')) +   count(selected(${guy_attachment_cond},'poor')))
end group	defect_summary_e
hidden	issue_count	Number 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!

[JenniferAcunto]

It is hard to help without seeing your survey XLS. How I would build this out is inside the repeat use an if statement to return 1 if hazardous or poor conditions are reported for each category type. 

foundation_value: if(${foundation} = 'poor' or ${foundation} = 'hazardous', 1, 0)

Then outside the repeat you can sum the values for each category.

foundation_sum: sum(${foundation_value})

Finally, to get the overall number of poor and hazardous items you would simply add up your various sums.

bad_sum: ${foundation_sum} + ${grounding_sum}....

[MichelleWilliamsERM]

Nice job on your strategy!

At any point would you need to see your final calculations in your parent record?

If so, I have an outside-the-box idea for you, but I'd like to talk through it - over a call - because I need to know what your max count expected for each of the above calculations. 

[MattEdrich]

Hello Jennifer and Michelle, thank you both very much for your responses!

@JenniferAcunto, I appreciate your response! I did my best to implement your idea, and it is nearly working but the resulting sum I get is wrong in a strange way: the final sum seems to display as a string of 0's that update one category at a time (these individual 0's appear to count correctly), and I end up with an ENORMOUS number of expected records. For example, '312000' would suggest three issues found in the foundation, 1 grounding issue, and 2 base plate issues. Currently, this is resulting in 312,000 expected records in that final repeat section, which obviously is preposterous - in this case, I would be looking for 6 records to be required. Nothing I have come up with to convert that sum total to a single integer has worked!

@MichelleWilliamsERM Today it occurred to me that a short summary section at the very end of the survey, above the signature required before submission, would be a useful feature. Thus, I believe I am trying to display final calculations (but don't actually need to...). I'd love to hop on a call sometime to chat about this! In the meantime, I will ponder maximums for each category - they would mostly be determined by on-tower conditions such as how many legs it has or how many sections tall it is, so a perfectly fixed number is tough to achieve.

In the meantime, here is my XLS! 

[DougBrowning]

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.

[JenniferAcunto]

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. 

[AndyMcClary]

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/b... (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