Data Management of tables from Survey123 in ArcGIS Pro

[rwhit15]

Hello! I am pretty new to ArcGIS and Survey123 and I have recently created a Survey123 form for some of our field work. The form has questions which only appear once as well as some repeating questions that will have multiple responses per submission. I have uploaded this data into ArcGIS and noticed that the data from the survey got divided into several tables (Figure 1).

Figure 1. Survey is the main table. Each table below was created from a repeating question in the survey.

In the survey table, there is the SiteID, which is something I'd like to have appear in every table. It's connected to the GlobalID.

In one of the subsequent tables, the ParentGlobalID appears which is the same as the global ID in the survey table. I'm trying to have the SiteID column appear in all the tables that don't currently it and thought there would be a way to connect them through the GlobalID. However, in the other tables, all of the Global IDs are different. 

I am trying to have the SiteID appear in each table through the connection between the GlobalID in the survey table and the ParentGlobalID that appears in each subsequent table. I'm having a lot of trouble doing this though. I've tried using the Join feature but haven't had much success. I'd like to copy the SiteID in the survey table and place it in every other table based on the ParentGlobalID but I'm not sure if that's possible or if there's workaround I could do to make that happen. Any thoughts about how I could do this?

[Neal_t_k]

In your form, inside the repeat you can create a hidden field, ${SiteID_1} and in the calculation field set it equal to ${SiteID}.  Do this in every repeat you want that siteID.  That way it is calculated when the form is filled out as opposed to calculating later.  

That said you should be able to join the two tables based on the GlobalID/ParentGlobalID relationship.  You may need to check the settings in ArcGIS online to ensure editing is enabled.

[DougBrowning]

This is how we do it and it works.  I use SiteIDRepeatTableName as the field name in each repeat.  

I actually have a fancier workflow where I create my own relationship classes as well using my own id then publish all that as a service.  It is for sure a advanced setup but it works slick.  My write up is here   https://community.esri.com/t5/arcgis-survey123-questions/mapping-with-survey123-within-a-polygon-or-... 

[MobiusSnake]

One potential risk around this approach is this scenario:

• User sets the Site ID
• User fills out a bunch of repeats
• User changes the Site ID
• User submits form

Many of those repeat records won't have their Site IDs recalculated, they'll be stuck on the original value.  If you have repeats-within-repeats this is especially pronounced.  This isn't a risk if the Site ID is read-only (e.g. passed in through Field Maps integration, the inbox, or something else like that).

I handle this using this approach:

• Add Site ID fields to all repeats, but don't include questions for them in S123
• Add a "Processing Status" field to all layers and tables with two values, "Pending" (0) and "Ready" (1).  This will always be 0 when submitted.
• Have a scheduled notebook that finds all Pending records and cascades the Site ID value from the parent record down through all child and grandchild records, while flipping the status value to Ready.
• Most users (excluding admins) access data through a hosted feature layer view that restricts records to those flagged as Ready.

This process includes other validation, enrichment and notification steps as well, cascading of parent IDs is just one part of it.

[RachelWhittington]

Would I create this hidden field and its calculated field in the XLS sheet in Survey123 connect? 

How come I would have to check editing ArcGIS online if I'm using Pro? Is it because Survey123 connect is linked to ArcGIS online.

[Neal_t_k]

Yes, create the hidden field in the XLS sheet,  however @MobiusSnake makes a good point regarding user entered ID vs autogenerated ID.

For editing the feature class in ArcGIS Pro, I believe you have to turn editing on in the setting, and yes the feature class that Survey123 is using to store the entries is on ArcGIS Online (or your Enterprise system).

[rwhit15]

So, I entered ${SiteID_1} into the hidden field (body::esri::visible), and then in the calculation field, I entered ${SiteID_1} = ${SiteID} for each field that was a "begin repeat" type.

I ended up getting an error message saying that the survey element wasn't found. I also tried putting the hidden field and calculation fields within the repeat instead at the beginning of the repeat and that didn't work. I also checked ArcOnline and the editing is enabled. 

I am not sure where to go from here. Any thoughts? I have also tried changing the capitalization of the "site_id" to match the survey element but I'm not sure if I did it right.

[DougBrowning · Accepted solution]

No you just add a field inside the repeat called say SiteIDRepeatName then give that field a calculation of ${site_id} to pull the value into the repeat.  You are just trying to get the site_id value into each subtable.  

[rwhit15]

Thank you! This solved the problem 🙂

I do have one final question. Will I have to go in and re-enter all of my data for all of those repeat questions so that the SiteID automatically goes into those tables? Or manually re-enter the SiteID names into the SiteID column in Survey123 or ArcPro?

The fields have been created but they just have Null values right now. 

[DougBrowning]

You should be able to fix this in ArcPro.  Use the 123 relates and it will allow you to figure them out.  Select a parent, follow the relate, field calc in the site_id, repeat.  Will be a bit tedious depending on how many records. You could script if you are capable of that but easy to get into trouble with that also.

I suggest export to a GDB from AGOL as a backup first.  Then you can edit the service directly in Pro.