topic Re: Arcade Exp - Joining two Layers for Dashboard Table in ArcGIS Dashboards Questions

Arcade Exp - Joining two Layers for Dashboard Table

AllisonWebb — Thu, 27 Mar 2025 00:44:27 GMT

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?

Re: Arcade Exp - Joining two Layers for Dashboard Table

KenBuja — Thu, 27 Mar 2025 15:05:17 GMT

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.

Re: Arcade Exp - Joining two Layers for Dashboard Table

AllisonWebb — Thu, 27 Mar 2025 18:42:17 GMT

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.

Re: Arcade Exp - Joining two Layers for Dashboard Table

KenBuja — Thu, 27 Mar 2025 19:47:53 GMT

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 };

Re: Arcade Exp - Joining two Layers for Dashboard Table

AllisonWebb — Thu, 27 Mar 2025 21:35:00 GMT

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.

Re: Arcade Exp - Joining two Layers for Dashboard Table

KenBuja — Fri, 28 Mar 2025 14:40:01 GMT

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));

Re: Arcade Exp - Joining two Layers for Dashboard Table

AllisonWebb — Fri, 28 Mar 2025 15:25:08 GMT

That worked! Thank you so much!!

Re: Arcade Exp - Joining two Layers for Dashboard Table

KenBuja — Fri, 28 Mar 2025 15:34:27 GMT

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