Arcade Exp - Joining two Layers for Dashboard Table

[AllisonWebb]

I'm able to create an Arcade expression to pull attributes from two feature layers to create a dashboard table element.  However, I want all of the records from one layer to populate, and then add the attributes from the other layer when there is a matching record from that layer.  Attached is my Arcade Expression - How do I pull all of the records from the "plots" layer?

[KenBuja]

When posting code, use the Insert/edit code sample tool instead of attaching it as a text file.

Give this script a try. It puts null values for the plot attributes in each feature for the grades layer and only fills them in when there is a matching plots feature.

A couple of additional notes about this.

This uses the Arcade variable substitution in the Filter function in line 38, which means you don't have to worry about whether a value has quotes or not.

Aslo, calling the Filter function many times in a loop is very expensive and will slow your script down. Using @jcarlson's Memorize function will drastically reduce the execution time for the script. In my test script using a plots layer of 36 features and a grades feature of 51 features, this reduced the execution time from about 8 seconds to half a second.

function Memorize(fs) {
  var temp_dict = {
    fields: Schema(fs)["fields"],
    geometryType: "",
    features: []
  };
  for (var f in fs) {
    var attrs = {};
    for (var attr in f) {
      attrs[attr] = Iif(TypeOf(f[attr]) == "Date", Number(f[attr]), f[attr]);
    }
    Push(temp_dict["features"], { attributes: attrs });
  }
  return FeatureSet(temp_dict);
}

var portal = Portal("https://xyz.maps.arcgis.com/");
var plots = Memorize(FeatureSetByPortalItem(portal, "xyzx", 0, ["*"], false));
var grades = FeatureSetByPortalItem(portal, "xyzx", 0, ["*"], false);

// Create empty features array
var features = [];

// Populate Feature Array
for (var t in grades) {
  var tableID = t["BlockCode"];
  var feat = {
    attributes:
      {
        BlockCode: tableID,
        FinalGrade: t["FinalGrade"],
        SelectedEURs: t["SelectedEURs"],
        last_name: t["last_name"],
        UCode: null,
        Vineyard_Name: null
      }
  };
  for (var p in Filter(plots, "UCode = @tableID")) {
    feat.attributes.UCode = p["UCode"];
    feat.attributes.Vineyard_Name = p["Vineyard_Name"];
  }
  Push(features, feat);
}

var joinedDict = {
  fields: [
    { name: "BlockCode", type: "esriFieldTypeString" },
    { name: "FinalGrade", type: "esriFieldTypeString" },
    { name: "SelectedEURs", type: "esriFieldTypeString" },
    { name: "last_name", type: "esriFieldTypeString" },
    { name: "UCode", type: "esriFieldTypeString" },
    { name: "Vineyard_Name", type: "esriFieldTypeString" }
  ],
  geometryType: "",
  features: features
};

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

[AllisonWebb]

Thank you!  This is only pulling the records from the grades layer with the correct attributes from the plots layer.  I want all the records from the plots layer.  The idea is to be able to filter the table in the dashboard to see the records in the plots layer that don't have a record in the grades feature layer to determine which need to be graded.  But also see the scores for those in the plots layer that have a grades.

grades layer BlockCode matches plots layer UCode

plots fields "UCode", "Vineyard_Name"

grades layer "BlockCode", "FinalGrade", "SelectedEURs", "last_name"

I cannot use a joined view because the plots feature layer is overwritten frequently, and I cannot overwrite when there is a joined view.

[KenBuja · Accepted solution]

It sounds like you just need to reverse the logic

for (var p in plots) {
  var tableID = p["UCode"];
  var feat = {
    attributes:
      {
        UCode: tableID,
        Vineyard_Name: p["Vineyard_Name"],
        BlockCode: null,
        FinalGrade: null,
        SelectedEURs: null,
        last_name: null
      }
  };
  for (var g in Filter(grades, "BlockCode = @tableID")) {
    feat.attributes.BlockCode = g["BlockCode"];
    feat.attributes.FinalGrade = g["FinalGrade"];
    feat.attributes.SelectedEURs = g["SelectedEURs"];
    feat.attributes.last_name = g["last_name"];
  }
  Push(features, feat);
}

var joinedDict = {
  fields: [
    { name: "UCode", type: "esriFieldTypeString" },
    { name: "Vineyard_Name", type: "esriFieldTypeString" },
    { name: "BlockCode", type: "esriFieldTypeString" },
    { name: "FinalGrade", type: "esriFieldTypeString" },
    { name: "SelectedEURs", type: "esriFieldTypeString" },
    { name: "last_name", type: "esriFieldTypeString" }
  ],
  geometryType: "",
  features: features
};

[AllisonWebb · Accepted solution]

When I use your first expression, it takes a few seconds to save after clicking Done.  Then I can add the fields to the table.  When I use your second expression, it won't save after clicking Done.  If I Cancel and go back to Select a layer, the expression is there with an "unable to execute Arcade script".  I appreciate any help you can provide.  

There are 1668 records in plots and 3 records in grades, so not too many.  

function Memorize(fs) {
  var temp_dict = {
    fields: Schema(fs)["fields"],
    geometryType: "",
    features: []
  };
  for (var f in fs) {
    var attrs = {};
    for (var attr in f) {
      attrs[attr] = Iif(TypeOf(f[attr]) == "Date", Number(f[attr]), f[attr]);
    }
    Push(temp_dict["features"], { attributes: attrs });
  }
  return FeatureSet(temp_dict);
}

var portal = Portal("https://xyz.maps.arcgis.com/");
var plots = Memorize(FeatureSetByPortalItem(portal, "xyz", 0, ["*"], false));
var grades = FeatureSetByPortalItem(portal, "xyz", 0, ["*"], false);

// Create empty features array
var features = [];

// Populate Feature Array
for (var p in plots) {
  var tableID = p["UCode"];
  var feat = {
    attributes:
      {
        UCode: tableID,
        Vineyard_Name: p["Vineyard_Name"],
        BlockCode: null,
        FinalGrade: null,
        SelectedEURs: null,
        last_name: null
      }
  };
  for (var g in Filter(grades, "BlockCode = @tableID")) {
    feat.attributes.BlockCode = g["BlockCode"];
    feat.attributes.FinalGrade = g["FinalGrade"];
    feat.attributes.SelectedEURs = g["SelectedEURs"];
    feat.attributes.last_name = g["last_name"];
  }
  Push(features, feat);
}

var joinedDict = {
  fields: [
    { name: "UCode", type: "esriFieldTypeString" },
    { name: "Vineyard_Name", type: "esriFieldTypeString" },
    { name: "BlockCode", type: "esriFieldTypeString" },
    { name: "FinalGrade", type: "esriFieldTypeString" },
    { name: "SelectedEURs", type: "esriFieldTypeString" },
    { name: "last_name", type: "esriFieldTypeString" }
  ],
  geometryType: "",
  features: features
};

[KenBuja · Accepted solution]

I didn't include all the lines of the code in my reply, only the ones that were changed. You didn't return the dictionary as a FeatureSet at the end of the code.

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

One other thing. I forgot to switch the FeatureSet to be Memorized

var plots = FeatureSetByPortalItem(portal, "xyz", 0, ["*"], false);
var grades = Memorize(FeatureSetByPortalItem(portal, "xyz", 0, ["*"], false));

[AllisonWebb]

That worked!  Thank you so much!!

[KenBuja]

Glad to help. Don't forget to click the "Accept as Solution" button on post(s) that answered your question.