Best way to fill Survey123 with parent and child layers with historic data

[EricaNova]

I am preparing some historic data to migrate from data entry and storage in MS Access to Survey123. 

My historic data are in a single, flat feature class. We want to share the data with a partner organization, but our data sharing agreement stipulates that we "fuzzy" the location data to three decimal places. We also want to filter the shared data to a particular time range, and filter to certain records. This seemed like a job for a hosted feature class view layer.

I can set up a new survey from my feature class, using the historic data to create the appropriate fields and field types in Survey123 Connect. I created a hidden repeat within my survey to create a child layer that contains a rounded geopoint, and copies the fields I want to share from my parent feature class. This is great - I can then create a hosted feature class view layer from the child layer, filter to the time and records I want, and share this with our partners.

The only hiccup is that I can't figure out how to populate the child layer with existing historic data. I can create the child layer separately in Pro, but when I try to append the child layer to the parent layer in AGOL, the two are not linked the same way new survey entries are.

Any ideas for how to append the historic data in the child layer? Maybe there is a way to force the feature class to recalculate any fields that are caculations? Or - how to do this a different way? I could simply use a feature layer view of the original feature class if only I could add a calculation on the geopoint feature!

Here's a screenshot of my test Survey's xlsx form. Maybe there is a way to force the child layer's data to re-calculate? The child layer will only ever have copies or calculations of fields that exist in the parent layer. 

[DougBrowning]

You have to add then set the parentglobalid field - which should be there already in from the 123 form you built.  Hopefully you have some other key to join on from the historical then you can run a field calc to get the GlobalID of the parent into the child parentglobalid field.  If not make one before importing.  I always have 2 keys, one I made that is logical combo of fields an then the one 123 builds in with the parentglobalid so that the forms work properly.

Hope that makes sense.  Run a test copy first of course.

[EricaNova]

Hi @DougBrowning , I actually tried entering in a few "test" observations into the new survey123 form (so that I would have properly set up Parent and Child GlobalIDs), downloaded both layers (parent and child), zipped them as shapefiles, and attempted to append the data back to my survey (once I'd deleted the test data). This failed and it did not connect the two layers. Maybe I'm missing a step?

[DougBrowning]

Well when you export then import the globalids changed on you.  Thats why I don't use them.  So you have to set a key somewhere then fix them once you add them back in.  It's a pain for sure.

[EricaNova]

@DougBrowning I'm not sure with these instructions I have enough to solve my issue 🤔

[EricaNova]

There is some instruction here, "Append data to a Survey123 for ArcGIS survey from an existing survey with related tables", but @DougBrowning is correct - while my child layer retained the correct GlobalID and ParentGlobalID, the globalids were changed in my parent layer. 

[DougBrowning]

Coming from historical data you won't have this issue since it never had globalids to begin with.  But one way to do this is create a tempGlobalID text field and calc the globalid into it.  That way when you add them back in you can join on this text field and then fix the parentglobalid field.

Does that help?

[EricaNova]

@DougBrowning thanks for your patience 🙂 I agree, the historic data won't necessarily have GlobalIDs. I'm sorry, I think I would need more step by step instructions. I don't know when this "joining" would happen, there doesn't seem to be an option to join. And I don't know how I would populate one table from the other. I'm sure what you're suggesting would work... just need more clarity/detail on specific steps. You may not have time to do that and that's ok!

[EricaNova]

@DougBrowning apparently your advice WAS enough to solve it. I'll post the solution with step-by step directions so I don't forget them and others can reference. Thank you!

[EricaNova · Accepted solution]

Thanks to hints from @DougBrowning I was able to figure out the solution to this issue. 

If you are starting from an empty survey, where you have a parent table and hidden repeat that acts as a child table (see my xlsx form screenshot), this is how to upload existing data to the survey, while preserving the relationship between the two tables:

Create the parent table in ArcGIS Pro to match the survey's schema. I did this by creating the survey the way I wanted using Survey123 Connect, creating a test point, then downloading the test point so I knew how to format everything. Ensure the parent table has a GlobalID field. You may need to calculate this in ArcGIS Pro.

Create the child table in ArcGIS Pro, preserving the way fields are calculated (e.g., I would round the latitudes and longitudes from my parent table to create my "fuzzied" locations). Ensure this layer has a GlobalID field AND a ParentGlobalID field. You can fill the ParentGlobalID field with any text for now, including a dummy GlobalID. You should also have at least one field, e.g., ObjectID, that is a unique identifier and is present in both the parent and child layers. The parent and child layer should have the same number of rows. 

Save both the parent and child layers in a file geodatabase. Zip the file geodatabase.

Ensure your survey is empty - delete any records that are currently there. 

Navigate to your survey's child layer in AGOL and in the Overview section, click "Update data" and then "Add features". Select your zipped file geodatabase, select file geodatabase, and select the child layer you created. Choose to append to the child layer. Keep clicking next until it appends.

Next, navigate to your survey's parent layer in AGOL and in the Overview section, click "Update data" and then choose "add and update features". Select the parent layer when prompted from your zip file. I chose "object ID" for the unique identifier. Click through, accepting defaults and selecting to update both geometries and attributes. 

Now, open both of your AGOL feature classes in ArcGIS Pro from your "my content" section. You will need to update the ParentGlobalIDs of the child layer to match the GlobalIDs in the parent layer. Ensure both layers are sorted by the unique ObjectID (or in the same order) and then go to the parent layer and copy the GlobalIDs by clicking the first one, pressing shift, clicking the last one, right-clicking and pressing "Copy". In the child layer, select the entries in the "ParentGlobalID" field in the same way, and then right-click and PASTE. It may be safer to do this with a join but I was working with a test survey with two records 🙂 If you've done this correctly, you should be able to navigate to your parent layer's data from the Overview section and see that there is a form attached to each record:

and if you click on the record, the related data should appear: