Complicated equations in calculate for xls forms survey123?

Question

Hello,

we we are currently using survey123 for collecting field data on aquatic organism passage barriers in the field. Our survey is set up with a repeat that equates to a table relate for each pipe at a road stream crossing.

once data is collected, we must export the data and bring it into excel to do some logistical excel if/then calculations and then some equations to get a final barrier score which we then must re upload.

my question is, does anyone know the exact limitations for how complicated I can get in the calculate field for xls forms? I can’t seem to find anything more complicated that someone has done other than adding a tip to a bill. Can I use if/then in xls forms?

If this is possible it would be a huge breakthrough for us!

TIA!

dougbrowning · Answer

You can for sure get complicated.  See these examples.  Maybe a specific example or form sample would help see what you need.

if(${Lower1}='HL' or ${Lower1}='WL' or ${Lower1}='NL' or ${Lower2}='HL' or ${Lower2}='WL' or ${Lower2}='NL' or ${Lower3}='HL' or ${Lower3}='WL' or ${Lower3}='NL' or ${Lower4}='HL' or ${Lower4}='WL' or ${Lower4}='NL' or ${Lower5}='HL' or ${Lower5}='WL' or ${Lower5}='NL' or ${Lower6}='HL' or ${Lower6}='WL' or ${Lower6}='NL' or ${Lower7}='HL' or ${Lower7}='WL' or ${Lower7}='NL',1,0)

if(${SoilSurface} = 'S' and ${TopCanopy} = 'N' and string-length(${Lower1}) < 2 and string-length(${Lower2}) < 2 and string-length(${Lower3}) < 2 and string-length(${Lower4}) < 2 and string-length(${Lower5}) < 2 and string-length(${Lower6}) < 2 and string-length(${Lower7}) < 2, 1,0)

concat(join(", ", ${UnknownCodeTop}), ", ", join(", ", ${UnknownCodeLower1}), ", ", join(", ", ${UnknownCodeLower2}), ", ", join(", ", ${UnknownCodeLower3}), ", ", join(", ", ${UnknownCodeLower4}), ", ", join(", ", ${UnknownCodeLower5}), ", ", join(", ", ${UnknownCodeLower6}), ", ", join(", ", ${UnknownCodeLower7}), ", ", join(", ", ${UnknownCodeWoody}), ", ", join(", ", ${UnknownCodeHerbaceous}), ", ", join(", ", ${UnknownCodeBasal}), ", ", join(", ", ${UnknownCodeWoody2}), ", ", join(", ", ${UnknownCodeStubble}))

sum(${HeightHerbaceous}) div sum(${nonzeroherbhgt})

Hope that helps.

MichaelSheather-Reid · Answer

Hi Kathleen

We too are wanting to build complex formulas into Survey123 smartforms and are having difficulty finding enough specific documentation and examples. Have you found anything since your previous post? We too work in the field of ecology and environmental management so I would be really interested to know if you found a workable solution.

Regards, Angelene (even though the user name says Michael 🙂

TimTapp · Answer

You can use if statement in calucations, refer to quick reference link below.

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