Symbolizing one to many relationship in ArcGIS online

[wlavell]

Hi

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?

[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

B) 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

[wlavell]

Hi

So I realized perhaps this was not explained clearly enough.

In my dashboard we have two data sources.   

1 data source for spatial information
1 data source for reporting information

relationship between spatial to reporting is:  1 province record can have many reporting records in the reporting table.

The reporting table has data reported by 3 criteria
date
agency
province

and # of persons reported under 3 demensions

• Activity1
• Activity2
• Activity3 

i.e
Province          Agency            Date             Activity       # of Persons
Province A    Agency ABC     Sept 2020   Activity 1.    200 

Province A    Agency 123     Sept 2020   Activity 1.    100 

Province A    Agency A12     Oct 2020   Activity 2.    50 

Province B    Agency 123      Oct 2020     Activity 1     100 

Province B    Agency ABC      Dec 2020     Activity 2     100 

Province B    Agency DDD     Sept 2020     Activity 1     100 

Province C    Agency ABC    Sept 2020    Activity 2     200 

Province C    Agency 123    Oct 2020       Activity 1     200 

Province C    Agency DDD   Sept 2020    Activity 2     200 


Map visualization: 
- will show # of persons per province for the year
-  When a user selects a filter from the selector dropdown the # of persons will update in the map view based on the criteria they have selected.  

Selectors
Agency,  Date, Activity

- The map layer view needs to dynamically update the number of persons based on 3 criteria. 

This is why the 1 to many relationship is needed. 

I have not seen the ability for the map layer to dynamically update the attribute #s being visualized based on a user input. 

[wlavell]

Can the #s for the join statistics be updated based on user input filters?  Say date selectors?  So show me # of persons for this polygon in march? or June or March?   Not as separate layers though.

[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.