Bug with calculations on repeats in Survey123 Connect?

12-11-2019 10:37 AM
New Contributor III

I'm working on an irrigation schedule form in Survey123 and I have a section where I'm stumped because my calculations are coming out wrong but I can't determine why.

The basic calculation data entry goes as follows:

1.  Number of days per month the schedule runs (${existing_prog_a_run_days_month})

2.  Number of start times per day from 1 repeat (count(${existing_prog_a_start}))

3.  A 2nd repeat that asks for a station name, number of minutes (${existing_prog_a_run_minutes}), and number of gallons per minute (${existing_prog_a_run_gal_min}).

The total calculation for amount of water used per month should be the number of days per month * the number of start times * the sum of the water used in each station (which is the number of minutes * the gallons per minute).

I think I've verified that I can't use the aggregate repeat functions to sum arithmetic calculations so I can't do sum(${existing_prog_a_run_minutes} * ${existing_prog_a_run_gal_min}) because that just doesn't work. To get around this, I created a calculate field inside the repeat (${existing_prog_a_run_calculation}) that has a calculation value of ${existing_prog_a_run_minutes} * ${existing_prog_a_run_gal_min} and then am using sum(${existing_prog_a_run_calculation}) to get the totals from the repeat.

Here's the issue:  I'm testing with some basic numbers to simplify the calculation and I'm getting what seems to be a concatenated result instead of a sum.  So, I'm using 10 days, 1 start time, and 2 stations that both are set to the same values of 10 minutes at 15 gallons per minute.  This should boil down to 10 days * 1 start time * (2 * (10 minutes * 15 minutes)).  If I set this as a calculate field in the XLSForm, the result that shows up is '1501500' which doesn't even seem like a proper concatenation.  If I set it as an integer field (or even decimal), I get the result of '3000' which is what I expect.

In the past, working with calculate fields, I guess I assumed that they were just hidden versions of integer/decimal fields. This little experiment seems to suggest that something else is going on. I've even tried wrapping my calculation with the number() and int() functions to try and force a numeral, just in case, but that doesn't fix the issue.

What am I missing here?  Is this a bug or am I unintentionally missing something that explains the incorrect concatenation instead of the addition.

Note that, in the attached worksheet, this will be duplicated for a Program A, B, and C but I'm only working on Program A right now.  If anyone imports and/or tests this, you only need to fill in the fields marked "Existing>Program A>Start Time, Run Days/Month", and then 2 "Station Run Times>Minutes, Gal/Min" to get the error. The other fields are optional and don't affect the calculation. The correct/incorrect resulting total shows up in "Total Gallons".

0 Kudos
1 Reply
Occasional Contributor II

For the line existing_prog_a_run_calculation, set the column bind::type to int. That tells the survey to treat that question as an integer, allowing your next calculation to correctly add its values instead of concatenating them.