topic Re: Sum Subtotals fields to get a total field error in ArcGIS Survey123 Questions

Sum Subtotals fields to get a total field error

DiegoLlamasOlivares — Thu, 29 Jul 2021 20:01:37 GMT

Hello,

I have one question.

I am doing a survey with connect.

I have some questions where i collect quantity, then those fields i multiply for cost in dollars

for example i have 3 cars then i multiply that for 500 dlls so my sub total is 1500

then to calculate a total y sum all subtotals name fields as ${a21_003}+${a21_002}+${a21_001}+${a20_007}+${a20_006}+${a20_005}+${a20_004}+${a20_003}+${a20_002}+${a20_001}+${a1_001}+${a1_0001}+${a2_001}+... for the 32 records.

i have some issue. If some of my sub total field is empty my total do not work, it appear empty. so, i thought that if I put a default value it would take it as not empty but in my survey my field are empty and not with 0 as should be the default value.

as you can see, my subtotal works fine but my total never works because when we collect data we do not have all of those debris to collect.

So my question is how can I solve this issue, because i need to create a report with a total cost but i am not able to generate the total field.

Thanks,

Diego Llamas

Re: Sum Subtotals fields to get a total field error

DougBrowning — Wed, 04 Aug 2021 15:39:53 GMT

Check out coalesce function for this.

coalesce(value1, value2) Returns the first nonempty value. Supports only two values.

Use it like coalesce(${a21_003},0)

Re: Sum Subtotals fields to get a total field error

DavidRichey — Sun, 19 Sep 2021 22:20:11 GMT

Hey DougBrowning great tip. Where did you find that syntax?

Re: Sum Subtotals fields to get a total field error

DougBrowning — Mon, 20 Sep 2021 13:37:46 GMT

Bookmark this page it has it all

https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm