idea Survey123 - URL Parameters/Populate Attributes from Related Table 1:M Relationships in ArcGIS Survey123 Ideas

Survey123 - URL Parameters/Populate Attributes from Related Table 1:M Relationships

ElizabethHovsepian — Wed, 11 Dec 2019 22:38:14 GMT

When opening a survey by selecting a feature from a web app and populating attributes of the selected feature within the survey, I would like to populate attributes of a feature that has a relationship/related table to the feature that is selected. In example shown below, I would like to populate attributes not only from the pole (selected feature), but from the OH switch as well, such as facility ID, phase, voltage, etc. even if feature has a 1:M relationship. I would like to do this with minimal coding involved. This will be very valuable for field crews doing inspections as there will be less room for error as well as a huge time saver while collecting info on the assets.

I had submitted this as a question previously and came to conclusion is not working due to the 1:M relationship. Maybe there is a way to make it work? This is code i used: arcgis-survey123://?itemID=c910f2c47e8a4af1a74c85a80807bacb&field:pole_number={FACILITYID}&field:oh_switch_number={relationships/3/FACILITYID}

Thanks for consideration,

Elizabeth

Re: Survey123 - URL Parameters/Populate Attributes from Related Table 1:M Relationships

DavidForbuss1 — Fri, 13 Dec 2019 23:05:08 GMT

You should be able to create a view on your gdb that would add some columns to your parent table from the related table. We have this setup for some symbology on a webmap that we feed into Collector. The features are the parent table, and there is a related inspections table. Our SQL statement pulls the most recent inspection (lastprocessed) and determines how long ago it was (dayssincelastprocessed) and adds that number of days into the view's extra columns. I bet you could do the same thing in this situation but just pull the first record in the related table. You'd then publish the view as a feature service and feed it into your map. I'm not super good with SQL but maybe this could give a launch point:

The two columns added to the end of the parent table as a view:

The SQL statement we're using:

SELECT a.*,
(SELECT max(b.inspectiondate) FROM CriticalFacilitiesInspectionsTable_evw AS b
WHERE a.facilityid=b.facilityid) AS lastprocessed,
DATEDIFF(day,(SELECT max(b.inspectiondate) FROM CriticalFacilitiesInspectionsTable_evw AS b
WHERE a.facilityid=b.facilityid), GETDATE()) AS DaysSinceLastProcessed
FROM ssgravitymain_evw AS a

Re: Survey123 - URL Parameters/Populate Attributes from Related Table 1:M Relationships

ElizabethHovsepian — Fri, 07 Feb 2020 19:06:42 GMT

Thanks for your suggestions. I don't have much experience in sql either. Hopefully, one day, this idea may come to fruition without much user programming involved.

Elizabeth