Error Converting XLS Form - PullData Function

[CLillard]

Trying to implement the Pulldata function to automatically fill select fields. When I try to save my survey123 form I keep getting the error "Error converting XLS Form - Form is invalid. See above for errors: /norg.javarosa.xform.parse.XFormParseException: Invalid calculate for the bind attached to "$(date_last_capture)": Couldn't understand the expression starting at this point.. (see screenshot)."

Help! Attached my survey form and external CSV.

[CLillard]

Screenshot of remainder of error message.

[DougBrowning]

I think it because the pulldata will return a string not a date.  You will probably need to have this pulldata in a temp field then use date-time(question, expression, or string) Converts a number or string to a date object. to convert it for the question.

I also see your Label columns have errors.  Probably because you did a copy paste from the name column?  Always use paste values in excel or it will cause a big mess.

Hope that does it

[CLillard]

I don't completely understand what you are saying. Can you show an example? Also, I'd like to add that I get the same error when trying to pull data for the 2nd field which is 'sex'. That is just a string so not sure why it is throwing that error.

[DougBrowning · Accepted solution]

Well that was not it you had several other issues.

First you have a missing quote and a random paren here

pulldata('SnakeLookup', 'LastCaptured, 'BrandID', (${brand_number})

should be

pulldata('SnakeLookup', 'LastCaptured', 'BrandID', ${brand_number})

Next 123 requires the date to look like this

date('2017-05-28T04:39:02+10:00') see https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformformulas.htm 

Then when I tried 2023-05-02 it was converting that from UTC time (even thought date should ignore time) and thus you got 5-1.  So instead I set the time to noon like this 2023-05-02T12:00.

Next for sex you are mixing and matching case  in the name and label.  You have to be exact.

123 always stores and uses the name not the label.  Your csv has Unknown and so it was not a match to unknown.  I strongly suggest you keep the case the same in both the name and label or it will drive you nuts forever.  I really wish 123 would fix the template that has yes and Yes its really bad form.

You also pasted all the formulas into the label.  Be careful with that.  

See attached for this working.