topic Re: Survey 123 Dynamic Questions and Related Table in ArcGIS Survey123 Questions

Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Thu, 23 Nov 2017 00:43:24 GMT

Hi does anyone know if this is possible in Survey 123?

Say I have a survey with a "select_one" question that presents the user with a number of options. Depending on what option the user selects they will be presented with a number of Y/N questions. These questions will be different depending on the option and the number of questions can vary as well but they are always Y/N answers. In my scenario there are hundreds of Y/N questions but they will all not be necessarily be answered (depending on the select_one option)

i.e.

select_one Option 1
- Question 1 Y/N
- Question 2 Y/N
select_one Option 2
- Question 1 Y/N
- Question 2 Y/N
- Question 3 Y/N
- Question 4 Y/N
select_one Option 3
- Question 1 Y/N
- Question 2 Y/N
- Question 3 Y/N

I know that the display of the questions can be achieved using the "relevant" column and I understand the notion of "repeats" but I want to write the question text and the answer to a related table rather than configuring a flat feature class with hundreds of columns for all the different questions as this leads to a lot of redundancy and blows out the limitations of the feature class.

Hope this explanation is clear?

Any ideas anyone?

Cheers

Callum

Re: Survey 123 Dynamic Questions and Related Table

Anonymous User — Mon, 27 Nov 2017 12:57:48 GMT

You should be able to use groups with a nested repeat that has the Relevant column controlling when they are displayed for answers based on previous questions.

So, let's say that your Option 1, 2, & 3 are a series of questions that you want a related table for each series.

Main Question that is a select one with answers 'Option 1', 'Option 2', and 'Option 3'

Begin Group Option1 Relevant Column equals ${Main Question}=Option 1

Begin Repeat Option1

Question 1

Question 2

End Repeat Option1

End Group Option1

Begin Group Option2 Relevant Column equals ${Main Question}='Option 2'

Begin Repeat Option2

Question 1

Question 2

Question 3

Question 4

End Repeat Option2

End Group Option2

Begin Group Option3 Relevant Column equals ${Main Question}='Option 3'

Begin Repeat Option3

Question 1

Question 2

Question 3

End Repeat Option3

End Group Option3

You'll have to play around with the structure to match your question setup, but that should do what you want in terms of writing each group to a related table.

Cheers!

Mike

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Tue, 28 Nov 2017 20:35:04 GMT

Thanks Mike for that. With your approach would each question be a record in the related table? or would there be 3 related tables with multiple columns depending on the number of questions? I am trying to get a solution with 1 related table and each question is a record in that table..

cheers

Callum

Re: Survey 123 Dynamic Questions and Related Table

Anonymous User — Tue, 28 Nov 2017 20:50:17 GMT

The above approach would add three related tables that would be named "Option1", "Option2", and "Option3" and have each of the questions within them.

However, I think what you are looking for would be something like this -

Begin Repeat Name of Related Table

Starting Main Question (The answer to this select one will dictate which Option question set is exposed)

Begin Group Option1 Relevant Column equals ${Main Question}=Option 1

Question 1

Question 2

End Group Option1

Begin Group Option2 Relevant Column equals ${Main Question}='Option 2'

Question 1

Question 2

Question 3

Question 4

End Group Option2

Begin Group Option3 Relevant Column equals ${Main Question}='Option 3'

Question 1

Question 2

Question 3

End Group Option3

End Repeat Name of Related Table

This should give you all of the questions within one related table and the ability to expose only the question groups that you want to expose.

Mike

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Tue, 28 Nov 2017 20:53:41 GMT

Thanks Mike I will have a play around with your ideas and see if I can get it to work the way I want.

Will let you know how I get on

cheers

Callum

Re: Survey 123 Dynamic Questions and Related Table

Anonymous User — Tue, 28 Nov 2017 21:18:42 GMT

No worries. I use this in a similar manner for one of our endangered bat surveys. We capture bats with poles that have a net between them and then record some vital measurements from the bat. If it is a bat of interest, then we place a small transmitter on it's back to perform some radio telemetry tracking to find it's potential roost trees. So, I have the transmitter questions hidden until they select "Yes" for are you going to track the bat?

In my survey, I have a "bat capture" form with multiple groups inside of a related table that are exposed depending upon answers from select_one questions. So, I think this method should help you out.

If you need some help with the xlsx form structure, then put something together and I can take a look at it.

Mike

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Mon, 04 Dec 2017 19:31:34 GMT

Hi Mike

I've played around with you suggestions and I don't think it is possible to achieve what I want.

I want my related table to look like this:

Question	Answer
Is it corroded?	Y
Is it underground?	N
Is it broken?	N
Does it need maintenance?	Y

