nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Symbolizing one to many relationship in ArcGIS online

wlavell

I am making a webmap in AGOL which will then be later used in in the Arc Dashboard. I have a table which has data collected by date and I want to create a filter which will show those polygons by a specific date.

The issue is that the visualization needs to have a one to many relationship with the data table.

1 province can have 12 months of reported data.

I need to visualize the number of persons per province per month. I can make the table join in AGOL using the one to many relationship but the visualization itself then will symbolize by the first record it finds.

As well the updates on this will be fed from a GoogleSheet.

The proposed architecture is
-GoogleSheets for data collection
- upload as CSV table to AGOL
- attribute join with one to many relationship from the table to the provincial feature layer
- make a dashboard with ArcGIS dashboard.

I have been reading that this might not be possible to symbolize with this approach. But I have seen dashboards visualizing by days using ArcGIS dashboard, so there must be ways to do this.

Is there a better tool to do this in as well? Perhaps the ArcGIS app builder or App studio?

Trouver plus de publications taguées avec

Accepted answers

All comments

jcarlson

So after the attribute join, what does the output layer look like? Honestly, performing the join should "bake in" the attributes from your table, allowing you to visualize by them. You've read correctly, you can not symbolize a layer by a related table. But what you're describing isn't that.

Have you actually attempted the 'proposed architecture' process you describe? And if so, how exactly are you symbolizing your features afterward?

Or is the issue that you want to aggregate the data over an entire month, and your data is by date?

wlavell

Ideally, I would like to make a dashboard similar to what you see with Tableau and Power BI.

Data Structure:
Two tables: 1 spatial, 1 CSV non spatial table
Province - spatial layer
fields:
Province name
Province name pin
1 row per province and PIN

Reporting_table.csv
fields:
province - many rows, not unique
province_PIN - many rows
month - many rows, not unique
agency - many rows, not unique

Ideally, the calculation should do a dynamic sum of province perhaps on the reporting table and visualize by the that. In Power BI you just make a join and then can choose to symbolize by that related table using a sum. In AGOL it seems that you need to make the calculations in advance ?

The goal would be

As a user I can see the number of persons helped per province and filter per month and then per agency and change these interactively based on my criteria.

When doing the join from 1 to many I can relate the other table but cannot visualize using a sum for the related table.

The province layer default state would be
- Total of all and then the user can filter down to what they want.

Also the GoogleSheet will be updated on a monthly basis and so the reason why we used GoogleSheets for our reporting. It would be ideal if the spatial layer could update on a regular period say once every 3.5 weeks

jcarlson

Got it. Well, your post covers a couple of very common questions asked here in the Community.

1. Symbolizing by related tables.

Unfortunately, this isn't possible using a layer and separate table, even if there is a relationship established between them. The symbology profile in AGOL does not support one feature accessing the attributes of other features or layers. In order to use the table's attributes to symbolize your features, you'll need them to be "baked in" somehow.

One way of doing this is to perform a join in AGOL using the Analysis tab. You could also attempt to create a hosted view layer using an attribute join. In your case, you could join this based on both the province and PIN fields together. This is a good option, as the hosted view layer remains up to date as its parent layers are edited / updated. No need to re-join and replace your layer, and the output view will behave as though the source attributes were all in a single table, which, importantly, allows you to visualize based on the joined fields.

2. Automatically refreshing from a Google Sheet

This is a very popular question on here. You can find lots of similar posts detailing different iterations of the problem, but yours seems very straightforward. If the updates are only coming one way (Google Sheet → AGOL table), then you should be able to publish the sheet, and whenever you need an update, click Update, then Overwrite.

You'd have to do this manually, unless you devise a means of automating it using the ArcGIS Python API. Doing so is not difficult, but may be a bit much for something that's just a few mouse clicks every month.

Final note: to make this easier for joining / updating, make sure that the parent layers are separate items in AGOL. Having the table and spatial layer in a single service would risk something going wrong in the spatial layer when overwriting the table.

AmandaRing

@wlavell To build on Josh's great answer, based on the following line, it sounds like you might be looking to summarize a field in your join table to get the number of clients per province:
"When doing the join from 1 to many I can relate the other table but cannot visualize using a sum for the related table."
If so, you may in fact want to build a one-to-one join, using the Add statistics option. This would allow you to calculate the sum, for example, of a numeric or date field currently in the table you are trying to join, for each matching record in the target data. By default, the count of matching records will also be calculated and added to the result layer. You should then be able to symbolize based on an output field statistic field.

At this time, the count option is not available as a statistic by itself, so if you were just looking to get a count of the number of matching records per province, you could either:
A) pick a different statistic and then use the join count that is added with that statistic calculation

add a new field with values of 1 for all, and then calculate the sum of that newly added field.

Setting up your join operation like this would still allow you to create the result as a Hosted Feature Layer View, enabling your join output to stay up to date as the input data changes. Check out this blog post and the Join Features documentation for more details.

- Amanda

AmandaRing

I think you may be able to use Arcade to accomplish what you are envisioning. The examples provided in this presentation on Using Arcade with Your Apps might help you get started.

ahmedshaibal

Hi, @wlavell. Were you able to accomplish what you wanted to do with the related table? I believe in software's like BI and others we can just show the relationship (1:1 or 1:M) between the two tables and the map can dynamically update based on whichever selection is made.

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.