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. 

[TSmith]

I appreciate all the help here, I am still getting a result with an empty table. 

var features = [];
var feat;
for (var v in valvefs){
   var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["insdate", "turns", "fintorque"],false),'insdate DES');
   var recentrecord = First(relatedrecords);
   if (Count(relatedrecords)>0){
         
         var feat = {
           attributes: {
               'valveid': v["assetid"],
               'insdate': recentrecord["insdate"],
               'diameter': v["diameter"],
               'turns': recentrecord["turns"],
               'fintorque': recentrecord["fintorque"],
           }
       }
       Push(features,feat);
   };
};   
var joinedDict = {
  fields: [
    { name: "valveid", 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))

would there be a better way to accomplish this? I'm really just interested in grabbing turns, torque, and diameter from the most recent inspection.

[jcarlson]

I mean, if you're working in a dashboard, could you just have a list of valves that filters a details widget for the most recent related record upon selection? Or do you want the inspections part of the valves so the list can be sorted based on inspection data?

I still can't quite understand how you're getting an empty result. Any chance you could export an anonymized / obfuscated subset of the data so that I can test it against live data? I'm assuming the layer isn't public.

[TSmith]

there might be a better way to do it, but my goal is to filter inspection data to show on a dashboard which valves exceed thresholds (i.e. # of turns greater than a specified amount, or torque greater than a specified amount) the dashboard filtering capabilities are perfect for this, but I need the data in a format the dashboard can use. 

Unfortunately, working with related tables is proving to be quite frustrating. Would like to just highlight valves due for inspection (this part I have working) but then also flag ones based on past inspections (most recent) which I can't seem to load a dictionary for so far.

[ahassa22]

Are you getting a data access error or unable to execute arcade script?

[TSmith]

No, no syntax errors or anything like that. It executes and returns an empty featureset with the field names (really odd) 

Is there perhaps a simpler way to accomplish this? I'm realizing Arcade can do a lot, but I'm new to using it in this capacity. 

[TSmith · Accepted solution]

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!

[jcarlson]

Glad it's working!

Since it's the related records, you'd do a filter like that within the loop, after pulling the related records. Something like

var time_endpoint = DateAdd(Now(), -5, 'years')
var filt_records = Filter(relatedrecords, 'insdate >= @time_endpoint')

Then proceed as before, but reference filt_records in place of the original relatedrecords.

[TSmith]

I thought that would work, but I get an execution error, probably having something to do with trying to do math on a date field. Maybe creating a new field in memory that's a number? I also know that there's ways to call a sql variable in the filter function, but I'm not sure what the syntax would be. 

Something like: 

var sql = 'insdate>= DATEADD(year, -5, GETDATE()'

it may also be easier to just filter on number of rows.

[abul123]

Thanks for sharing solution for this issue, we got it too. Converting the date field to number fix it! 

[DavidNyenhuis1]

With this week's update to Arcade and ArcGIS Dashboards, date fields in feature set constructors now just work. You no longer have to wrap dates with Number() if you pass the dictionary into the FeatureSet() function (which as of this release accepts a dictionary as opposed to only text based JSON).

Instead of:

return FeatureSet(Text(dict))

Do this:

return FeatureSet(dict)

Learn more in this blog post. 

NOTE: For Enterprise users, this update is targeted for 11.2