vlookup in survey123

2507
8
03-31-2020 12:35 PM
FiorellaRodriguez
Occasional Contributor

Hi, 

Is the vlookup and hlookup function supported in survey123? 

My second option is to do a nested if but I´m trying not to use it because I already have a lot of calculations and I have been noticing my survey sometimes crashes after some of calculations are done. 

Thank you for your help. 

Tags (1)
0 Kudos
8 Replies
Jim-Moore
Esri Regular Contributor

Hi Fiorella

Perhaps a pulldata() function would be useful for your survey. This method allows you to do a lookup from the values in an external CSV file.

Please see a great explanation on this method here: https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-da... 

And more here: Formulas—Survey123 for ArcGIS | Documentation 

Regarding the crashes, which version of the app are you using and on what device and OS version?

Best,

Jim

0 Kudos
FiorellaRodriguez
Occasional Contributor

Hi Jim, thank you for your reply. 

The pulldata function does not work for me in this because because the data that I need to look up is calculated within the same form, but thank you for your recommedations. I think in this case I will use a nested if. 

About the crash, I had installed Survey123ConnectAlpha_x64_3_11_20, but I thought that might be the issue so I installed version 3.7.62 however I´m having the same behavior. I found that the crash happens within a calculation field. I´m not sure if I could open a tech support case for this. I have not publish the form, I´m in the creation stage and using connect for testing the form´s calculations. 

This is what happens: 

I have this calculation in a calculate field type  if(${calc_cant_lect} < 55, number(${s1})+1, round(${l2} + 0.5,0)), when the field ${calc_cant_lect} is lower than 55 the form works and does the calculation, but if the field ${calc_cant_lect} is 55 or higher it crashes. 

Thank you for your help. 

0 Kudos
Jim-Moore
Esri Regular Contributor

Hi Fiorella

Thanks for the additional info about the crash. Strange! Would it be possible to share your XLSForm so I can help troubleshoot?

Also please note the latest version of Connect is version 3.9.120 (released last week).

Best,

Jim

0 Kudos
FiorellaRodriguez
Occasional Contributor

Hi Jim, 

Yes sure, I have attached the xlsform. Thank you, I really appreciate your help. 

I will install the lasted version and create a new survey from this xlsform to test if I have the same behavior. 

0 Kudos
Jim-Moore
Esri Regular Contributor

Hi Fiorella

Many thanks for the XLSForm. I stripped the survey down to retain only the calculations that are causing an issue, namely the s# and l# sets of questions. If I further reduce the number of calculations I get to a point where 23 or 24 sequential calculations will work, but any more than that and Connect crashes. I'll investigate further and get back to you.

One thought though is perhaps there's a way to achieve the same result without the need for all the iterative calculations? The ${calc_cant_lect} question returns how many readings have been entered. Would it be possible to replace the 60 x l# questions by using this number along with ${calc_fact_dat} in a calculation? Something like:

(${calc_cant_lect} * ${calc_fact_dat}) - (${calc_fact_dat} - 1)
Or...
((${calc_cant_lect} - 1) * ${calc_fact_dat}) + 1

Not sure how that would affect your calculations further down the form.

Best,
Jim

0 Kudos
FiorellaRodriguez
Occasional Contributor

Hello Jim, 

By any chance were you able to investigate further what is going on with Survey123 that my form crashes? 

#survey123 connnect

0 Kudos
Jim-Moore
Esri Regular Contributor

Hi Fiorella

Thanks for checking in - we're still investigating the cause of this crash and it seems to be connected to some issues raised by other users. This issue is difficult to resolve without affecting other core functionality of the field app and Connect. When progress is made we will share on EAC a beta build for testing and I will add an update here.

In the meantime, have you considered using calculations similar to those mentioned above as an alternative?


Best,
Jim

0 Kudos
by Anonymous User
Not applicable

Hi @FiorellaRodriguez 

Just letting you know we have recently updated the 3.14 beta builds on the Early Adopter Community and the next release will include major improvements to the expression calculation engine, new calculation modes, improvements to loading relevant questions and repeats, and other fixes to repeats, nested repeats and repeat count. With these improvements we see faster loading surveys, increased stability, less crashes and ability to optimize calculation workflows.

The issue you reported above with crashes when using complex calculations has been resolved in the latest 3.14 beta builds.

Please refer to this blog post for more details: https://community.esri.com/t5/arcgis-survey123-blog/arcgis-survey123-3-14-beta-now-available-for/ba-...

Direct link to EAC announcement: https://earlyadopter.esri.com/project/article/item.html?cap=e69ef91f45744b98882c651f7b518eb7&arttype...

Regards,

Phil.