Updating relational fields in online dashboard

[BenjamenWetherill]

I feel like there must be an easier way to do this, but I can't figure it out.  I want to easily update sample results for geolocated sites in a dashboard without reassigning and reloading the data to the sites each time.

I have some sites that are sampled for E.coli bacteria on a regular basis.  I would like to easily update new result data for the same sites each week.  The only solution I have been able to figure out is as follows:

1.  Create an Excel file with all results data (full history) with fields: Date, Site, Result.  And upload to ArcPro as a standalone table.

2.  Join my sites feature layer one-to-many with the standalone results table.  Join by Site.

3.  Export features of my joined sites layer to a new layer in ArcPro.  Then remove join on original sites feature layer so it is available for future use.

4.  Share the newly created exported layer as a Web Layer.  This new layer has duplicative repeated feature rows for each date a site was sampled.  Fields:  Site from feature layer, Date, Site, Result.

5.  Republish the dashboard with the newly shared layer.

It would be much simpler if I just had one unchanging feature layer of non-repeated sites and this layer was related as a relational database to a freshly uploaded table of results data.  Is this not possible in ArcGIS Online?

[bbollin]

@BenjamenWetherillhave you looked at update data? Here is a blog you might find useful: https://www.esri.com/arcgis-blog/products/arcgis-online/data-management/updating-data-in-arcgis-onli...

If you are python savvy you can also write a script to automate this workflow too, but the above blog walks through how simple the experience is today in AGOL

[MobiusSnake · Accepted solution]

You can create a hosted feature layer in ArcGIS Online that includes a layer and a related table.  The best way to do it (in my opinion) depends on your Pro license.

If you have a Pro Standard or Pro Advanced licensed, I would create a File GDB with a feature class (your layer properties) and a table (your sampling data).  Add a Global ID to the feature class and a GUID foreign key field to the table.  Create a 1:M cascading relationship class between the two, using the Global ID as the primary key and the GUID as the foreign key.  Zip the FGDB and post to AGOL to create a new service.

If you have a Pro Basic license you can't create relationship classes, so in that case your best bet is to find an AGOL template that has a similar schema and modify it to suit your needs.  If you create a new layer in AGOL and search the templates, you'll see "Water Quality Inspection" (as well as some others) has this 1 layer -> 1 related table schema, you could probably roll it out and then drop and add the fields to do what you need.

Rather than republishing, you'd probably want to get new data in there through one of a few ways:

• Give users a tabular view in something like Experience Builder
• Give users a Survey123 form for site data collection that will append to the existing results (possibly with Field Maps integration)
• Append new Excel results to the table on a regular basis (this would probably require adding in some GUIDs to make the relationship work)
• Write a Python script that posts new results to the related table and links them to the appropriate parent features

[BenjamenWetherill]

I think this is exactly what I'm looking for!  But, I don't understand the objective of the GlobalID and GUID fields.  What is the purpose of the cryptic registry style field values?  I want to create a relationship based on SiteID values which I understand.  Why do I need to use these cryptic values?  I assume I will need to map the cryptic GlobalID values to the GUID field somehow.

[MobiusSnake]

I think you could use Site IDs as well, using regular integer fields as keys in a relationship class used to be something I saw fairly often in desktop applications but I don't see it much in ArcGIS Online.  I think a big reason is because Global IDs ensure global uniqueness, they're automatically generated and managed by the data store and are read-only, so you don't have to worry about things like primary key duplication, or having your primary key change.  You could create a unique index on your Site ID field as well, but violating that uniqueness constraint tends to result in some cryptic error messages as well.

I'm not sure if any of the templates use integer keys (I believe GlobalID/GUID links are the standard across templates), but if you have a Pro Standard or Advanced license, I'd give the FGDB approach with an integer key a try and see how it goes for you.

[BenjamenWetherill]

Thanks for all the help. I was able to create the relational File GDB and upload it to AGOL, but I can't figure out how to symbolize based on the data in the related table.  The symbolization only provides access to the fields in the feature class.

[MobiusSnake]

Yeah, that's a trickier problem, I'm not sure there's a simple way to do that.

One approach I've looked into in the past is using an Arcade expression that finds related records and applies symbology based on a calculation of the resulting records.  I believe finding related records is blocked in web maps' symbology contexts however - I'm guessing because the performance might not be great, but that's speculation on my part.

I'm not sure how you'll be refreshing your related table data, but is it possible to create a field dedicated to symbology in your feature class, then apply a calculation to each record at the time you load those related records in?

[BenjamenWetherill]

If I understand your suggestion correctly, I don't think that would work because I want to have the option of symbolizing multiple values per site.  The scenario is that I have sites in my feature class and the related table has values for each site for multiple years.  The user would then select which year they want to view symbolization for.

I'm also thinking that something like that would defeat the purpose of trying to find the simplest solution for updating data.  I'm starting to think that my original method is the simplest, with step 4 changing to updating the web layer from AGOL instead of sharing the new layer from Arc Pro.