Arcade Data Expression- Dictionary from multiple tables

1226
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
17 Replies
TSmith
by
New Contributor III

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.

0 Kudos
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
0 Kudos
TSmith
by
New Contributor III

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.

0 Kudos
ahassa22
New Contributor

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

0 Kudos
TSmith
by
New Contributor III

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. 

0 Kudos
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!

0 Kudos
jcarlson
MVP Honored Contributor

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.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

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.

0 Kudos