Survey123 Report Syntax Issues with Zeros vs Null/Empty

[Teresa_Blader]

Hi! I've got a working report template, but I have some text responses that I only want to appear when relevant. My issue is the input is a number and so when it's empty, the report still evaluates it as a zero, but zero is also a valid input and I need a different message for a null/empty field vs >=0. 

${if NitrateResult == "" || NitrateResult == null} evaluates as true in the report even if the user actually entered in zero. And the reverse, if the field is empty: ${if NitrateResult >=0 && NitrateResult <0.5} evaluates as true even though the user didn't enter in 0.

I'm wondering how to tackle this so both messages don't appear when the field is empty or zero which is what is happening now:

${if NitrateResult == "" || NitrateResult == null}Not Reported ${/}${if NitrateResult >=0 && NitrateResult <0.5}Your nitrate results were under detection. This means that your water is below the Health Risk Limit of 10 mg/L (milligrams per liter) for nitrate. ${/}

I've tried ${if NitrateResult != "" && NitrateResult != null && NitrateResult >=0 && NitrateResult <0.5}, but Survey123 just errors and fails to fetch the report:

${if NitrateResult == "" || NitrateResult == null}Not Reported ${/}${if NitrateResult != "" && NitrateResult != null && NitrateResult >=0 && NitrateResult <0.5}Your nitrate results were under detection. This means that your water is below the Health Risk Limit of 10 mg/L (milligrams per liter) for nitrate. ${/}

This prevents both messages from appearing when the user enters 0 or nothing, but the second message doesn't appear when the user enters zero, which is what I'm after:

${if NitrateResult == "" || NitrateResult == null}Not Reported ${/}${if NitrateResult >0 && NitrateResult <0.5}Your nitrate results were under detection. This means that your water is below the Health Risk Limit of 10 mg/L (milligrams per liter) for nitrate. ${/}

[TylerGraham2]

You might try is putting parenthesis around each equation in the "if" statement. When to use parenthesis is a report template "if" statement is very poorly documented, but sometimes it needs to be done. In my case it was when trying to check if a choice was selected. If I didn't use the parenthesis like so: ${if (Pile_Activity | selected:"Replacement") || (Pile_Activity | selected:"Removal")} it wouldn't work. 

For your last snippet, you might try: 

${if (NitrateResult >0) && (NitrateResult <0.5)} and see if that works.

Alternately, if you can combine results for 0 and null, you might try doing some template math. I had a report where I wanted to have it fill in 0 if the value was null or empty and I couldn't get the if statement to work. I also recall it not working when 0 was entered as a value. I worked around it by doing some math ${int_field + 0} which adds 0 (in the report not the database) to everything in the field, including nulls and empty values which converts them to 0 and doesn't change a value (e.g. 0.5 + 0 = 0.5).

[Teresa_Blader]

Sadly parentheses did not help.

So to clarify, everything "works" without parentheses. It's just that ${ ... == ''} and ${ ==null} and ${ ... ==0} are all considered the same.

The report sees empty, null, and zero the same. But I think zero and null/empty are different.

So whether I do:
${if (NitrateResult >0) && (NitrateResult <0.5)}
${if NitrateResult >0 && NitrateResult <0.5}

... both work fine either way and behave the same.

The issue is even if the user entered nothing, and the cell is empty, the report or the database still sees it as zero and returns the related expressions as true.

So I DONT want it to add zero to anything, I just want it to believe the database that if the cell is null - it is null and not zero. And if it's zero, then its not null or empty.

Right now if the answer was 0, the report sees that as the same as null or empty

If there is no answer, the report sees that as the same as 0

So I have no way to distinguish a response of 0 from a response of null.

[TylerGraham2]

It might be time to report it to esri, if you haven't already. I was able to work around it in mine because in my case null was a proxy for 0, but since you can't operate on that assumption, you need it to recognize the difference, and I think that will be esri's job.   

[Neal_t_k]

Have you tried something like this, not sure it will work any different given your iterations above  but worth a shot.

${if NitrateResult} Text Here ${/}

${if NitrateResult == ""} Text Here ${/}

https://doc.arcgis.com/en/survey123/browser/analyze-results/featurereport-conditionalelements.htm

[Neal_t_k]

Or possibly this, again not sure, but worth a shot:

${if NitrateResult != "" && NitrateResult < 0.5}
  Your nitrate results were under detection...
${/}
${if NitrateResult == ""}
  Not Reported
${/}

[Teresa_Blader]

For some reason including this expression causes the report to error. It just fails to fetch. I've tried it a few times. I've double checked typos. It just doesn't like != "" or something!

if NitrateResult != "" 

[Teresa_Blader]

Thanks! I did try this ${if NitrateResult} {/} wrapping. Sadly, it still treats 0 as null or empty. 😭 I DONT GET IT!!

however using ${if NitrateResult} {/} was helpful to help clean up my report.

And it did remove the issue where if the user typed in 0 it would show both the == "" || == null and the >= 0 expressions. So now it just shows Not Reported. 

So a survey response of null and zero are still treated the same, before ${if NitrateResult} {/} is evaluated. The survey however does store the zero or null as entered into the form.

${if NitrateResult == "" || NitrateResult == null} Not Reported 
${/}
${if NitrateResult}
${if NitrateResult >= 0 && NitrateResult < 0.5} Your nitrate results were under detection... 
${/}
${if NitrateResult >= 0.5 && NitrateResult < 3} You had detectable levels ... 
${/}
${if NitrateResult >= 3 && NitrateResult < 10} Though your nitrate results... 
${/}
${if NitrateResult >= 10} Your nitrate results meet or exceed ... 
${/}${/}

[Neal_t_k]

@Teresa_Blader Ok thinking outside the report here.  Can you still modify the form/layer.  What if you did the calculation/conditon in the form itself. 

Calculated field: If(${NitrateResult})='','Not Reported', if(${NitrateResult}>=0 and ${NitrateResult}<0.5,'Text',if${NitrateResult}>=0.5 and ${NitrateResult}<3,'Text2',if(${NitrateResult}>=3 and ${NitrateResult}<10,'Text3','Text4))))

Also might have to do something like this for the first if:  if(string-length(${NitrateResult})=0,......

Then just call the calculated field in the report.

[Teresa_Blader]

That was where we started originally actually, but then these giant paragraphs of interpretations and recommendations are getting stored into the database which we're not interested in doing so I'd really like to find a way to still just keep those in the report and not in the database. Right now the survey itself brings in those as notes that aren't stored in the database.

I appreciate the idea though!