Survey123 Calculation Won't Trigger Based On Hidden Value

[Marc_Patti]

Hi Everyone,

I’ve been working on some Survey123 Connect calculations to populate a survey question with an answer based on values contained in a separate feature layer called “Building Permits.” In that layer, the amount collected for an electric permit is stored in a field called “Electric_Amount.”
What I would like to do is update a question (called “erw_paid”) in a survey form linked to our Building Inspection layer based on the value of that payment field. If the “Electric_Amount” field contains anything other than “NaN”, I would like to have a question in the Building Inspection form update its value to “Yes.” The layers share a key field called “Permit ID”, so certain values can be retrieved using a “getValue” function.

I’ve been trying to accomplish this using a hidden field populated with a pull function and some logic responding to the value of that field, but the question never updates with the desired value when the form is opened. I’ve tried setting parameters for both to “calculationMode=auto”, but the calculation for the “erw_paid” question never seems to trigger even though it isn’t being held back by a relevant function. I’ve attached an Excel with the relevant lines, but I’ll include short snippets below as well:

Question 1:

• Type: Decimal
• Name: elec_hidden
• Calculation: pulldata("@layer", "getValue", "attributes.ElectricPermit_Amount", "https://services6.arcgis.com/cMgmhwu0L5CeOOoS/arcgis/rest/services/Department_of_Community_Developme...", concat("PERMITID='",${permitid},"'"))

Question 2:

• Type: select_one yesno
• Name: erw_paid
• Calculation: if(${elec_hidden} != 'NaN', 'yes', 'no')

Can anyone tell me where I might be going wrong here?

[DougBrowning]

I am not sure it will store a NaN in it actually.  I would try changing Q1 to string and see what it gives you.

[abureaux · Accepted solution]

I'm with @DougBrowning. I don't think NaN will actually be stored.

Dump ${elec_hidden} into a text field and visually confirm what it is actually returning. I've only seen NaN returned as an error. If you are seeing NaN and the calculate still isn't working, I'd tweak the calculate to not return an error (aka, NaN, in this case).

And in case anyone reading this is curious, NaN = Not a number.

[Marc_Patti]

Yep, dumping the result into a non-hidden text field was the right move. Turns out the function was pulling a "null" string, which I wasn't expecting. I re-wrote the function to change the "erw_paid" answer to "Yes" if the new string field did not pull string values of "null", "", or "0.00". It ended up looking like this:

if(${elec_hidden} = 'null' or ${elec_hidden} = '' or ${elec_hidden} = '0.00', 'No', 'Yes')

Thanks to all of you for getting me pointed in the right direction!

[ChristopherCounsell]

The field is double, so it will return a result or a null value.

You can wrap the calculation in a function to check if a result has been returned.

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

boolean(question, expression, or value)

Try having a secondary calculation that checks the result e.g. boolean(${pulldataquestion}) and then act on if that function returns true or false.

Try and leverage the Null / empty functions on the above documentation page instead of trying to interpret NaN as a literal string.