Survey123 feature class as a related table

[GordonSumerling]

Hello All,

I need to understand how it might be possible for my Survey123 feature class to be a related table to a polygon feature in the map.

In essence I have a farm field feature class and I want to perform an inspection against this. Conventionally, I would create a feature class to represent the farm in ArcMap and then create a related feature class in ArcGIS that is the inspection feature class. Now I could use this in collector. However, I want to take advantage of the field manipulation capability of Survey123. 

The inspector already knows the farm ID so he just needs to perform the inspection with survey123. So the question is how do I publish the survey123 feature class and then relate it to the farm polygon feature class in ArcGIS Online?

Cheers

Gordon

[deleted-user-1OQrlDk8rc8p]

Hi James,

I have read the whole thread, but I am confused how the globalID even gets transferred to the form for an existing feature service if it is not added per the blog Working with Existing Feature Services in Survey123 for ArcGIS.  

I've created and edited the form in Survey123 Connect and am not understanding this step that @Ismael Chivite stated "Then change the FormID value in the Settings tab so it references the name of the related table you want to edit". The table I want to edit is the table in the feature service. Should I create another table that relates to the feature service first then create a form in Survey123 Connect and follow the steps? 

Also to clarify the work around for relating Survey123 forms with existing polygon feature services:

First you create the survey for an existing feature service, delete all  question fields relative to the parent layer. Does that translate to delete all the existing questions and add the questions you ultimately want on the form? Should I export the data to a table and add it to my contents and then use that new added table as the form_id?

Apologize for the massive confusion I have with this, I am new to Survey123.

Thank you

[JamesTedrick]

Hi Vicky,

Transferring the GlobalID & creating a survey from an existing feature service are two related but separate concepts. First, Ismael's blog post presupposes that you have a minimum of 2 layers already:

• the 'asset' layer of things you want to inspect
• the 'inspection' layer (or table, if lacking shape storage) with the inspection questions

These two layers all have a relationship class defining the 1:M relationship (one asset can have many inspections), using GlobalID or other GUID values.  You have also published both of these layers out in the same feature service, usually with the asset layer first.

