Data Expression to join two layers results in "Invalid data type for expression"

[delozar_EMWD]

Hello,

I'm attempting to create a data expression that joins a hosted feature layer to a layer within a map image layer based on a shared ID, to be used in a dashboard indicator element. Enterprise 11.1.

A little background: We're creating a workflow where field users open Field Maps, choose a point from the map image layer, launch Survey123 from an attribute expression in the pop-up, relevant attributes are brought over from the point and then the field users capture information on the asset's location. A survey needs to be performed for every point. Management wants to be able to see the progress of how many surveys have been completed. 

I've been fiddling around with the arcade expression provided here and kept having timeout issues until I changed the layers to only bring in one or two fields instead of "*" aka, all of them. Now, I get "Invalid data type for expression" which makes me think I'm on the right path but still missing something.  The field it's refencing is coming from the hosted feature layer (survey results).

Maybe I'm overcomplicating how this can be done? Any thoughts are welcome 🙂

//portal connection
var prtl = Portal("URL HERE");

//survey results point layer
var surveys = FeatureSetByPortalItem(
    prtl,
    "ITEM ID HERE",
    0,
    ['globalid','objectid','service_order_number', 'backflow_required'],
    false
);

//water service point layer
var services = FeatureSetByPortalItem(
    prtl,
    "ITEM ID HERE",
    0,
    ['globalid','objectid','SERVICE_ORDER_NUMBER'],
    false
);

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

// Populate Feature Array
for (var t in services) {
    var tableID = t["SERVICE_ORDER_NUMBER"]
    for (var p in Filter(surveys, "service_order_number = "+tableID)){
        feat = {
            attributes: {
                SERVICE_ORDER_NUMBER: tableID,
                Backflow_Required: p["backflow_required"],
            }
        }

    Push(features, feat)
    }
}

var joinedDict = {
    fields: [
        { name: "SERVICE_ORDER_NUMBER", type: "esriFieldTypeString" },
        { name: "Backflow_Required", type: "esriFieldTypeString" },
    ],
    'geometryType': '',
    'features':features
};

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

[KenBuja]

When posting code, use the "Insert/Edit code sample" button instead of attaching it as a text file. It's much easier to read and make reference to.

It looks like the "service_order_number" field isn't a numeric field, but rather a text field, so the Filter expression would need quotes around the variable. Try using this line instead

for (var p in Filter(surveys, "service_order_number = @tableID")) {

Using variable substitution (see the Filter function documentation) will automatically take care of whether quote are required around the variable.

[delozar_EMWD]

When posting code, use the "Insert/Edit code sample" button instead of attaching it as a text file. It's much easier to read and make reference to.

Thanks for the suggestion. It's my first post so I wasn't aware of that option 🙂 I've made that update now.

---

It looks like the "service_order_number" field isn't a numeric field, but rather a text field, so the Filter expression would need quotes around the variable. Try using this line instead

for (var p in Filter(surveys, "service_order_number = @tableID")) {

Using variable substitution (see the Filter function documentation) will automatically take care of whether quote are required around the variable.

---

That's correct, they're both text fields I'm trying to join by. I made the suggested change and now I get a new error: "Cannot read properties of null (reading 'toString')" Which must be because some of the points have a null service order number? Taking a look at that next. 

[KenBuja · Accepted solution]

You can check whether the service order number is empty before the filtering the FeatureSet

for (var t in services) {
  var tableID = t["SERVICE_ORDER_NUMBER"];
  if (!IsEmpty(tableID)) {
    for (var p in Filter(surveys, "service_order_number = @tableID")) {
      feat = {
        attributes:
          {
            SERVICE_ORDER_NUMBER: tableID,
            Backflow_Required: p["backflow_required"]
          }
      };

      Push(features, feat);
    }
  }
}