Having trouble getting pulldata function to work

[MadelynHanton]

I am trying to pull data from the 'coordinates' csv file to be auto-filled into a field in my Survey 123 Connect survey. This is what I have in the Calculation field or that row: 

pulldata('coordinates', 'EXT_MIN_X', 'TRSQ', ${trsq_selected})

The field type is decimal as this should be returning a coordinate. 

Earlier in my survey, the participant selects their TRSQ (which is the value of trsq_selected). I want to pull the minimum X coordinate from coordinates.csv using that TRSQ value. No errors are appearing when I reload the survey, but nothing is populating in the filed after I select a TRSQ value. It just stays blank. I have read all of the other ESRI Community posts I could find that address issues similar to mine but none of those solutions worked. I have attached a zipped folder containing my Survey123 XLS sheet and the coordinates.csv file. 

Thanks in advance.

[Anonymous User]

Hi @MadelynHanton,

I had a quick look at your survey xlsx file and csv file and can see the problem. The format of the text used in the choice name field in the survey, is different to the CSV file, one is using underscores, the other has a space.

T3N R11E281 vs T3N_R11E281

If you make these the same, both either spaces or both either underscores, the pulldata calculation works as expected. I would suggest using underscores, and avoiding spaces in the name coloumn of choice lists, as this can cuases issues in other places or with some backend databases.

Regards,

Phil.

[MadelynHanton]

Hello Phil @Anonymous User , 

Thanks for your reply. I did use underscores in the actual 'name' field for the values in the choices tab, only the 'label' field uses spaces (i.e. the spaces appear to the survey user when they select their TRSQ but the actual value assigned to the trsq_selected variable should contain underscores). I tried changing the 'label' field to have underscores as well and I am still have the same issue. I don't think the label field contents should matter as long as the values in the name field and the coordinates spreadsheet are the same. 

[Anonymous User · Accepted solution]

Hi @MadelynHanton,

The problem is that your TRSQ field in the CSV is using spaces, which does not match the name field of the choice list in xlsx file. These are the two fields used in the pulldata() expression which the values must match.

Once I update the choice list name to match the CSV file values (both with underscores) the pulldata() expression works as expected.

Note that the label in the choice list sheet is just for display purposes only. It is not used in the expression, so does not need to match, the label can be completely different to the value. Only the value in the name field and CSV file must match.

Regards,

Phil.

[MadelynHanton]

Phil @Anonymous User 

My apologies, I misread your first comment. I thought my coordinates file had underscores when it didn't, I should have double checked. Thanks for your help! I got it to work now. 

Best, 

Maddy