Counting instances of specific responses across many repeats

600
6
12-15-2022 05:46 PM
MattEdrich
Occasional Contributor

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!

0 Kudos
6 Replies
JenniferAcunto
Esri Regular Contributor

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}....

- Jen
0 Kudos
MichelleWilliamsERM
Occasional Contributor III

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. 

0 Kudos
MattEdrich
Occasional Contributor

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! 

0 Kudos
DougBrowning
MVP Esteemed Contributor

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
Esri Regular Contributor

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. 

 

- Jen
0 Kudos
AndyMcClary
New Contributor III

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

0 Kudos