GroupBy() Function Trouble

[amrodri01]

I have a data set "sourceTable" with RoadNumbers associated for each entry. Each entry has an Individual_Removal_Score. I want to combine all entries based on RoadNumber and SUM the Individual_Removal_Score values to create one record for each RoadNumber with a Total_Score. I then want to filter "stats" to lookup RoadNumbers that match the ID field of the target layer and return the Total_Score. This is being done as an attribute rule in pro for a field in the target dataset.

//Reference Layer and relevant fields
var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score"],false);
//key field for target layer
var ID = $feature.ID;
//statistic variable target layer
var Miles = $feature.Total_Miles;
//Group reference layer by RoadNumber and SUM Individual_Removal_Scores for each entry
var stats = groupby(sourceTable, 'RoadNumber',
{name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'})
//Filter Summary table(stats) by RoadNumber where value is equal to the key field "ID"
var Lookup = First(Filter(stats, "RoadNumber = @ID"));
//return Total_Score/Miles if key field has a match
return iif(Lookup == null, null, ((Lookup.Total_Score)/Miles));

Script verifies but won't save because of an error on line 13 "Attribute value lookup error". I have used this exact script without the GroupBy() function just fine so I'm at a loss. I'm assuming the GroupBy() table isn't being returned properly so there is nothing to reference? Wondering if the field "Total_Miles" needs to be created physically somewhere or if it gets created in memory while its running. The way I understand it is the GroupBy() function creates a new temp FeatureSet. I thought I was creating a FeatureSet with RoadNumber and Total_Miles (being a sum of "Individual_Removal_Score") as the fields but my thinking is that it's breaking down because there are multiple entries with the same roadnumber? (Screen shot example of the table I'm attempting to group below)

Thanks for the input!

[KenBuja]

If you have a field in your table that contains the mileage for each road, you would add that to the GroupBy function. Or you could use the "SHAPE__Length" field. However, since that won't be in miles, you would have to convert that in the Expression. Here, I'm making the conversion from meters to miles

var stats = groupby(sourceTable, 'RoadNumber',
  [
    {name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'},
    {name: 'Total_Miles', expression: 'SHAPE__Length * 0.000621371', statistic: 'SUM'}
  ]
);

Then in the final line, you would use that summarized length

return iif(Lookup == null, null, ((Lookup.Total_Score)/Lookup.Total_Miles));

[amrodri01]

I do have a field in the sourceTable that has the mileage of the road based on the roadnumber. I guess I'm not sure how it would display properly because the sourceTable is a point layer and the target layer is a line layer. Both layers have their own fields with the total mileage of the road (not shape length). If I were to GroupBy() the ML1_Road_Miles wouldn't that just sum the mileage and not have it a single value? Thats why I had it referencing the line FeatureSet "Total_Miles" as a variable. Maybe an average instead which would give it the correct value?

Below is a snip of the line FeatureSet. This script is writing to the "Storage_Score field

Below is a snip of the point FeatureSet "sourceTable" w/ the Road Mileage

Even still the filter function displays an error on line 7. Below is how I have it now, but it doesn't seem right.

var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score", "ML1_Road_Miles"],false);
var ID = $feature.ID;
var stats = groupby(sourceTable, 'RoadNumber', 
[{name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'},
{name: 'Total_Miles', expression: 'ML1_Road_Miles', statistic: 'SUM'}])

var Lookup = First(Filter(stats, "RoadNumber = @ID"));

return iif(Lookup == null, null, ((Lookup.Total_Score)/Lookup.Total_Miles));

[KenBuja]

Showing that other table makes it a little more understandable. In that case, leave off that second GroupBy expression and use your original version.

What is the error you're getting on line 7?

[amrodri01]

Gotcha, okay with the original script it was referencing line 11. Just kind of weird since it only shows in the attribute rules pane and not in the expression entry window. 

[amrodri01 · Accepted solution]

Figured out the solution, needed to filter the multiple RoadNumber records into a table with just a single matching ID (Line 8 ) then the rest of the script functioned.

//Reference Layer and relevant fields
var sourceTable = FeatureSetByName($datastore, "TNF_RoadConditionAssessment", ["RoadNumber", "Individual_Removal_Score"],false);
//key field for target layer
var ID = $feature.ID;
//statistic variable target layer
var Miles = $feature.Total_Miles;
//Filter all features that equal road ID
var Lookupall = Filter(sourceTable, "RoadNumber = @ID")
//Group reference layer by RoadNumber and SUM Individual_Removal_Scores for each entry
var stats = groupby(Lookupall,'RoadNumber',
{name: 'Total_Score', expression: 'Individual_Removal_Score', statistic: 'SUM'})
//Filter Summary table(stats) by RoadNumber where value is equal to the key field "ID"
var Lookup = first(Filter(stats, "RoadNumber = @ID"))
//return Total_Score/Miles if key field has a match
return iif(Lookup == null, 0, (Lookup.Total_Score/Miles));