execution error - invalid parameter - JoinLayerFieldsToTable after successful GroupBy

1233
2
Jump to solution
01-12-2024 05:12 PM
ChrisGiamarino
New Contributor III

Hi,

I have a feature class with 1425 rows and a table with only 10-15 jurisdictions. I am trying to GroupBy the feature class and then JoinLayerFieldsToTable. I've successfully grouped the 1425 rows, but when I try to join them using the esri arcade GitHub documentation, I get an execution error.  My team and I want to create a bar chart in Dashboards with 5 numerical bars by jurisdiction. 

Here is the code sample: 

 

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz
// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz 
var portal = Portal("https://marincounty.maps.arcgis.com/");
var polyFs = FeatureSetByPortalItem(portal,"1112f6fa268c48948093050fe0fc0c44",0,["*"],true);
var tablefs = FeatureSetByPortalItem(portal,"1093d97dbe43474b96d09b701d9672a1",["*"], 0,false)

// create grouped table variable
var polyGroup = GroupBy(polyFs,
['Jurisdiction'],
[{name:'SumPotentialCompTons', expression: "PotentialCompTons", statistic: 'SUM'},
{name:'SumPotentialMulchTons', expression: "PotentialMulchTons", statistic: 'SUM'},
{name:'SumPotentialROWPTons', expression: "TotPotentialROWPTons", statistic: 'SUM'}]);

var features = [];
var feat;

// Populate Feature Array
for (var t in tablefs) {
    var tableID = t["Jurisdiction"]
    for (var p in Filter(polyGroup, "Jurisdiction = '${tableID}'")){
        feat = {
            attributes: {
                FeatureID: tableID,
                Name: p["Jurisdiction"],
                SumPotentialCompTons: p["SumPotentialCompTons"],
                SumPotentialMulchTons: p["SumPotentialMulchTons"],
                SumPotentialROWPTons: p["SumPotentialROWPTons"],
                Population: t["Population"],
                ProcurementTarget: t["ProcurementTarget"],
				
            }
        }

    Push(features, feat)
    }
}
Console(p)
var joinedDict = {
    fields: [
        { name: "FeatureID", type: "esriFieldTypeString" },
        { name: "Name", type: "esriFieldTypeString" },	
        { name: "SumPotentialCompTons", type: "esriFieldTypeDouble" },
        { name: "SumPotentialMulchTons", type: "esriFieldTypeDouble" },
        { name: "SumPotentialROWPTons", type: "esriFieldTypeDouble" },
        {name: "Population", type: "esriFieldTypeInteger"},
        { name: "ProcurementTarget", type: "esriFieldTypeDouble" },
    ],
    'geometryType': '',
    'features':features
};

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


 

Could anyone help me troubleshoot and resolve the issue? I'd really appreciate it. We have each jurisdiction populating the feature class with field maps, so we don't want to hard code the population and procurement target values. I am unfamiliar with Console() or seeing logs as to what is happening. 

0 Kudos
1 Solution

Accepted Solutions
ChrisGiamarino
New Contributor III

nevermind, after some troubleshooting and referencing this solution response post from @jcarlson, I got my code to work. Here is the solution: 

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz


var portal = Portal("https://marincounty.maps.arcgis.com/");
var polyFs = FeatureSetByPortalItem(portal,"1112f6fa268c48948093050fe0fc0c44",0,["*"],true);

var tablefs = FeatureSetByPortalItem(portal, "1093d97dbe43474b96d09b701d9672a1", 1,["Jurisdiction","Population","ProcurementTarget","uniqueID"],false);
// return(tablefs)


// create grouped table variable
var polyGroup = GroupBy(polyFs,
['Jurisdiction'],
[{name:'SumPotentialCompTons', expression: "PotentialCompTons", statistic: 'SUM'},
{name:'SumPotentialMulchTons', expression: "PotentialMulchTons", statistic: 'SUM'},
{name:'SumPotentialROWPTons', expression: "TotPotentialROWPTons", statistic: 'SUM'}]);



// create empty features
var features = [];
var feat;

// populate feature array
for (var t in tablefs){
  var tableID = t["Jurisdiction"]
  for (var p in Filter(polyGroup, "Jurisdiction = @tableID")){
    feat = {
      attributes: {
        FeatureID: tableID,
        Name: p["Jurisdiction"],
        PotCompTons: p["SumPotentialCompTons"],
        PotMulchTons: p["SumPotentialMulchTons"],
        PotROWPTons: p["SumPotentialROWPTons"],
        Population: t["Population"],
        ProcurementTarget: t["ProcurementTarget"],
        TargetRemaining: Round((t["ProcurementTarget"] - p["SumPotentialROWPTons"]),2),

      }
    }
  Push(features,feat)
  }
}

var joinedDict = {
  fields: [
    { name: "FeatureID", type: "esriFieldTypeInteger" },
    { name: "Name", type: "esriFieldTypeString" },
    { name: "PotCompTons", type: "esriFieldTypeDouble" },
    { name: "PotMulchTons", type: "esriFieldTypeDouble" },
    { name: "PotROWPTons", type: "esriFieldTypeDouble" },
    { name: "Population", type: "esriFieldTypeInteger" },
    { name: "ProcurementTarget", type: "esriFieldTypeDouble" },
    { name: "TargetRemaining", type: "esriFieldTypeDouble" },
  ],
  'geometryType': '',
  'features':features
};