When creating a survey from a feature service, Survey123 Connect automatically assumes the first layer (usually #0) is the one you want to be the parent of the survey form and populates the FormID value in the Settings Tab of the Excel worksheet.  If you want another layer/table (in ArcGIS technical-ese, a layer is a table with shapes, a table lacks shapes) to be the parent of the form (i.e., questions not in a repeat go to a field in that table), you will need to change the value of the FormID to be the name of that layer/table.  

Next is to make sure that the form can accept the GlobalID of the parent in the form.  You will then need to manually add the parentglobalid field (or other name for the parent key field) to the survey as a question (likely hidden or a note) and make sure the bind::esri:esriFeildType is set to esriFieldTypeGuid.  That provides you a place to tell the survey which feature you are referencing.

To repopulate values, including the GlobalID of the asset, you create a link in the pop-up of the asset layer in the pattern described in https://community.esri.com/groups/survey123/blog/2016/07/30/understanding-survey123s-custom-url-sche... .  This will include a parameter of field:<parentglobalid>={GlobalID} , substituting the correct values for your layer & table.

I hope this helps!

[TimothyKing3]

Hi James,

I have a workflow that I would like to use Survey123 for.

I am using Collector/Survey123 to collect stratigraphic information on 1x1m units.  The field crew open the survey via Collector and fill out the Survey123 form.  I have three repeats, a strat levels form that contains information on each level within a strat, a feature log that contains information on any features found within the unit, and a field specimen log that tracks samples coming from the unit.  I would like to use the data from the strat levels related table to populate a new Survey Form that would be a Unit Summary that would contain all of the strat, feature and FS data along with a few additional questions and final photos of the unit.  I am thinking this would be possible by creating a new survey from the existing feature service and tweaking it but I am wondering how I would be able to select the information the individual unit I would need.  Each related table contains unique IDs using the  Site# and Unit # along with the global ids.  I will have to read through this post a few more times as I think that will help me wrap my head around the issue.

Thanks,

LTK

[LynnBerni]

Hello James,

Thank you for your succinct summarry [Dec 7, 2017] of the comments in this post. 

Per your notes  "Next is to make sure that the form can accept the GlobalID of the parent in the form."You will then need to manually add the parentglobalid field (or other name for the parent key field) to the survey as a question " ,  I am attempting to follow the steps outlined in Chris Goessel's comment [Jan 16, 2018].  But I am unsure as to how to create the URL connection to the survey. 

I've built my survey on an existing feature service in our internal Portal. Here's my submission URL:

https://www.slcounty.org/gisportal/sharing/rest/content/items/659e2a7851494f0b84c0de1f89eff7b2.

I have another survey that is working just fine with this submission URL, but it doesn't have repeats!

Also, I have not yet been able to publish this survey, as I keep getting the following error: "The Feature Service does not meet the requirement for a survey with repeats - supportsApplyEditsWithGlobalIds".  Which is why I am trying to setup the GlobalID and ParentGlobalID correctly.  I think that's the key to my problem.

Also, once I do determine the correct custom URL scheme, where exactly does that information go in my survey??  In the calculate field?

Please advise,

Thanks! Lynn

[JamesTedrick]

Hi Lynn,

It sounds like the feature service/tables you are attempting to submit to are not quite configured correctly.  In order for Survey123 to submit to an existing set of tables, the following requirements must be met (as described in Use Survey123 with existing feature services—Survey123 for ArcGIS | ArcGIS )

- Each layer/table must have GlobalIDs enabled

- If there are attachments, the attachment relationship must use GlobalIDs (this is normally done by enabling attachments after enabling GlobalIDs)

- relationships between layers/tables must be of 1:M type and must use a GUID field for the key; traditionally this is done by using the GlobalID field in the parent layer and having a field of type GUID (usually called parentgolbalid or similar) in the child tables

When publishing the tables to a feature service, all layers being published must meet these requirements.

[LynnBerni]

Thanks for the speedy reply!  I'm fairly certain it's the GlobalID's that are holding us up.  We've resolved the first two items on the list. What I'm not clear on, after reading this post and all comments, is the correct way to add  the"parentglobalid". Is it enough to add a GUID field in my related table and name it "ParentGlobalID"?  Will it then be populated automatically when surveys are submitted?   Or do I need to include hidden fields in the survey to capture the globalID and then transfer that to a parentglobalID field in the repeat?

[JamesTedrick]

Hi Lynn,

The field, as you describe, needs to be added.  If the form doesn't have the parent layer in it (the form only has the related table), you will need to add the parentglobalid field to the form.  The relationship class connecting the parent and related table must use the globalid and parentglobalid fields respectively. Given the scenario you describe (both parent and related table in hte form), these fields should not appear in the survey- they are normally filled in automatically.  It is easiest to generate the survey from the feature service after it is published; this will take care of some of these issues automatically.

[YukunXing]

I could really use some help to understand what I'm doing wrong.I used Collector before but am new to Survey123. Initially I published a feature service with existing data, selected a feature in Collector, and opened Survey123 via pop-up/URL. While the survey fields were "pre-populated" correctly, submitting the survey would create a new feature instead of updating the existing one. My first question - is it possible to update existing features with Survey123, and if so what I might have missed?

It was at this point that I bumped into this thread. As a completely separate test I have been trying to make the related table approach work since yesterday, without much luck. Mostly I have been following Ismael's June 15, 2017 update and the Dec 7, 2017 "summary" by James. It seems I'm having issue with the GUID field (foreign key) of the related table. If it is added as a field to the survey, I got an error when trying to upload the survey, because that field is not editable. If it is not part of the survey, the survey can be uploaded, but when submitting survey data I run into a INSERT error again related to the same field, which I guess because no value is provided for it.

Details
+++++++++++++++++++++++++++++++++++++

Created the "asset" layer, created three features. Added and populated the "GlobalID" field (GUID).

Created a blank "Inspections" table, added a "GlobalID" field (GUID).

Created a relationship class (asset: Inspections, 1:M), with the respective "GlobalID" fields being the primary and foreign keys.

Published "asset"/"Inspection" as a feature service.

In Survey123Connect, created a survey against that service.

Changed "form_id" in the settings tab to "Inspections".

Deleted all questions related to fields of the "asset" layer from the survey - I noticed the generated survey didn't have any "GlobalID" field to being with.

Delete the two "repeat" lines from the survey.

If I don't add "GlobalID" to the survey, no matter whether I attach the "&field:GlobalID={GlobalID}" part to the pop-up URL or not, when submitting survey data I get this error: "Add error code 1000 - the INSERT statement conflicted with the FOREIGN KEY constraint "REL_FK_ASSET_INSPECTIONS_INSPECTIONS_0". The conflict occurred in database ..., column 'GlobalID' ".

If I add "GlobalID" to the survey as a text question, this was the error message I got when uploading the survey: "The custom feature service submission url is not compatible with this survey (Field GlobalID in Inspections is not editable)".

I see Chris' post above about storing the parent GUID passed by the URL in a separate/additional GUID field. So I added a separate GUID field ("parent_GlobalID", in additon to "GlobalID"), and used it instead of "GLobalID" (the foreign key) to hold the parent GlobalID passed via URL. I was able to upload the survey, but when submitting data I ran into the same INSERT error as above whether field binding is set to null or "esriFieldTypeGUID".

I then added another GUID field ("parent_GlobalIDorig") to "Inspections" and the survey. Field binding is set to null, and passed parent GlobalID into this field in the URL. Field binding for "parent_GlobalID" is set to "esriFieldTypeGUID" this time, and its calculation is set to:
if(string-length(${parent_GlobalIDorig})>36,substr(${parent_GlobalIDorig}, 1, string-length(${parent_GlobalIDorig}) - 1),${parent_GlobalIDorig})
Ran into the same INSERT error again.

The last thing I tried was to replace "parent_GlobalIDorig" with "GlobalID" in the survey. Once I do that I couldn't upload the survey, because "GlobalID" is not editable.

[JamesTedrick]

Hi Yukun,

On the first question - Survey123 does support editing existing features through the use of the Inbox functionality (see Prepare for editing existing survey data—Survey123 for ArcGIS | ArcGIS ); however it does yet support editing existing features when opened in via a URL.

As mentioned above, it looks like a misconfiguration is present at teh database level- the parent key / foreign key field should not be the GlobalID field; rather, a new GUID field (we often call it parentglobalid) should be added.

[YukunXing]

Thanks James for the clarification on updating existing data with Survey123.

And you are right about the problem with my survey for the related table. I'm not completely sure what caused the ID fields to be non-editable, but it was possible I used the "add global ids" tool to create them, and if that's the case it might be the reason. Seeing your last reply I manually added a new GUID field ("parentID") to the related table and re-created the relationship class ("parentID" being the foreign key), re-published the feature/table, and added the "parentID" field to the survey, passed the primary key value from the parent to it with URL, and I was able to successfully submit a survey. When i select the asset point in ArcGIS Online viewer, I could see the related records of the "Inspections" table containing the data/photo I just submitted.

So it seems the workflow is relatively straightforward at this point. I didn't use any intermediate field for parent ID calculation. I will try offline mode next and see whether I'll run into issues with "{}". 

Update: Tried launching Survey123 from offline map in Collector and filling out the survey (both celluar and wi-fi are off), and turning network back on to send the saved survey. Worked without any issue - a record was created in the "Inspection" table, and it was associated to the correct asset point.

I really appreciate all your help on this James.