ArcGIS Online Dashboard join layers through Arcade scripting

Question

I'm trying to build a script to create a joined layer based on a feature layer and a table my client gave me with only data entry permissions. The structure of the two layers is something as following (just an extraction of the key fields):

Geometric feature layer:

Table layer:

As you can see, between the two layers there are two matching field and both are string fields. The Object ID for the first layer is set by design, while the second Object ID is generated after the table gets populated with the data from a Survey123 form.

I have found a script on github that allows to join two layers (a layer and a table) through a matching field and I've tested as it is on AGOL and it is working.

var portal = Portal("https://www.arcgis.com/");
var polyfs = FeatureSetByPortalItem(
    portal,
    "4dbbad3d6f694e0ebc7c3b4132ea34df",
    0,
    ["*"],
    false
);

var tablefs = FeatureSetByPortalItem(
    portal,
    "4dbbad3d6f694e0ebc7c3b4132ea34df",
    6,
    ["*"],
    false
);

// Create empty features array and feat object
var features = [];
var feat;

// Populate Feature Array
for (var t in tablefs) {
    var tableID = t["FeatureID"]
    for (var p in Filter(polyfs, "HydroID = "+tableID)){
        feat = {
            attributes: {
                FeatureID: tableID,
                Name: p["DPS_Region"],
                ModelID: t["ModelID"],
                AddressCount: t["AddressCount"],
                MAX_TSTime: t["MAX_TSTime"],
            }
        }

    Push(features, feat)
    }
}

var joinedDict = {
    fields: [
        { name: "FeatureID", type: "esriFieldTypeString" },
        { name: "Name", type: "esriFieldTypeString" },  
        { name: "ModelID", type: "esriFieldTypeInteger" },
        { name: "AddressCount", type: "esriFieldTypeInteger" },
        { name: "MAX_TSTime", type: "esriFieldTypeString" },
    ],
    'geometryType': '',
    'features':features
};

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

I presume the functioning of this script is that the matching field is FeatureID in the table layer and HydroID in the feature layer. By doing so you can then set variables calling fields from both the source layers.

I have then started to adapt everything to the layers and specifics I have, but when I modified the matching references from var tableID = t["FeatureID"] to var tableID = t["parentGUID"] (the foreign key in the table layer) I got this error:

Test execution error: Expected ".", [0-9], or [eE] but "c" found.. Verify test data.

At first I had no clue, but with some trials I'm suspecting this method wants an integer field type as matching field, but in my case I have a string field type and the ObjectID field is referring to different things.

Is there another way to perform this join using string field type as matching field?

Answer 1

A few suggestions. The filter should be using the t["ParentGUID"] value to filter against the GlobalID field in the polyfs. Additionally, Filter features a featureset not a feature, but since you're only getting potentially 1 feature back, you can use First() to get the feature and populate your attribute values. Looping through all features is unneccesary because there can only be one GlobalID value in a table.

var tableID = t["ParentGUID"];
var p = First(Filter(polyfs, "GlobalID = '" + tableID + "'"));
var feat = {
        attributes: {
            FeatureID: tableID,
            Name: p["DPS_Region"],
            ModelID: t["ModelID"],
            AddressCount: t["AddressCount"],
            MAX_TSTime: t["MAX_TSTime"],
        }
}

Push(features, feat)

Finally, I've had issues with creating a list of features without actually create a Feature() object. You may consider:

var portal = Portal("https://www.arcgis.com/");
var polyfs = FeatureSetByPortalItem(
    portal,
    "4dbbad3d6f694e0ebc7c3b4132ea34df",
    0,
    ["*"],
    false
);

var tablefs = FeatureSetByPortalItem(
    portal,
    "4dbbad3d6f694e0ebc7c3b4132ea34df",
    6,
    ["*"],
    false
);

// Create empty features array and feat object
var i = 0;
var feat;
var joinedDict = {
        fields: [
            { name: "FeatureID", type: "esriFieldTypeString" },
            { name: "Name", type: "esriFieldTypeString" },  
            { name: "ModelID", type: "esriFieldTypeInteger" },
            { name: "AddressCount", type: "esriFieldTypeInteger" },
            { name: "MAX_TSTime", type: "esriFieldTypeString" },
        ], 
        'geometryType': '',
        'features':[]
    };
// Populate Feature Array
for (var t in tablefs) {
    var tableID = t["ParentGUID"];
    var p = First(Filter(polyfs, "GlobalID = '" + tableID + "'"));
    var feat = Feature(Null,
            FeatureID, tableID,
            Name, p["DPS_Region"],
            ModelID, t["ModelID"],
            AddressCount, t["AddressCount"],
            MAX_TSTime, t["MAX_TSTime"]
        );
    joinedDict.features[i] = feat;
    i++
}

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

Answer 2

The following script is the actual SOLUTION that worked for my case. The answer from @evv_gis has been the key to develop this. Now I'm able to call for attributes from both layers through a join that works on the ParentGUID/GlobalID matching field.

    var portal = Portal("https://www.arcgis.com/");
    var polyfs = FeatureSetByPortalItem(
        portal,
        "4dbbad3d6f694e0ebc7c3b4132ea34df",
        0,
        ["*"],
        false
    );
    
    var tablefs = FeatureSetByPortalItem(
        portal,
        "4dbbad3d6f694e0ebc7c3b4132ea34df",
        6,
        ["*"],
        false
    );
    

// Create empty features array and feat object
var i = 0;
var joinedDict = {
        fields: [
            { name: "FeatureID", type: "esriFieldTypeString" },
        ], 
        'geometryType': '',
        'features':[]
    };
    
// Populate Feature Array
for (var t in tablefs) {
    var tableID = t["FeatureID"];
var p = First(Filter(polyfs, "HydroID = '" + tableID + "'"));
var feat = {
        attributes: {
            FeatureID: tableID,
        }
}
    joinedDict.features[i] = feat;
    i++
}


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