pulldata is returning the word "null" instead of an actual null (empty cell)

[EricaNova]

Hi there,

I am using a pulldata calculation to pull the value "camera_model" from another, related layer. My related layer is a hosted table that I uploaded from an .xlsx file.

My pulldata calculation says, "when the value of 'tag_id' in the survey matches the value of 'Collar_ID' in the hosted table, grab the value of 'Camera_Model' from the hosted table":

camera_model1 = pulldata("@layer", "getValue", "attributes.Camera_Model", "https://services1.arcgis.com/...my_url.../0", concat('Collar_ID=', "'",${tag_id},"'"))

This is working fine when values are not blank, but when the field "Camera_Model" is empty, it returns the word "null" instead of just leaving the cell blank. This occurs only in the web app, where most of my users collect data, not the mobile app or Survey123 connect preview page.

This is an issue – for example, my field ${drop_code} has an input mask of 2 letters gets filled with “nu”, which is an allowable value for that field and could be mistaken for real data.

I've tried some post-processing, e.g.,

Field ${camera_model} = if(string-length(${camera_model1}) > 0, ${camera_model1}, '') or

if(${camera_model1} != null, ${camera_model1}, ''), but this seems to actually remove values from some fields that should be auto-filled!

I attached a reproducible example using a publicly available layer. Note - “Location” field is filled with null in webapp (but is filled with nothing in mobile app).

Note - I haven't gotten any answers here, so I've sent this question into support. I'll update this post as I get more information.

[ChristopherCounsell]

Can you wrap the pulldata function in coalesce()?

If value 1 is null, return value 2.

Value 2 can be a manual string "No camera found" and then relevancy applied on your other questions such as drop_code, so they don't fire.

[EricaNova]

Hi @ChristopherCounsell , thanks for the suggestion!

Two things:

1. ESRI recommends against calculations in the same cell with pulldata - apparently pulldata likes to "be on its own" so any other calculations that include that pulled data should happen in a second calculation row

2. I did try this, but in the web app the pulldata is actually returning the WORD "null", so my calculation "coalesce(pulldata("@layer", "getValue", "attributes.Location", "https://services.arcgis.com/As5CFN3ThbQpy8Ph/arcgis/rest/services/Cities_in_Canada_/FeatureServer/0", concat('Geographical_Name=', "'",${Geographical_Name},"'")), '')" is still returning the word "null", since it is a non-empty value!

[JayForstner]

Hi Erica,

I am wondering if you were ever able to resolve this issue?  We are seeing the same thing in one of our surveys.  Works fine in the app, but when users submit via a browser, it is populating the actual word 'null'.  

[EricaNova]

Hey @JayForstner , ESRI logged a bug for this one, BUG-000171024. I just checked the status, it is Open, and "Under Consideration" tagged with "Medium" severity. So no... I did not resolve it, I had to work around it.

[AneteZvaigzneLV]

Still under consideration, the suggested workaround works. 

BUG-000171024 for ArcGIS Survey123