topic Re: relevant calculated fields are returning 0.00 when there is nothing to calculate in ArcGIS Survey123 Questions

relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

LynnBerni — Wed, 22 Aug 2018 20:01:12 GMT

In the following screenshot, the fields 5 & 6 are calculated based on data in fields 1-4. The whole group (fields1-6) is relevant as to whether or not a sample is collected. But, when no sample is collected, the calculated fields are returning 0.00. I would like them to be blank. Is there a way to control (cancel) the calculation when the relevant expression is not met?

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

JamesTedrick — Wed, 22 Aug 2018 20:39:37 GMT

Hi Lynn,

I am seeing that calculate questions are submitting their calculations when not relevant. However, it I'm not seeing the effect when the items are in a group. Can you share the survey or the part of the survey experiencing the issue?

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

LynnBerni — Wed, 22 Aug 2018 20:50:20 GMT

Hi James,

Here's a link to the xls form. It's the "Sample Analysis" group.

Thanks!

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

JamesTedrick — Thu, 23 Aug 2018 01:25:42 GMT

Hi Lynn,

This doesn't appear to be the right survey- I'm not seeing the group you refer to.

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

markcain — Thu, 23 Aug 2018 02:56:11 GMT

You should be able to wrap the calculation in an if statement.

if(${no_sample}!='',calculation,'')

This basically means that if the field ${no_sample} is not empty, then the calculation is performed, otherwise the field is blank.

or if you want it to only calculate when a certain option is chosen

if(${no_sample}='option1',calculation,'')

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

LynnBerni — Thu, 23 Aug 2018 19:26:07 GMT

thanks Mark, tried the if statement and the field is still being populated with 0. This field just doesn't want to be NULL!

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

LynnBerni — Thu, 23 Aug 2018 21:21:29 GMT

Hi James,

That's the one, the group starts on line 19 of the sheet.

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate

JamesTedrick — Thu, 23 Aug 2018 22:16:44 GMT

Thanks, I missed it in my review. In looking through what's occurring, it appears there is a change in how null values are being handled- before, they would have prevented the pulldata() function from running. I've filed an issue to look into this.

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

LynnBerni — Wed, 16 Jan 2019 16:54:52 GMT

Hello James Tedrick,

Have there been any updates on this issue? Or maybe other suggestions I could try? It continues to plague us.

Thanks!
Lynn

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

SteveLynch — Wed, 16 Jan 2019 17:11:31 GMT

is the table a .dbf or in a file geodatabase?

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

LynnBerni — Wed, 16 Jan 2019 17:13:13 GMT

dfb in egdb

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

SteveLynch — Wed, 16 Jan 2019 17:18:12 GMT

shapefile, which uses .dbf, converts null to zero.

To test, create a field in a fgdb that has nulls. Use CopyFeatures and save it to shapefile and you'll notice the zeros.

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

DanPatterson_Retired — Wed, 16 Jan 2019 19:15:10 GMT

Lynn, it sounds like relying on null values should be avoid in that case. If the data you are using are counts, put in a count value that isn't possible... you could go 'old school' and use -999 or some equivalent scaled variant. Any

observation which is a count cannot possibly have a negative number

Re: relevant calculated fields are returning 0.00 when there is nothing to calculate and should be NULL

LynnBerni — Wed, 16 Jan 2019 20:38:13 GMT

Thanks Dan, that's an excellent point!