Survey123 - group by and sum?

[CPG]

Hello. I'm using Survey123 Connect to build forms with 2 repeats, one nested within another. The main repeat selects the line of record, the nested allows a user to select a species and add a count. Because the available species list is extremely long, we've linked a .csv file for the species dropdown question.

Within the form, are we able to group by and sum each of the individual species counted?

I would like to display the distinct species and sums for each line (parent repeat) AND for all lines together. This would be a note, for field QC purposes.

I can supply some .xlsx work, but looking to see if this is even possible first.

Thanks!

[ZacharySutherby · Accepted solution]

Hello @CPG, 

Based on the context I think you may need a JavaScript function to do totaling based on unique species but I'll start with another option that doesn't require JavaScript but I don't think it would work in your scenario. 

I've seen folks get really creative with HTML in Survey123 to display a table that summarizes the count of species for the field personnel to quickly observe what's been collected in the repeat. To do that in the repeat you would have a calculate question that sets up the row for the table something like: 

concat('<tr><td>',${scientific_name},'</td><td>',${count},'</td><td>',${groundcover},'</td></tr>')

Then outside the repeat you would use the join() function to build the table from each row, something like: 

concat('<table border="1" cellspacing="0" cellpadding="5"><tr><th>Species</th><th>Count</th><th>Ground Cover</th></tr>',join('',${calculation_in_repeat}),'</table>')

In this scenario there is no logic to tease out duplicate species and append their count to a previous entry which is where the JavaScript function comes in. Using a JavaScript function you can process repeat data and identify if a species has already been collected and revise the count in an object. From there you can build the rows of the table in the JS function, return that and use that in your HTML table. 

[CPG]

Thanks Zach! I had done something similar to your HTML idea, though yours might look a little better. Was hoping for a nice, clean approach to displaying a "group by and sum" calculation. I'll stick with this idea for now, as .js is a bit beyond me at the moment.

Thanks again for the response!