Arcade Data Expression- Dictionary from multiple tables

[TSmith]

Hello,

I am interested in feeding a dashboard with values from multiple tables. I have the following code below: 

var portal = Portal('yourportalurlhere');
var valvefs = FeatureSetByPortalItem(portal,'id',index,["*"],false);
for (var v in valvefs){
   var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["*"],false),'insdate DES');
   var recentrecord = First(relatedrecords)
   for (var r in recentrecord){
       var feat = {
           attributes: {
               assetid: r["valveid"],
               insdate: r["insdate"],
               diameter: v["diameter"],
               turns: r["turns"],
               fintorque: r["fintorque"],
           }
       }
   } Push(features,feat);
};
var joinedDict = {
  fields: [
    { name: "assetid", type: "esriFieldTypeString" },
    { name: "insdate", type: "esriFieldTypeDate" },	
    { name: "fintorque", type: "esriFieldTypeDouble" },
    { name: "turns", type: "esriFieldTypeDouble" },
    { name: "diameter", type: "esriFieldTypeDouble" },
  ],
geometryType: '',
features: features
};

I'm wanting to grab two values from the parent table (diameter and assetID) and other values from the most recent inspection record. I'm not sure why the code isn't working, but the error I keep getting is 

Execution Error:Runtime Error: Cannot call member property on object of this type.

Any help is appreciated! I got something similar working, however all of the values come from the same table. 

[jcarlson]

Your expression looks good, for the most part. However, since you use First to return the first feature from your relatedrecords, there's no reason to include a for loop over recentrecord, as it is already a singular feature.

Iterating over a feature goes through its fields. So when you say "for var r in recentrecord", you're telling the expression to loop through each field in the feature, and that's why you're getting the error, since the individual attribute key/value pairs don't have the attributes you're trying to pull out. To demonstrate:

var fs = $layer

var f_1 = First(fs)

for (var f in f_1){
    console(`${f} : ${f_1[f]}`)
}

Returns this in the messages:

BASEELEV : 141.36
BLDGHEIGHT : 10.74
BUILDINGID : 0712218018
EAVEHEIGHT : 10.74
FEATURECODE : 561
FLOORCOUNT : 0
LASTEDITOR : ESRI
LASTUPDATE : 2010-01-18T18:00:00-06:00
OBJECTID : 1
ROOFDIR : 0
ROOFFORM : Flat
SHAPE__Area : 55.71875
SHAPE__Length : 30.689584083596625

Just drop the "for r in recentrecord" loop and define the feat object in the larger "for v in valvefs" loop.

[TSmith]

That solved the error, but now my output is empty. 

I believe the dictionary needs an identifier to map the fields, and with the for loop gone, how do I map the fields only present in the related record?

for (var v in valvefs){
   var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["*"],false),'insdate DES');
   var recentrecord = First(relatedrecords)
       var feat = {
           attributes: {
               assetid: ["valveid"],
               insdate: ["insdate"],
               diameter: v["diameter"],
               turns: ["turns"],
               fintorque: ["fintorque"],
           }
       }
       Push(features,feat);
   };
var joinedDict = {
  fields: [
    { name: "assetid", type: "esriFieldTypeString" },
    { name: "insdate", type: "esriFieldTypeDate" },	
    { name: "fintorque", type: "esriFieldTypeDouble" },
    { name: "turns", type: "esriFieldTypeDouble" },
    { name: "diameter", type: "esriFieldTypeDouble" },
  ],
geometryType: '',
features: features
};
return FeatureSet(Text(joinedDict))

[jcarlson]

Ah, okay. So, we still need to tell the expression where it's getting those attributes. recentrecord is the feature, so try something like this in defining your feat object. I don't actually know which fields are coming from which feature, so I made a guess.

var feat =
    attributes: {
        assetid: recentrecord["valveid"],
        insdate: recentrecord["insdate"],
        diameter: v["diameter"],
        turns: recentrecord["turns"],
        fintorque: recentrecord["fintorque"],
    }
}

EDIT: I just re-read your original expression. Just replace those "r"s with "recentrecord" and it should work.

[TSmith]

ah, simpler than I thought. It doesn't like null values though, and I'm not sure which field specifically is null. (Cannot call member method on null) maybe some additional logic to change nulls to "unknown"?

[jcarlson]

Null fields are fine to work with, it's the fact that you're probably trying to pull attributes from a null. You end up with a null when your related records featureset is empty.

Throw it inside of a big if that checks the Count of the relatedrecords featureset, and only proceeds to add a feature if records exist.

if (Count(relatedrecords)>0){
    // do your recentrecord and feat stuff
}

If you want to add something to the output for features with no related records, you can add an else statement and manually define the feat object with strings like 'No inspections on record', rather than pull related attributes.

[TSmith]

Making more progress, but when I test the code it now outputs empty rows. 

for (var v in valvefs){
   var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["*"],false),'insdate DES');
   if (Count(relatedrecords)>0){
        var recentrecord = First(relatedrecords)
        var feat = {
           attributes: {
               valveid: recentrecord["valveid"],
               insdate: recentrecord["insdate"],
               diameter: v["diameter"],
               turns: recentrecord["turns"],
               fintorque: recentrecord["fintorque"],
           }
       }
       Push(features,feat);
   };
};   
var joinedDict = {
  fields: [
    { name: "assetid", type: "esriFieldTypeString" },
    { name: "insdate", type: "esriFieldTypeDate" },	
    { name: "fintorque", type: "esriFieldTypeDouble" },
    { name: "turns", type: "esriFieldTypeDouble" },
    { name: "diameter", type: "esriFieldTypeDouble" },
  ],
geometryType: '',
features: features
};
return FeatureSet(Text(joinedDict))

[jcarlson]

Can you share a screenshot of the output? Unless your data has empty rows to begin with, that shouldn't be happening.

Also, your joinedDict object defines a field "assetid", but the feat object has a "valveid" attribute now, so it's not going to pick that attribute up.

[TSmith]

It takes a long time to run so it's doing something. There are only 900 valves, but over 10,000 inspection records. I did change the field names so they all match. I wonder if it has something to do with the order of my loops? 

[jcarlson]

You could try manually specifying the output fields in the relatedrecords featureset. Setting ['*'] returns all the attributes, which adds to the processing time, especially if there are many fields. Try

["insdate", "turns", "fintorque"]

 Couldn't "valveid" be pulled from the parent feature, too?