Having trouble getting pulldata function to work

315
4
Jump to solution
04-14-2021 06:25 PM
MadelynHanton
New Contributor II

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.

0 Kudos
1 Solution

Accepted Solutions
Philip-Wilson
Esri Notable Contributor

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.

Philip-Wilson_0-1618528383652.png

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

Philip-Wilson_1-1618528600916.png

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.

View solution in original post

0 Kudos
4 Replies
Philip-Wilson
Esri Notable Contributor

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.

0 Kudos
MadelynHanton
New Contributor II

Hello Phil @Philip-Wilson , 

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. 

0 Kudos
Philip-Wilson
Esri Notable Contributor

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.

Philip-Wilson_0-1618528383652.png

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

Philip-Wilson_1-1618528600916.png

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.

0 Kudos
MadelynHanton
New Contributor II

Phil @Philip-Wilson 

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