Arcade Data Expression- Dictionary from multiple tables

1067
17
Jump to solution
12-23-2021 08:04 AM
TSmith
by
New Contributor III

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. 

0 Kudos
1 Solution

Accepted Solutions
TSmith
by
New Contributor III
var features = [];
var feat;
for (var v in valvefs){
              var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["insdate", "turns", "fintorque"],false),'insdate DESC');
            
			
			
			  if (Count(relatedrecords)>0){
              var recentrecord = First(relatedrecords);      
              var feat = {
              attributes: {
              'assetid': v["assetid"],
              'insdate': number(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))

 

casting the date as a number solved the issue. My only further question now is this: 

if I wanted to reduce processing time by filtering the related table down to the top 2000 records, or say records only in the past 5 years, would that go before the for loop, or within? I have also considered just creating a hosted view of the data to point this expression to.

 

Thanks so much for your input!

View solution in original post

0 Kudos
17 Replies
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

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

 

0 Kudos
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

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"?

 

 

0 Kudos
jcarlson
MVP Honored Contributor

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.

jcarlson_0-1640279338764.png

jcarlson_1-1640279348312.png

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

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

 

0 Kudos
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

TSmith_0-1640281960509.png

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? 

0 Kudos
jcarlson
MVP Honored Contributor

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?

- Josh Carlson
Kendall County GIS