nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Pulldata.xlsx

SnakeLookup.csv

Find more posts tagged with

Accepted answers

dougbrowning

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.

SnakeLookup.csv

Form 3.xlsx

All comments

dougbrowning

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.

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

See attached for this working.

SnakeLookup.csv

Form 3.xlsx

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

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

Screenshot of remainder of error message.

Screenshot 2023-05-02 120831.png

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.