Select to view content in your preferred language

Arcade: Sum of feature values in a related table

1056
7
05-02-2023 06:08 PM
Labels (1)
kawakawa4
New Contributor III

Hi, 

I have a point layer in a web  map that I would like to symbolise using the sum of a field in a related table (1 to many join). So I have opened an expression in the 'Choose attributes' section of the Styles tab.  There are some empty values in the field so I have tried to correct that in the script.

However, I am getting an error saying: "Test execution error: Execution error - Key not found - Possums. Verify test data.". I have checked the 'Possums' field and it is an integer field, and my spelling is correct. 

Here is the script: 

 

var relatedRecords = FeatureSetByRelationshipName($feature, '18', ['Possums'], true);
var value = $feature['Possums'];
if (IsEmpty(value)){
  return 0
}
var sum = Sum(relatedRecords, 'Possums');
return sum;

 

 

Something that I'm not sure about is the name of the relationship class. I sourced it from here: 

kawakawa4_0-1683073612239.png

is that correct, and why is my script not working please?  Many thanks

Tags (2)
0 Kudos
7 Replies
ChristopherCounsell
MVP Regular Contributor

I don't believe FeatureSets are supported for symbology. Please refer to this idea (and vote for it):

https://community.esri.com/t5/arcgis-online-ideas/allow-featureset-functions-to-be-accessed-in/idi-p...

 

ChristopherCounsell
MVP Regular Contributor

Also - just keep in mind performance. For your map to be symbolized this way, it would have to do a lot of work... better to keep FeatureSets in the pop-up I believe

jcarlson
MVP Esteemed Contributor

One way around this is to create an attribute-based join between the two layers, which can be done using the web map's analysis tools. When a join is based on an attribute and both layers are owned by you, the resulting layer can be a "live" join, meaning it will stay up to date as the source layer is edited.

This would give you a way of symbolizing features based on related data, while still maintaining a link to the original datasets.

- Josh Carlson
Kendall County GIS
kawakawa4
New Contributor III

Thanks @jcarlson , I have been using join views for other layers, but I can't find a way to symbolise by the sum of values in the join table using that method. Using 'aggregate' in the web map, I can get a count of aggregated features or a sum of values as a label, but not in the symbology.

An example of where I need this: I have points symbolised as pie charts and I want the pie chart to show the sum of all values from the join table related to that that point. Currently with a join view, I get several pie charts on top of each other. Or similarly, I would like the size of a point symbol to change according to the sum of all values from the join table related to that that point. Currently with a join view, I get several separate points on top of each other. 

Hopefully there is a way to do that with symbology options that I am missing!

0 Kudos
ChristopherCounsell
MVP Regular Contributor

There isn't a way within the symbology options as FeatureSets need to be supported.

You can do workaround solutions like @jcarlson suggested. Another one is to update a 'count' field in the parent table and symbolize that directly. You could potentially do that as part of your data collection workflow. Or you could schedule a python script to run regularly and update it (check related table for edits, if there is an edit, query and update the count for the parent record).

SarahBisley
New Contributor

Hello

I know it's been awhile but I'm wondering how you solved this. I have exactly the same situation, with Possums on Banks Peninsula 😀

Sarah -Pest Free Banks Peninsula. 

 

0 Kudos
kawakawa4
New Contributor III

Kia ora Sarah, I ended up making a join view from my trap and trap records layers and symbolising by the sum of the Strikes field to give a figure for Total kills and then symbolised using that. This worked for map symbols, but not charts.  

0 Kudos