Survey123 Inspection workflow advice - Dealing with both parent and child fields

[Austin_21]

 Hello,

I would like to know the best approach to a survey123 workflow using existing features in ArcGIS Online (a site parent layer with an inspection related table 1:M). The relationship is set up with GlobalID as the primary key and GUID as a foreign key. I need to ensure that the parent fields can be updated based on the related inspection records (i.e Last Inspected Date, Inspection Status, etc.), but no new parent records are created, only new related records (inspections). I also need to use the fields from the parent layer and the related table in the report template. I have one survey where initial inspections are submitted, and another survey for review, using the same feature layer.

For those of you who have done this workflow or similar, how did you set this up? I am in the process of testing the behavior when both surveys target the parent table (leveraging the inbox only), but I am facing an issue where the report is pulling every related record, not just a single inspection record. I also am testing targeting the related tables for both surveys as I would like parent and child records in each survey, but to avoid schema issues, I am left with either using pulldata @layer function for the parent fields and setting as null types, or duplicating parent fields into the related table to store the parent values so they could be added into the report. Unfortunately, the pulldata @ layer has not worked for me as it takes too long for it to load the value in the survey, and the data is not persistent.  Of note, I tried using the relationship ${parent_table.fieldname} in the report that is generated from the related table, but I am only able to pull all of the records from the parent field, and not only the parent record that is related to the inspection record.

Also, if the related table is the target for the survey, and the parent fields need to be updated with the related records, is the best solution to use PowerAutomate/Make?

All of the videos I've seen keep the parent fields and related table fields in separate surveys, which does keep it fairly simple, but I would prefer if fields from both tables could be added to the same survey. Some of the posts I've seen related these topics and this workflow is a bit outdated, and some items may have been deprecated and are no longer relevant.

I’d really appreciate any insights or recent experiences with this type of setup. If you’ve found an approach that works well, or have run into similar issues and solved them, I’d love to hear how you tackled it.

Thanks in advance!

[TylerGraham2 · Accepted solution]

Check out the Report queries page, they specifically say to use resultRecordCount to specify the number of repeats to return.  If you just want the latest, probably set it to 1, but knowing how Esri sometimes doesn't tell the whole story for how tools work, I'd test it to make sure you are getting the record you need.  Report queries—ArcGIS Survey123 | Documentation

To use data from both the parent/related tables in a report the easiest way will probably be to create a survey that you only use to create reports with both tables included. Then you can use the parent table and call the related table using {#repeattable} method. This used to work in both directions, where you could call the parent layer from a survey made using only a child table, however it quit working and has been replaced with $layers. $layers has been very finicky in my experience and is very poorly documented, which is why I recommend creating a new survey and using the {#repeattable} method.

Repeat expressions—ArcGIS Survey123 | Documentation

Report expressions—ArcGIS Survey123 | Documentation (scroll to the very end for $layers)

Check out this esri video, specifically starting about the 8 minute mark for a layout similar to what you are looking for.  https://mediaspace.esri.com/media/t/1_3aw7zdrs

[Austin_21]

Thank you! The resultRecordCount set to 1 with an additional filter did work for the report generation. That video was very helpful in adding the right logic to the form.   

I am sticking to targeting the parent table for both surveys, using the inbox for both as it is the best way to update parent fields as the inspection forms are filled out. It is also good to review some of the example templates that ESRI provides within Survey123, along with the shared report templates. 

[KateBrigham]

Hello!

This is a little off topic but I am working on a workflow for fire hydrant inspections. None of the hydrants were surveyed so I used Survey123 to collect location and static attributes. I did not include a repeat in this original survey (I'm very new to this software). I am now trying to figure out how to create a one to many relationship with the hydrants feature layer and some sort of inspections log. I have been messing around in ArcGIS pro but I do not have data in the inspections log yet which is proving problematic. I am wondering if you could share how you went about setting up your parent/child relationship? I've been searching and searching Esri documentation and seminars but can't seem to figure it out. Thanks!

[TylerGraham2]

Did you create your feature service first in Pro and publish it, or did you use Survey123 Connect to create it?

If you used Survey123 Connect, you can add new repeats, groups, questions, etc to the survey and republish it. When you create a repeat in Survey123 it automatically creates the relationship in the feature service. I would download a copy of the feature service first because it may wipe the hosted one clean of data.  

If you published the feature service through Pro, you can't use Connect to add new fields or tables (repeats), you have to use Pro.  Start with your template database, the one you used to create a feature service in AGOL.  Create your child feature class/table in the database and add a GUID field to it.  The goal is to get the GUID to hold the parent table's GlobalID for the parent attribute. I'm currently having problems with a Pro software update, so this is generally by memory.  You add the table to the map that you used to publish to AGOL.  Right click on the table in the TOC and select the option to create relates.  If the option is in there, set it as a 1:M (1 to many) relationship.  Set it up so the parent table's globalid is using the GUID field you created as the rel_globablid.  That establishes the relationship.  I've found that I have the best results publishing if I then go into the Pro toolbox and run the tool named "create relationship class" or something similar to that.  You have to run through a few steps on naming things and verifying the right fields are being set for the relationship. If the relation type is selectable in there make sure it is 1:M. Run that tool and it will create a relationship class in the database.  Make sure your table names AND aliases have no spaces or special characters, or Survey123 Connect will show an error when you try to call the field name in the xlsform.  Publish your data (publish as a new one if you're testing, you don't want to break a production workflow or erase data).  If you want the repeat in an existing survey do "begin repeat" and in the name column type in the name of the repeat table.   

If you're creating a new survey that points to a child layer, in the settings table, the submission URL you need to append the appropriate layer ID number of the end of the URL.  For example: if the child layer has an ID number of 4 in the feature service your Submission URL would look like https://www.arcgis.com/sharing/rest/content/items/<featureserviceItemIDgoeshere>/4  Then you would set the form_ID column in settings to be the name of the child layer.  

[KateBrigham]

Thank you so much, this was incredibly helpful!