Duplicate selection from drop down in a repeated group breaks sum function

Question

Hello,

I am trying to develop a form for surveying natural water sources.  Some of these sites have water plumbed into a tank. Some have water plumbed to multiple tanks.  The tank designs can vary significantly per site.

I have a repeated group for users to pick a tank and then fill out all of the fields for it, like design, capacity, how full it is, etc.  They can repeat the group for how ever many tanks are at the site.

If the user selects a different design for each of the tanks, all of the sum functions outside of the repeating group that rely on what is in the group work (i.e, a sum of the total capacity of the site, how much water is actually there, and a percentage based on these two fields).  However, if the user selects the same tank design for multiple tanks at the same site, then the sum functions and subsequent calculations that rely on them break.  Having the tank design repeat is crucial as it is more common for like designs to be at the same site than different designs.

This is my first bout at using Survey123 so I am naive to a lot of what is going on, but this is the first roadblock I can't seem to get past with just googling.

The repeating section starts at line 98.  What I cannot understand is that the ${TotalCapacity} (line 146) calculation works either way, but the ${TotalAvailable} (line 156) does not, even though it is practically the same calculation.

Thanks!

Tanks.csv

eighths.jpg

DWU_10K.csv

DWU_6K.csv

test-Connect_Arioch.xlsx

Clip_2070.jpg

Clip_2071.jpg

DasheEbra_0-1745079571292.png

HingFungChan_0-1745071342172.png

DavidPike_0-1745067606720.png

TylerGraham2 · Accepted Answer

I took a look at it and the problem originated mostly from passing a calculate in a repeat to a field outside of the repeat. Rather than summing the numbers it concatenated them. I'd changed the TA2 field that summed everything to a decimal question so it is visible in the survey, and that is what you see for the BossCalc field in the screenshot below. Since you can't see calculate fields in the form when you get calculate errors, changing it to the field type the data would be in is good practice to track down errors.

I made some changes to the xlsform, and highlighted them with an orange/red fill color.  I changed the Tanks from select multiple to select one.  If someone selects 2 tanks, the fields are all setup for one tank per repeat and Survey123 won't know what to do with the data and just left things blank. I also added some constraints to the fields, so if the calculated gallons exceed the capacity of the tank, it won't let them submit until they fix their measurements.

You can reduce the number of fields in the repeat by using one decimal question instead of a calculate and note questions.  Set it to read only and apply round() the the calculation formula and it will display the answer rounded to however many decimals you specify and users can't edit it.

I also added a TA9 field to sum the estimated gallons that a user can manually enter and include that with the total capacity calculations.

You still have more work to do. I didn't mess with the DWU questions. If you do 2 DWU 8' tanks you will see the concatenating error reproduce in the Total Water Stored at Site question.  Set those up like I did using the decimal question type and they should work.

Also, so you are aware on the xlsform you shared there were some errors.

* The calculation in line 133 is missing the ${} for the field name, so it won't display.
* Line 134 calculation is missing the *7.48052 to convert from cu. ft to gallons
* Line 135 calulation point to the WaterPresentSTDCubeCalc, but should point to the WaterPresentMeasCubeCalc

One last thing since you said you were new to this.  Any question that isn't set as required in Survey123 can and will be skipped by people.  A lot of your questions are just a "yes" choice and the question is optional. If I had tanks on my property I could skip the plumbing question and avoid the trigger to enter any data about my tanks or other plumbing features.  You would never know if it was intentional, accidental, or if there was actually anything there or not. Best practice would be to set the questions as required and use yes/no or yes/no/not applicable choices.