Unpivoting multi-value answers to a Survey 123 question

898
4
Jump to solution
07-08-2021 05:35 PM
AlfredBaldenweck
MVP Regular Contributor

I'm looking at doing data entry connecting documents to land parcels. 
Each document can apply to multiple parcels. 

I would like to create a survey (for ease of data entry) and use a multivalue question associate the two, then unpivot it so I have a table to relate my list of documents and my list of parcels.

E.g. 

AlfredBaldenweck_0-1625790628095.png

Becomes

AlfredBaldenweck_1-1625790650141.png

Is there an easy way to unpivot data? 

If there is, can I set it up to happen automatically with each form that comes in? I understand that would probably end up making two different tables (Raw and Final), but I'm fine with that.

I also understand that Survey 123 is not necessarily the best program for this; if anyone has any suggestions for this, I'd happily take them. I know that you can unpivot in MS Access, for example. The goal is to be contributing to the table as new documents are created with minimal data entry instances.

 

Thanks!

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Esteemed Contributor

Have you considered using a related table for this? In Survey123, they're referred to as Repeats. (https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformrepeats.htm)

That way your documents only get entered once, and each can relate to as many parcels in your related table as necessary. Graphically, that would look like:

Main Table

OIDBookAuthor
1War and PeaceTolstoy
2My Life and Hard TimesThurber

 

Related Parcels

OIDRel_IDParcel
11St. Petersburg
21Stalingrad
32Columbus, OH

 

If you needed to recombine the tables into the second picture in your post, this can easily be done by means of an attribute-based join.

- Josh Carlson
Kendall County GIS

View solution in original post

0 Kudos
4 Replies
jcarlson
MVP Esteemed Contributor

Have you considered using a related table for this? In Survey123, they're referred to as Repeats. (https://doc.arcgis.com/en/survey123/desktop/create-surveys/xlsformrepeats.htm)

That way your documents only get entered once, and each can relate to as many parcels in your related table as necessary. Graphically, that would look like:

Main Table

OIDBookAuthor
1War and PeaceTolstoy
2My Life and Hard TimesThurber

 

Related Parcels

OIDRel_IDParcel
11St. Petersburg
21Stalingrad
32Columbus, OH

 

If you needed to recombine the tables into the second picture in your post, this can easily be done by means of an attribute-based join.

- Josh Carlson
Kendall County GIS
0 Kudos
AlfredBaldenweck
MVP Regular Contributor

I think this will work great!

Thank you!

0 Kudos
AlfredBaldenweck
MVP Regular Contributor

Sorry, just tested it and have two questions.

It looks like it relates off of Global ID; is it possible to set it up using a different key? Or should I just add in another question and domain/data validate to ensure that the correct ID is also assigned?

0 Kudos
jcarlson
MVP Esteemed Contributor

As far as I know, Survey123 requires globalID values to relate to one table to another, even though you can establish a relate of your own based on other fields. The globalID / GUID values would all be managed automatically, so it wouldn't be too difficult to manage for new features.

If you add another question, it's going to assume that's a field in the parent table. Going that way, you either need to enter your data in the "unpivoted" style (one submission per author-parcel combo), or else you'll end up with multiple columns for additional parcels.

- Josh Carlson
Kendall County GIS
0 Kudos