can i build a survey that populates a table *not* related to the spatial data layer in my existing feature service?

1362
9
07-12-2018 10:10 AM
LynnBerni
Occasional Contributor II

Hello,

I am attempting to tweak the XLS form fields created from an existing feature service in order to populate two tables, but it isn't working. I think I understand why but not how to fix the problem...or even if what I'm trying to do is possible.

Here's the basic structure of my data in SDE:

Sample Sites (point data)

==> Related Table:  Bacteria Samples [1:M, SiteHasSamples, related on SITEID fields]

 

Bacteria Batch (table)

==> Related Table: Bacteria Samples [1:M, BatchHasSamples, related on GlobalID/ParentGlobalID fields]

 

 

What I want is a survey that populates the Bacteria BatchBacteria Samples tables only. But, since the Batch table is not related to SampleSites, it does not get included in the XLS form created from the existing feature service.  Here are my tweaks to the XLS to include Batch in the survey:

  1. delete all fields related to Sample Sites
  2. replace them with my Bacteria Batch fields
  3. keeping Bacteria Samples as a repeat
  4. change the form_id to pweng.SLCOEN.BacteriaBatch

But it's not working. I've been working my way through each error that the survey snags on when trying to publish it in Survey123 Connect (v2.6.6).  Here's the current error: 

I assume this is because I kept the samples fields in a repeat, and it thinks the repeat should be based on SITEID, but it's not anymore. As previously mentioned, I think I understand the problem but not how to fix it...or even if what I'm trying to do is possible.

I really, really want to figure out how to make this survey work. The fallback option would be to have 2 separate surveys and then manually copy in the unique BATCHID to each sample processed in the batch. Not horrible, but definitely not ideal.

Please help!!!
Thanks,
Lynn

p.s.  More screenshots for context:

0 Kudos
9 Replies
LynnBerni
Occasional Contributor II

Hello James Tedrick...have I stumped you?   Or maybe you just haven't seen this yet.

Any thoughts?

Thanks!

0 Kudos
LynnBerni
Occasional Contributor II

Ismael Chivite‌ can you provide me with assistance on this issue?

Thanks!

0 Kudos
IsmaelChivite
Esri Notable Contributor

Hi Lynn. This is possible, although not straight-forward.  The reason why this is not easy is because Connect will not automatically create an XLSForm for you when you go into New Survey->From Feature Service. Well, you already know that, but not everything is lost.  From what you describe in your question you are ALMOST there and you are in the right track. I will describe step by step for more clarity:

Essentially, you need to create an XLSForm manually.  In this XLSForm, you need to:

  1. Specify in the Settings worksheet the URL of the feature layer item as well as the layer/table you want to work against
  2. Specify in the Survey worksheet all the fields in your table on which you want to capture data with the survey

To do #1, I suggest you do the following:

  • Go into Connect and Create a New Survey from the Feature Service that contains your standalone table. Please make sure that your Feature Service actually has the table in it, even if it not related to any other layer.  We already know that Survey123 Connect will ignore the standalone table, but at least you will get the submission_url value populated in the Settings tab of your XLSForm.   The submission URL tells Survey123 the location of the feature service you want to hit and in your case, the value will be correct.
  • Now, in that same Settings worksheet you will find a column called FormID.  That column contains the name of the layer in your feature service that will be used as the parent layer of your survey.  The value will be incorrect in your case, because by default Connect simply looks at the first layer in your feature service.  In your case, you will want to replace the value with the name of your standalone table as shown in the feature service Services Directory or the Feature Service Item Details Page.

While not everythig in https://community.esri.com/groups/survey123/blog/2017/09/25/working-with-existing-feature-services-i... will apply to you, you may find some useful bits of info in case that concepts like submission url and form_id are not clear.

To do #2:

  • With all the above, your survey knows exactly what standalone table to use, but all the questions in the Survey worksheet are now out of sync, because they refer to the first layer in your feature service. So go ahead and delete all the rows in the Survey worksheet. I said ALL rows, including repeats.
  • Now, you need to add, one by one, questions in your survey that will target fields in your standalone table. I suggest you go through the https://community.esri.com/groups/survey123/blog/2015/08/24/xlsform-mappings-to-arcgis-feature-servi... blog post for details on this. Do this carefully!

