Survey123, somewhere between if then and cascading selects?

1083
12
Jump to solution
10-09-2019 01:23 PM
BRYANHERRON
New Contributor III

I am setting up Survey123 forms for our field guys, one of the questions is the Employee Name, which leads to what their Classification is. Originally I just had 2 text type fields for them to type in the info. Now I am trying to provide them a list to select their name from which I have accomplished with the type select_one Employee or_other on the Survey tab and in the following row a Hidden Type and EmployeeCollated Name and the Employee list in the choices tab.  That works fine, they can select their name and when there is a new employee that hasn't been added to the form yet, they can type in their name.

FYI: I am exporting this all to a word document form of the old paper document they are currently filling out.

Employee A, B & C classifications are Tech-1 Employee D&E are Tech-2 and so on. So what I am trying to accomplish is to populate the "Classification" question based on the input of the Employee name. One thought was to use a series of if then statements. I assume in the relevant column. I don't completely understand how the if/then statements work, but if they do, the concept I envision is:

Row 10 if(${Employee}='other', ${Employee_other}, {go to the next if/then?}) How do you go to the next if/then on false?

Row 11 if(${Employee}='a', ${Tech-1}, {go to the next if/then statement?})

Row 12 if(${Employee}='b', ${Tech-1}, {go to the next if/then statement?})

Row 13 if(${Employee}='c', ${Tech-1}, {go to the next if/then statement?})

Row 14 if(${Employee}='d', ${Tech-2}, {go to the next if/then statement?})

and so on until all the classifications are met. Can you group Employee a,b,c into one if/then?

If the if/then is not the solution then maybe some manipulation of the cascading selects to just populate the classification instead of offering another selection and still allowing user input if ${Employee}='other'?

I'm relatively new to this, any help would be appreciated?

0 Kudos
1 Solution

Accepted Solutions
DougBrowning
MVP Esteemed Contributor

Create a csv that has your lookup value and return value.

Then you just query the csv in a calculation with a lookup value field, the columns to use (the csv can have lots of columns), and the column to return.

pulldata('TransectCodes', 'TransectLetter', 'TransectNum', ${TransectNum})

                CSV name          return column    lookup column   field with the lookup value

For me I am using the once(count(${RepeatCount})) trick to figure out which repeat I am on - then I lookup the letter for that repeat.  So 1 is A, 2 is B, etc.

Yours may be something like

a  tech1

b  tech2

c  tech1

etc

This also makes big lists way faster to load.  I ma up to 6,000 so far.

Hope that helps.

https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-da... 

View solution in original post

12 Replies
DougBrowning
MVP Esteemed Contributor

We I have done these I find it easier to just have a lookup csv then use pulldata to get the values. Then no need to write out these long if statements.

0 Kudos
BRYANHERRON
New Contributor III

Thanks for the idea Doug. I've never used a lookup csv. I'll do some research, but in the meantime, can you give me a short rundown on how that works?

0 Kudos
DougBrowning
MVP Esteemed Contributor

Create a csv that has your lookup value and return value.

Then you just query the csv in a calculation with a lookup value field, the columns to use (the csv can have lots of columns), and the column to return.

pulldata('TransectCodes', 'TransectLetter', 'TransectNum', ${TransectNum})

                CSV name          return column    lookup column   field with the lookup value

For me I am using the once(count(${RepeatCount})) trick to figure out which repeat I am on - then I lookup the letter for that repeat.  So 1 is A, 2 is B, etc.

Yours may be something like

a  tech1

b  tech2

c  tech1

etc

This also makes big lists way faster to load.  I ma up to 6,000 so far.

Hope that helps.

https://community.esri.com/groups/survey123/blog/2016/10/27/the-pulldata-function-access-external-da... 

BRYANHERRON
New Contributor III

I was hoping you hadn't checked my reply yet so I could have edited it. The first tutorial video I found explained this as well. A lot easier than I expected. Thank you for the explanation. Now on to the next unknown!

We'll see how the "or_other" part of the Employee name question affects the pulldata. Will it still open a text box to enter the classification manually when the user types in an unknown employee name? I hope it does and there's no additional trigger needed to get the text box to open for input.

Thanks again for your help.

0 Kudos
BRYANHERRON
New Contributor III

I just thought it was easy, can't figure out the error.

I've tried several things, but keep getting this similar message.I don't see any typos, I've changed the last call from Employee1 to Employee1Collated, Classification1 and several other things. I don't see any bad syntax. Idonno

Here's a view of the Employee_Classification.csv

Here's the form with the empty columns hidden. See rows 132-135.

0 Kudos
DougBrowning
MVP Esteemed Contributor

You have a extra ' right before ${Employee1} in your pulldata.

BRYANHERRON
New Contributor III

Yep, at 53 I need, but do not have...... glasses.

So any guess as to why row 135 is not placing the actual Classification after the label?

0 Kudos
DougBrowning
MVP Esteemed Contributor

You are using a few different spellings for Classification.  Look at your csv column name.  Has Classisfication  

BRYANHERRON
New Contributor III

I am still Blind

Changed them all to Class, including the csv filename and the csv header, but left the label the same.

No change

I have a feeling the issue is with the pulldata, I don't think it's placing any value for {Class1} and there's nothing for the note to report. I completely removed the or_other from the name selection, including the hidden row and there was no change, so I don't think the or_other is a factor.

Current CSV on top, Coices tab, Survey Tab, then 123 Connect on the bottom.

0 Kudos