Stuck on ArcGIS Online Feature Layers and Relates: Need Expert Help

Question

Hello Everyone,

I'm facing some challenges with relates and ArcGIS Online hosted feature layers. My boss is VERY keen to get this resolved, and I'm at a sticking point. Here's a breakdown:

Data I Have:

* A feature class containing polygons for each county in the United States.
* A CSV file with multiple rows of data for each county.
* A unique numerical value shared between my CSV file and feature class to identify each county.
* Arcgis Pro Desktop Basic

Objectives:

* Create a hosted Feature Layer in ArcGIS Online that can be used in both ArcGIS Online maps and dashboards, containing all rows of data for each county from the CSV file.
* Enable the updating of this feature layer as new versions of the CSV file become available.

Achievements and Limitations:

Scenario 1:

* Achievement: In ArcGIS Pro Basic, I successfully performed a relate to combine the data in my CSV file with the polygons in my feature class.
* Limitation: I can't publish this feature class to ArcGIS Online due to the relate. The error message is "24040 In-Memory relates are not supported."

Scenario 2:

* Achievement: I managed to join my CSV file to my feature layer by following guidance from this video, resulting in a new feature layer usable in ArcGIS Online Maps and Dashboards.
* Limitation: I can't quickly update or overwrite this feature layer with an updated version of the CSV file, meaning no updates can be made.

Requirements:

The ability to update or overwrite my feature layer in ArcGIS Online is critical, as it serves as the basic building block for almost all maps or dashboards I create. Creating a new feature layer and redirecting all existing maps and dashboards is not an option.

Closing Thoughts:

* I only need to update my feature layer in ArcGIS Online around 5-6 times a year and am willing to manually perform tasks as required.
* We're open to upgrading our ArcGIS Pro license from Basic to Standard if this enhances my options.
* My boss has made it clear that purchasing ArcGIS Enterprise is not an option :( .

Can anyone offer some guidance?

amyw · Answer

"Add relationship class" works for establishing a relate before publishing to ArcGIS Online

jcarlson · Answer

To answer your question, yes, having a single row for each feature is preferable. The tools available in AGOL bring their own constraints, and it's not always possible to keep large / complex datasets clean and efficient, but whenever possible, you should strive for database normalization.

https://en.wikipedia.org/wiki/Database_normalization

So in this case, any information about the counties should be in its own table and not duplicated across rows. Information that repeats for a given feature in that table/layer should be in a separate table and link in using a key field.

For human users, it can be tempting to keep things in a big flat table, but your data will be better off in separate related tables.

JonJones1 · Answer

Dear Sir,

Once again, you have outdone yourself! The depth of your response, especially the caveats you highlighted, has been incredibly enlightening. In my situation, this would have led to 66 duplicate instances of a county polygon due to my data table containing multiple rows of data for each feature. Thank you so much for bringing this to my attention.

I now understand that I should change directions. Initially, I had another post inquiring about labeling features from multiple rows of data, linked below. However, it now appears that the "cleanest" approach, at least for the current version of ArcGIS Online, is to maintain a single row of data per feature. However, it is acceptable to have my data in a separate table, but a user needs to be aware of the caveats.

In closing, I have one question: do you agree that maintaining a single row of data for each feature is not just simpler but perhaps also best practice for data in ArcGIS Online?

I am profoundly grateful for your guidance.

https://community.esri.com/t5/arcgis-online-questions/is-it-possible-in-arcgis-online-dashboards-to-have/m-p/1343066#M55403