Create a related table from a Survey123 XLS form

[KelseySmuczynski]

Hey Esri Community,

A colleague of mine created a standalone survey using Survey123 Connect. This survey needs to relate to a point feature class (the survey will be completed for each point location). Is there a way to create a new related table from the XLS form my colleague developed?  I do not want to create a survey based on a related table/feature class, which is what I have come across most often in my search for an answer. The survey is already made, I just want to create a related table containing the info from the survey.

[MichelleWilliamsERM]

I'm following this one for the answer. We would use this also, but at this time, we republish a new survey and bring in the data from the old survey.

[KelseySmuczynski]

Hi @MichelleWilliamsERM ,

Glad someone else would find a feature like this useful! In my case, this is a new survey for which no data has currently been collected. I'd love to be able to create the necessary relationship to my point feature class (currently published on ArcOnline as a hosted feature layer) directly from Connect or at least using the XLS form. It seems redundant and time-consuming to have created the survey in Connect, but then need to create a related table with the same information.

[Richard_Purkis · Accepted solution]

Hi @KelseySmuczynski @MichelleWilliamsERM 

This is absolutely possible but it will require a few steps in advance of configuring the survey in Connect. For your reference, this is the same query as in this thread. I ran through the steps and confirmed it works

1. Download your hosted Point feature service as a FGDB
2. Open this FGDB in Desktop / ArcGIS Pro
3. Within the FGDB  create a new table
   1. The name of this table will be the same as the repeat question in the Survey123 xlsx form
   2. Create all of the data field names (name column in S123 excel) and associated alias's (label column is S123 excel) that you want to capture within the Survey form
   3. Create a data field that is named "parentglobalid" and is a "GUID" data type
4. Within Catalog create a relationship class 
   1. Origin Table/Feature Class would be the Point Feature Class
   2. Destination Table/Feature Class would be the new table you created.
   3. Type of relationship would be "Composite"
   4. Accept defaults for labels, message direction
   5. Cardinality would "1 - M (one to many)
   6. Leave Relationship class attributed unchecked
   7. Primary Key would be the "globalid" field from the Point Feature Class
   8. Foreign Key would be the GUID field from the table you created named "parentglobalid"
5. Republish the feature class and choose to Overwrite the Existing Feature Service
6. Within Survey123 Connect for your existing survey 
   1. Include all the questions in a repeat that is the same name as the new table you created.
   2. In the settings tab add a submission url to the Point feature class
   3. Publish
7. Then download the re-publish the survey form and test it out.

Let me know if this helps

[KelseySmuczynski]

Thanks, @Richard_Purkis. 

I was hoping there was a way to establish the relationship between the Point feature class and the survey without needing to essentially re-create the survey in a table format. It would be neat if there was a tool that could build the table and create the relationship for you based on the XLS form. It sounds like for now I'll need to create a table and relationship class in Pro with the same field info used to build survey.

Also, for step # 6.2 in your instructions above, you mention to add a submission url to the Point feature class, but what about the related table that was created? Should a submission url be added to that? I think I'm confused about the table's role in this.

[Richard_Purkis · Accepted solution]

@KelseySmuczynski The submission url is for the point feature class. Given the point feature class will now have a related table and the survey xlsx form now includes a relate meaning they are consistent.

Thanks

[KelseySmuczynski]

@Richard_Purkis Understood! One last question -- my survey does not contain any repeats. Do the survey name and table name still have to match?

[Richard_Purkis · Accepted solution]

@KelseySmuczynski refer to step 6.1: Include all your questions from the original xlsx form in a repeat

Thanks