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.