Calculations in Survey123 connect form

[RachelS]

Hi,

I'm trying to create 2 calculations at the bottom of my form for totalhours and totalunsocialhours.

1. The first is totalhours, the formula I've tried only works if all the 6 fields are filled out which won't always be the case. How do I allow only if it's filled in?

${hoursworked1}+${hoursworked2}+${hoursworked3}+${hoursworked4}+${hoursworked5}+${hoursworked6}

2. The second is much more complicated. There are 3 time periods to choose from in a drop down list  12-8am, 8-6pm, 6-12pm.

If either 12-8am or 6-12pm I want to add the hours.

if(${timeperiod1} 12-8am or 6-12pm then add {hoursworked1}
if(${timeperiod2} 12-8am or 6-12pm then add {hoursworked2}
if(${timeperiod3} 12-8am or 6-12pm then add {hoursworked3}
if(${timeperiod4} 12-8am or 6-12pm then add {hoursworked4}
if(${timeperiod5} 12-8am or 6-12pm then add {hoursworked5}
if(${timeperiod6} 12-8am or 6-12pm then add {hoursworked6}

But again all 6 may not be filled out, there may be any between 1 up to 6.

I'd appreciate any help with this.

Rachel 

[JenniferAcunto · Accepted solution]

For your first question, the easiest way to handle this is to give those questions a default of 0. That way if your users don't answer the question, it has a numeric answer instead of a null and your summation calculation will still work.

For the second question, the general idea is the same, you want to 0's to populate your summation when the fields do not meet your specified parameters. You can do this with if statements:

if(${timeperiod1}='12-8am' or ${timperiod1}='6-12pm', ${hoursworked1}, '0')

What this statement is saying, is if timeperiod1 is 12-8am or 6-12pm, provide hoursworked1, otherwise provide 0. 

If you are still getting comfortable with Survey123, I recommend adding an intermediate question for each of your various if statements and then a final calculation field to sum them all together. Keep them visible in the survey while you do your testing, that way you can see if each one is firing how you expect it to and makes it easier to troubleshoot if it is not working. Once you are ready to publish you can hide those fields.

[RachelS]

Hi Jennifer,

Thank you so much for your help. I've finally got there.

The first question, when I put in 0 as the default it still wouldn't work but I found a thread that said to add the coalesce () function and that worked.

Survey123 Calculations not calculating - Esri Community

So my formula is:

coalesce(${hoursworked1},0)+coalesce(${hoursworked2},0)+coalesce(${hoursworked3},0)+coalesce(${hoursworked4},0)+coalesce(${hoursworked5},0)+coalesce(${hoursworked6},0)

For the second question, your formula's worked great, just when I changed the field type to 'hidden' the field type changed to boolean rather than decimal even when I had bind::esri:fieldType set to esriFieldTypeDouble.

I discovered I also need bind::type set to decimal on the hidden fields.

It's working like a dream now.

Thanks again for you time.

Rachel