Important: Now, all the above is only valid if your feature service is compatible with Survey123, and Survey123 can be a bit picky!  The error you are describing indicates that your relationship is using string fields. Unfortunately, as of version 3.0 Survey123 no longer supports working with relationships unless they leverage GlobalID fields.  There is more info about this here: https://community.esri.com/groups/survey123/blog/2018/07/17/how-to-turn-supportsapplyeditswithgloabl... 

Hope this helps!

0 Kudos
by Anonymous User
Not applicable

Hi Lynn,

Further to Ismael's comments, you need to make sure you have a GUID relationship between the Bacteria Batch and Bacteria Samples tables. I believe this is the cause of the error you are getting in Connect. You likely have GlobalID/GUID relationships between the other layers/tables, but possibly not these two?

Phil.

0 Kudos
LynnBerni
Occasional Contributor II

Thank you Ismael Chivite and Philip Wilson for your help on this.

Ismael, I am pretty sure that I've already completed the steps that you outlined, haven't I?:

Here are my tweaks to the XLS to include Batch in the survey:

  1. delete all fields related to Sample Sites
  2. replace them with my Bacteria Batch fields
  3. keep Bacteria Samples fields in the repeat
  4. change the form_id to pweng.SLCOEN.BacteriaBatch

As for the GlobalID/GUID relationship, I thought we had that covered as well.  BacteriaBatch has a GlobalID field, and BacteriaSamples has a GUID field named PARENTGLOBALID. The BacteriaBatchHasSamples relationship class is built on these fields. 

I don't understand why the publishing error is flagging the SITEID field, because that field isn't involved in the relationship between the two tables. Unless it somehow "remembers" that the BacteriaSamples fields were originally in a repeat based on SITEID (when I created the survey from the existing feature service). 

Here are links to my XLS forms:

Hopefully these will provide additional relevant information.

Thanks!

Lynn

0 Kudos
by Anonymous User
Not applicable

Hi Lynn,

Thanks for the additional information. What type of field in the SITEID used for the relationships in your original diagram of the tables, this need to be GlobalID/GUID relationship also.

Phil.

0 Kudos
LynnBerni
Occasional Contributor II

SITEID is a text field. It is the unique location code for our sampling sites (e.g. BC_25.97). We need relationships built on SITEID between the sites and the various tables which hold the  various types of data collected at each site (field parameters, bacteria, macroinvertebrates etc.).  A Global ID/GUID will not work for these relationships. 

I'm just not clear on why SITEID is being flagged at all.  The relationship class between the Bacteria Batch table and the Bacteria Samples table is built on a GlobalID/GUID.  When I updated the XLS form, I removed all fields related to the sample_sites points (including SITEID) and changed the form_ID to the BacteriaBatch table.  Yes, there is still a SITEID field in the BacteriaSamples repeat, but that shouldn't matter. We have a survey published that's populating the FieldParameters table, which was also created from the existing feature service....and it doesn't get hung up on SITEID.

0 Kudos
LynnBerni
Occasional Contributor II

Sorry to bug you Ismael Chivite and James Tedrick, but I'm getting nowhere fast in my efforts to publish this survey. Can you please take another look at my comments and the information that I've provided. 
Thanks much!
Lynn  

0 Kudos
MichaelKelly
Occasional Contributor III

Hi Lynn,

When you edit this data in ArcGIS Online, does the related data act as intended? I would suggest testing via the Web AppBuilder Edit widget to see - check adding, updating and deleting.

I tend to use is a 'Composite' type of relationship class (I notice you are using a 'Simple' relationship class) when setting up related data. 'Composite' types are typically used when setting up related records - it is the only way related records are supported in Collector. I'm not sure whether the same applies to Survey123 (it may be an ArcGIS Online limitation), but it's one worth persuing. 

Another option is to create a Feature Layer using Survey123 Connect - this will ensure that the relationships are being setup appropriately. Publish as much of the schema as possible this way (some of it will still have to be setup via Desktop due to the complexity). Once you do this, you can download the File Geodatabase and add any relationships you may need.

If you are using SITEID as part of the relationship, as Ismael mentioned, bear in mind that you will have to use Survey123 Classic. As of 3.x, relationships have to be setup using Global ID's.

Mikie

0 Kudos