// return dict as feature set
return FeatureSet(Text(joinedDict));

View solution in original post

0 Kudos
2 Replies
ChrisGiamarino
New Contributor III

Hi, I am still having issues with this expression. I'd like to join a table to a GroupBy table (polygon feature layer) using JoinLayerFieldsToTable. I am able to import the Feature Layer and Table using FeatureSetByPortalItem. I am also able to GroupBy the 'Jurisdiction' column for my feature layer. I am just unsure where I am going wrong with the populate feature array, joining dictionary, and successfully returning the dictionary as a FeatureSet. Please see source code below. Again, I appreciate any helpful insights or solutions. Thanks. 

 

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz


var portal = Portal("https://marincounty.maps.arcgis.com/");
var polyFs = FeatureSetByPortalItem(portal,"1112f6fa268c48948093050fe0fc0c44",0,["*"],true);

var tablefs = FeatureSetByPortalItem(portal, "1093d97dbe43474b96d09b701d9672a1", 1,["Jurisdiction","Population","ProcurementTarget","uniqueID"],false);

// create grouped table variable
var polyGroup = GroupBy(polyFs,
['Jurisdiction'],
[{name:'SumPotentialCompTons', expression: "PotentialCompTons", statistic: 'SUM'},
{name:'SumPotentialMulchTons', expression: "PotentialMulchTons", statistic: 'SUM'},
{name:'SumPotentialROWPTons', expression: "TotPotentialROWPTons", statistic: 'SUM'}]);

// create empty features
var features = [];
var feat;

// populate feature array
for (var t in tablefs){
  var tableID = t["uniqueID"]
  for (var p in Filter(polyGroup, "Jurisdiction = "+tableID)){
    feat = {
      attributes: {
        FeatureID: tableID,
        Name: p["Jurisdiction"],
        PotCompTons: p["SumPotentialCompTons"],
        PotMulchTons: p["SumPotentialMulchTons"],
        PotROWPTons: p["SumPotentialROWPTons"],
        Population: t["Population"],
        ProcurementTarget: t["ProcurementTarget"],

      }
    }
  Push(features,feat)
  }
}

var joinedDict = {
  fields: [
    { name: "FeatureID", type: "esriFieldTypeInteger" },
    { name: "Name", type: "esriFieldTypeString" },
    { name: "PotCompTons", type: "esriFieldTypeDouble" },
    { name: "PotMulchTons", type: "esriFieldTypeDouble" },
    { name: "PotROWPTons", type: "esriFieldTypeDouble" },
    { name: "Population", type: "esriFieldTypeInteger" },
    { name: "ProcurementTarget", type: "esriFieldTypeDouble" },
  ],
  'geometryType': '',
  'features':features
};

// return dict as feature set
return FeatureSet(joinedDict);

 

0 Kudos
ChrisGiamarino
New Contributor III

nevermind, after some troubleshooting and referencing this solution response post from @jcarlson, I got my code to work. Here is the solution: 

// Write an expression that returns a FeatureSet.
// Documentation: https://arcg.is/3c419TD
// Samples: https://arcg.is/38SEWWz


var portal = Portal("https://marincounty.maps.arcgis.com/");
var polyFs = FeatureSetByPortalItem(portal,"1112f6fa268c48948093050fe0fc0c44",0,["*"],true);

var tablefs = FeatureSetByPortalItem(portal, "1093d97dbe43474b96d09b701d9672a1", 1,["Jurisdiction","Population","ProcurementTarget","uniqueID"],false);
// return(tablefs)


// create grouped table variable
var polyGroup = GroupBy(polyFs,
['Jurisdiction'],
[{name:'SumPotentialCompTons', expression: "PotentialCompTons", statistic: 'SUM'},
{name:'SumPotentialMulchTons', expression: "PotentialMulchTons", statistic: 'SUM'},
{name:'SumPotentialROWPTons', expression: "TotPotentialROWPTons", statistic: 'SUM'}]);



// create empty features
var features = [];
var feat;

// populate feature array
for (var t in tablefs){
  var tableID = t["Jurisdiction"]
  for (var p in Filter(polyGroup, "Jurisdiction = @tableID")){
    feat = {
      attributes: {
        FeatureID: tableID,
        Name: p["Jurisdiction"],
        PotCompTons: p["SumPotentialCompTons"],
        PotMulchTons: p["SumPotentialMulchTons"],
        PotROWPTons: p["SumPotentialROWPTons"],
        Population: t["Population"],
        ProcurementTarget: t["ProcurementTarget"],
        TargetRemaining: Round((t["ProcurementTarget"] - p["SumPotentialROWPTons"]),2),

      }
    }
  Push(features,feat)
  }
}

var joinedDict = {
  fields: [
    { name: "FeatureID", type: "esriFieldTypeInteger" },
    { name: "Name", type: "esriFieldTypeString" },
    { name: "PotCompTons", type: "esriFieldTypeDouble" },
    { name: "PotMulchTons", type: "esriFieldTypeDouble" },
    { name: "PotROWPTons", type: "esriFieldTypeDouble" },
    { name: "Population", type: "esriFieldTypeInteger" },
    { name: "ProcurementTarget", type: "esriFieldTypeDouble" },
    { name: "TargetRemaining", type: "esriFieldTypeDouble" },
  ],
  'geometryType': '',
  'features':features
};

// return dict as feature set
return FeatureSet(Text(joinedDict));
0 Kudos