So depending on what is selected in the main question the user will get presented with a number of questions. Then the questions and the answer for these will be populated in a related table linked back to the featureclass which will have the main question.

I think this may be a "Bridge to Far" with Survey 123 at the moment?

cheers

Callum

Re: Survey 123 Dynamic Questions and Related Table

JamesTedrick — Mon, 04 Dec 2017 21:26:35 GMT

Hi Callum,

I don't think this is necessarily outside of what can be done with Survey123. One thing I'm not clear on:

- When you say you want the questions to be in a related table, are you specifying one related table per group of questions (as Mike showed above) or one related table with all of the additional questions? If the latter, that can be done with one repeat section and multiple groups that have the different relevant statements inside it.

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Mon, 04 Dec 2017 21:42:11 GMT

Hi James

Yes I want 1 related table with all of the additional questions. I don't want the related table to have a column for every question the related table should have only two columns "Question" and "Answer". The answers are either Y/N or Maybe. My survey has hundreds of questions so rather than having hundreds of columns I would rather have hundreds of records in a related table. If you think this is possible I would appreciate a small example?

cheers

Callum

Re: Survey 123 Dynamic Questions and Related Table

JamesTedrick — Mon, 04 Dec 2017 22:17:15 GMT

Ah, thanks- I hadn't quite caught that you wanted the table normalized to that degree. You're right, at this point Survey123 can't quite handle that structure; I'm not sure the XLSForm standard can completely support this - in particular, getting the label of the question to stored into a field would be challenging (I don't know of anything within the XLSForm specification that could get the label of a question, though there might be an alternative lookup mechanism that could be implemented).

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Mon, 04 Dec 2017 22:30:16 GMT

Thanks James

I was thinking the question part might be able to be handled with hidden fields and default values but I was struggling as to how to incorporate the related table when not really using repeats.

Perhaps food for thought for your development team as we would find functionality like this quite useful especially for surveys that have lots of Y/N type questions (by lots I mean hundreds) which makes the table large in terms of the number of columns. We prefer to use feature services and the submission_url for our surveys and we are finding that we often go over the 500 column limit for enterprise geodatabases.

Regards

Callum

Re: Survey 123 Dynamic Questions and Related Table

Anonymous User — Tue, 05 Dec 2017 14:09:42 GMT

This might...no it will sound absurd, but could you do some post-processing of the data? Just thinking way outside of the box here, but could you essentially modify the Survey to contain a bunch of hidden related tables. Plus create a tool that would allow you to point it at a FGDB of your dataset and process the related tables to end up with your one large table? I think that you would need to figure out some sort of Survey ID also; as this would allow you to query out the responses per survey submitted; since you have questions that can be skipped.

I am unsure if there is a limit on the number of related tables, but if you made a related table that contained data fields named "Survey ID", "Question Asked", and "Answer Given". The "Survey ID" field would be some sort of Id that you generate at the beginning of the survey, "Question Asked" field would contain a default value that matched the question being asked, and then the "Answer Given" field would contain the answer to the question being asked.

Now you would have to increment the name of each related table's question and answer field in order to have unique field names; i.e. Question2, Answer2, Question3, Answer3...all the way to your last QuestionX, AnswerX.

Next you would have to create a master template table named Final_Question_Answers_Table that contains a schema of three text fields; "Master_Survey ID", "Master_Question Asked", and "Master_Answer Given" that would data entered into the related table data fields.

Next you could build a Model builder or python script that will take your Master Template Table and merge all of the related table records into that table; via field mapping portion of the tool, to create the table you are looking for. You would have to point each of the data fields into the corresponding Master data fields' please see screen shot below.

Essentially, the survey is filled out and submitted, then you would have to download the data, after downloading the data, you could point the tool to the FGDB, allow it to process your data, and end up with the table that you want.

Like I said this is a far out in right field idea that would possibly get you what you need in the long run.

There might be an easier way to do this, but I'm shooting from the hip after understanding that you want a very lean table.

Maybe this will spark a better idea of how to handle this?

Good Luck!

Mike

Re: Survey 123 Dynamic Questions and Related Table

CallumSmith2 — Tue, 05 Dec 2017 18:51:17 GMT

Thanks Mike

Yes that is essentially where I am heading. We are not going to use related tables we are just going to have a flat feature class with lots of fields for all of the questions and then we will post process it into a related table so that there is 1 record for all of the Y/N questions (i.e. Normalise the structure). Our survey is configured with a submission_url so the survey data is stored directly in our enterprise gdb so no need to download. The python script can be scheduled on a regular basis to run against the enterprise gdb.

Thanks for your help and ideas. Much appreciated.

Regards

Callum