Joining two Feature Sets together and getting a count with an Arcade Data Expression

[Anonymous User]

The Scenario:

This scenario uses Arcade to join two datasets with a data expression and return a count of joined features:

1. A hosted feature service of "Zones"
2. A hosted feature service of survey data that shares a common ID with the Zones in a 1-M relationship.

Important note: This workflow was designed for use cases with a small number of zones (<15). Growing beyond this number may result in slow loading times for your widgets.

The Datasets:

Zone Polygons

ZoneID	Zone Name
0001	North
0002	West
0003	South

Survey Data

ZoneID	Submitter Name
0001	Mark
0001	Amy
0002	Bob
0002	Jared
0002	Jhonatan

The Expression:

//Bring in both feature sets for the join
var zoneFS = FeatureSetByPortalItem(Portal('https://arcgis.com'),'726b8daa3a424eee94726d08487bcd86',0,['ZoneID'], true)
var surveyDataFS = FeatureSetByPortalItem(Portal('https://arcgis.com'),'544bd653c91d4920bfe184b572758af1',0,['ZoneID','SubmissionName'], false)

var joinedDict = {
  fields: [
    { name: "ZoneID", alias: "ZoneID", type: "esriFieldTypeString"},
    {name: "Registrants", type: "esriFieldTypeInteger"}
  ],
'geometryType': 'esriGeometryPolygon',
'features':[]};

var i = 0;

//for each zone in the Zone layer, count the number of records with the same ZoneID in the survey submissions
for (var t in zoneFS) {
    var tableID = t["ZoneID"]
    var tableCount = Count(Filter(surveyDataFS,"ZoneID = @tableID"))
    joinedDict.features[i] = {
        attributes: {
            ZoneID: tableID,
            Registrants: tableCount
        }
    }
i++
}

// Return dictionary cast as a feature set 
return FeatureSet(Text(joinedDict));

The Result:

ZoneID	Registrants	FID
0001	2	0
0002	3	1
0003	0	2

Modified from: https://github.com/Esri/arcade-expressions/blob/master/dashboard_data/JoinLayerFieldsToTable

[PeterMilenkovic]

Excellent code.

[NorthSouthGIS]

I was inspired by your code. I am trying to take advantage of new data expressions in Dashboards to essentially denormalize a table of work history related back to a tree feature. I am having trouble with my code though - the performance is extremely slow. Any suggestions? 

// Get the related work orders
var includedFields = ['ServiceDate','StartDate','Status','completion_date','Price','Type','Client','TreeID'];
var includedTreeFields = ["Client","Tree","TreeID","BotanicalName","BotanicalID","CommonName","StreetNumber","StreetAddress","OnAddress","Parkway","Fictitious","OUL","Clearance","DBH","Maintenance","VisualCondition","VisualConditionNotes","RemovalPriority","HardscapeDamage","HardscapeDamageExtent","Date_Collected"]
var treeFS = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '12345',0,includedTreeFields,false)
var workFS = FeatureSetByPortalItem(Portal('https://www.arcgis.com'), '67890',2,includedFields,false)

var workFSFields = Schema(workFS).fields;
var treeFSFields = Schema(treeFS).fields;

var fieldsCombined = [];
for (var y in workFSFields){
  Push(fieldsCombined,workFSFields[y])
}
for (var x in treeFSFields){
  Push(fieldsCombined,treeFSFields[x])
}


var i = 0

var joinedDict = {
  fields: fieldsCombined,
  'geometryType': '',
  'features':[]
};

var combinedAttributes = {};
for (var workFeature in workFS) {

  var relatedTreeFeature = First(Filter(treeFS, "GlobalID = '" + workFeature.TreeID + "'"));

  var workAttributes = Dictionary(Text(workFeature)).attributes;

  var treeAttributes = Dictionary(Text(relatedTreeFeature)).attributes;

  for (var field in workAttributes) {
    combinedAttributes[field] = workAttributes[field];
  }

  for (var field in treeAttributes) {
      combinedAttributes[field] = treeAttributes[field];
  }

  joinedDict.features[i]={
    attributes: combinedAttributes
  }
  i++
}

return FeatureSet(Text(joinedDict));