Dashboard Data Expression with Related Tables

[MelissaW]

Hi,

I am trying write a data expression to create a featureset with fields from related tables to use in dashboard indicators & table. @jcarlson  I followed your post  here, but I'm getting an error: "Test execution error: Execution error - Cannot access value using a key of this type. Verify test data."  Can you provide any insight into where I went wrong?

Or, is there a better way to achieve this? I really need some fields from ALL the related tables to create indicators in my dashboard and I'm struggling big time on how to execute that.  

Background:

I'm accessing the Stands (2) layer and trying to pull data from the related Harvest Table (cut plan) (5). I really need fields from all the related tables, but I started with just one to see if I could figure it out. 

{
 "currentVersion": 10.81,
 "cimVersion": "2.6.0",
 "id": 2,
 "name": "Stands",
 "type": "Feature Layer",
 "parentLayer": null,
 "defaultVisibility": true,
 "minScale": 100000,
 "maxScale": 0,
 "canScaleSymbols": false,
 "geometryType": "esriGeometryPolygon",
 "description": "",
 "copyrightText": "",
 "editFieldsInfo": {
  "creationDateField": "created_date",
  "creatorField": "created_user",
  "editDateField": "last_edited_date",
  "editorField": "last_edited_user"
 },
 "ownershipBasedAccessControlForFeatures": null,
 "syncCanReturnChanges": true,
 "relationships": [
  {
   "id": 2,
   "name": "Silviculture",
   "relatedTableId": 6,
   "cardinality": "esriRelCardinalityOneToMany",
   "role": "esriRelRoleOrigin",
   "keyField": "GlobalID",
   "composite": true
  },
  {
   "id": 3,
   "name": "Seedlings",
   "relatedTableId": 7,
   "cardinality": "esriRelCardinalityOneToOne",
   "role": "esriRelRoleOrigin",
   "keyField": "GlobalID",
   "composite": true
  },
  {
   "id": 4,
   "name": "Cut Plan",
   "relatedTableId": 5,
   "cardinality": "esriRelCardinalityOneToOne",
   "role": "esriRelRoleOrigin",
   "keyField": "GlobalID",
   "composite": true
  },
  {
   "id": 5,
   "name": "Inventory",
   "relatedTableId": 4,
   "cardinality": "esriRelCardinalityOneToOne",
   "role": "esriRelRoleOrigin",
   "keyField": "GlobalID",
   "composite": true
  },
  {
   "id": 1,
   "name": "Company Tracts",
   "relatedTableId": 1,
   "cardinality": "esriRelCardinalityOneToMany",
   "role": "esriRelRoleDestination",
   "keyField": "TractGUID",
   "composite": true

// Set portal and layers
var p = 'https://gbp-frd.maps.arcgis.com'
var stfs = FeatureSetByPortalItem(Portal(p), 'e6ae2c4345a54bb29f789c2cde47bda2', 2);
Console(Count(stfs))

// Empty feature array and feat object to populate output
var features = [];
// Iterate over features in main layer
for (var f in stfs){
    // grab related records for feature
    var rel = FeatureSetByRelationshipName(f, "Cut Plan")
Console(Count(rel))

    //iterate over related records
    for (var r in rel){
      //create feature dict
      var feat = {
        'attributes': {
          'StandID_s' : f['StandID'],
          'GlobalID_s' : f['GlobalID'],
          'Age_s' : f['Age'],
          'Acres_s' : f['Acres'],
          'ConvAc_s' : f['Conv_Ac'],
          'District_s' : f['District'],
          'TractID_s' : f['TractID'],
          'Operability' : f['Operability'],
          'FirstThinPlanned': rel['T1P'],
          'FirstThinActual' : rel['T1A'],
          'SecondThinPlanned' : rel['T2P'],
          'SecondThinActual' : rel['T2A'],
          'ThirdThinPlanned' : rel['T3P'],
          'ThirdThinActual' : rel['T3A'],
          'PlannedHarvest' : rel['CCP'],
          'ActualHarvest' : rel['CCA'],
          'StandGUID_h' : rel['StandGUID'],
          'StandID_h' : rel['StandID']
        }
      };
      //push to array
      Push(features, feat);
    };
};
//create featureset dict, supply features array
var fs_dict = {
    'fields': [
      {'name': 'StandID', 'type': 'esriFieldTypeString'},
      {'name': 'GlobalID', 'type': 'esriFieldTypeString'},
      {'name': 'Age_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'Acres_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'Conv_AC_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'District_s', 'type': 'esriFieldTypeString'},
      {'name': 'TractID_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'Operability', 'type': 'esriFieldTypeString'},
      {'name': 'FirstThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'FirstThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'ThirdThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'ThirdThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'PlannedHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'ActualHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'StandGUID_h', 'type': 'esriFieldTypeString'},
      {'name': 'StandID_h', 'type': 'esriFieldTypeString'},
    ],
    'geometryType': '',
    'features': features
};

return FeatureSet(Text(fs_dict))

My Console shows:

Thank you!!

[jcarlson · Accepted solution]

In your loop for (var r in rel), you are referencing the FeatureSet when you attempt to return attributes:

rel['T1P']

Try referencing the feature, though, as that's what you want:

r['T1P']

[MelissaW]

Hi @jcarlson ,

Thank you so much for the reply! I made the correction you found, but now when I choose Run, it 'hangs up'; the blue circle in the output freezes.  So (after a pc restart :)), I went back and added the specific fields and 'false' to my "FeatureSetby..' variables on lines 3 & 11 and it still freezes on me when I click run. Any ideas?  Would it help to filter out the records that don't have a match in the relate? If so, what would that look like?

Thank you again for all your help! A lot of the questions I find on here have a solution from you and it's very helpful!

// Set portal and layers
var p = 'https://maps.arcgis.com'
var stfs = FeatureSetByPortalItem(Portal(p), 'e6ae2c4345a54bb29f789c2cde47bda2', 2, ['StandID','GlobalID','Age','Acres','Conv_Ac','District','TractID','Operability'],false);
Console(Count(stfs))

// Empty feature array and feat object to populate output
var features = [];

// Iterate over features in main layer
for (var f in stfs){
        // grab related records for feature
    var rel = FeatureSetByRelationshipName(f, "Cut Plan",['T1P','T1A','T2P','T2A','T3P','T3A','CCP','CCA','StandGUID','StandID'],false)
Console(Count(rel))

    //iterate over related records
    for (var r in rel){
      //create feature dict
      var feat = {
        'attributes': {
          'StandID_s' : f['StandID'],
          'GlobalID_s' : f['GlobalID'],
          'Age_s' : f['Age'],
          'Acres_s' : f['Acres'],
          'ConvAc_s' : f['Conv_Ac'],
          'District_s' : f['District'],
          'TractID_s' : f['TractID'],
          'Operability' : f['Operability'],
          'FirstThinPlanned': r['T1P'],
          'FirstThinActual' : r['T1A'],
          'SecondThinPlanned' : r['T2P'],
          'SecondThinActual' : r['T2A'],
          'ThirdThinPlanned' : r['T3P'],
          'ThirdThinActual' : r['T3A'],
          'PlannedHarvest' : r['CCP'],
          'ActualHarvest' : r['CCA'],
          'StandGUID_h' : r['StandGUID'],
          'StandID_h' : r['StandID']
        }
      };
      //push to array
      Push(features, feat);
    };
};
//create featureset dict, supply features array
var fs_dict = {
    'fields': [
      {'name': 'StandID', 'type': 'esriFieldTypeString'},
      {'name': 'GlobalID', 'type': 'esriFieldTypeString'},
      {'name': 'Age_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'Acres_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'Conv_AC_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'District_s', 'type': 'esriFieldTypeString'},
      {'name': 'TractID_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'Operability', 'type': 'esriFieldTypeString'},
      {'name': 'FirstThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'FirstThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'ThirdThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'ThirdThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'PlannedHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'ActualHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'StandGUID_h', 'type': 'esriFieldTypeString'},
      {'name': 'StandID_h', 'type': 'esriFieldTypeString'},
    ],
    'geometryType': '',
    'features': features
};
return FeatureSet(Text(fs_dict))

[jcarlson · Accepted solution]

Depending on the size of the layers, it might just take that long to execute the expression. No need to filter, though, using FeatureSetByRelationshipName grabs a pre-filtered set of related records already.

Try filtering the initial FeatureSet, just to test the expression on a smaller subset of the layer. Something like:

stfs = Filter(stfs, 'objectid < 100')

[MelissaW]

Awesome! Now off to pare down these attributes to the bare minimum:) Thanks so much!!

[MelissaW]

Hi @jcarlson ,

So sorry to bother you again on this! I attempted to add a join to the existing expression above with the related table.  I've read all the articles and forums I could google and I can't find anything similar so I tried to write something using the arcade sample here as a template.  I'm hoping it's possible, and I just messed it up.  🙂

I would like to join a new featureset using the 'Stand_No' attribute, which corresponds to 'StandID' in the relate.  I started the new part on line 42 and I'm currently getting this error:

Test execution error: Cannot read properties of null (reading 'toString'). Verify test data.

// Set portal and layers
var p = 'https://maps.arcgis.com'
var stfs = FeatureSetByPortalItem(Portal(p), 'e6ae2c4345a54bb29f789c2cde47bda2', 2, ['StandID','Age','Acres','Conv_Ac','District','TractID'],false);
var pfs = FeatureSetByPortalItem(Portal(p), 'a8af124e92af44579e3fd07bb59ebb4c', 0, ['Stand_No','Product_Type','Plot_Name','Plot_Status','Plot_Configuration','Height','DBH','Tally','TPA','Tons_pa','Cruiser_Name'],false)

//Limit to 50 records for Testing response time
stfs = Filter(stfs, 'objectid < 50')

// Empty feature array and feat object to populate output
var features = [];

// Iterate over features in main layer
for (var f in stfs){
        // grab related records for feature
    var rel = FeatureSetByRelationshipName(f, "Cut Plan",['T1P','T1A','T2P','T2A','CCP','CCA'],false)
        Console(Count(rel))
    
    //iterate over related records
    for (var r in rel){
      //create feature dict
      var feat = {
        'attributes': {
          'StandID' : f['StandID'],
          'Age_s' : f['Age'],
          'Acres_s' : f['Acres'],
          'ConvAc_s' : f['Conv_Ac'],
          'District_s' : f['District'],
          'TractID_s' : f['TractID'],
          'FirstThinPlanned': r['T1P'],
          'FirstThinActual' : r['T1A'],
          'SecondThinPlanned' : r['T2P'],
          'SecondThinActual' : r['T2A'],
          'PlannedHarvest' : r['CCP'],
          'ActualHarvest' : r['CCA'],
        }
      };
      //push to array
      Push(features, feat);
    };
};

    for (var v in pfs){
        var StandID = v['Stand_No']
        var filt = Filter(stfs, "Stand_No = @StandID")
        for (var s in filt){
            feat = {
                attributes: {
                  'StandID': StandID,
                  'Product_Type' : s['Product_Type'],
                  'Plot_Name' : s['Plot_Name'],
                  'Plot_Status' : s['Plot_Status'],
                  'Plot_Configuration' : s['Plot_Configuration'],
                  'Height' : s['Height'],
                  'DBH' : s['DBH'],
                  'Tally' : s['Tally'],
                  'TPA' : s['TPA'],
                  'Tons_pa' : s['Tons_pa'],
                }
            };
            Push(features, feat);
        }
    }  

//create featureset dict, supply features array
var fs_dict = {
    'fields': [
      {'name': 'StandID', 'type': 'esriFieldTypeString'},
      {'name': 'Age_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'Acres_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'Conv_AC_s', 'type': 'esriFieldTypeDouble'},
      {'name': 'District_s', 'type': 'esriFieldTypeString'},
      {'name': 'TractID_s', 'type': 'esriFieldTypeInteger'},
      {'name': 'FirstThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'FirstThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinPlanned', 'type': 'esriFieldTypeInteger'},
      {'name': 'SecondThinActual', 'type': 'esriFieldTypeInteger'},
      {'name': 'PlannedHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'ActualHarvest', 'type': 'esriFieldTypeInteger'},
      {'name': 'Product_Type', 'type' : 'esriFieldTypeString'},
      {'name': 'Plot_Name', 'type': 'esriFieldTypeString'},
      {'name': 'Plot_Status', 'type': 'esriFieldTypeString'},
      {'name': 'Plot_Configuration', 'type': 'esriFieldTypeString'},
      {'name': 'Height', 'type': 'esriFieldTypeInteger'},
      {'name': 'DBH', 'type': 'esriFieldTypeInteger'},
      {'name': 'Tally', 'type': 'esriFieldTypeInteger'},
      {'name': 'TPA', 'type': 'esriFieldTypeDouble'},
      {'name': 'Tons_pa', 'type': 'esriFieldTypeDouble'},
     ], 
    'geometryType': '',
    'features': features
};
return FeatureSet(Text(fs_dict))

thank you!!!!

[jcarlson]

It's hard to know from "outside", but I think it's got to do with the StandID. Is it actually a string? If so, your filter statement needs to enclose it in single-quotes. Also, for referencing variables in a SQL expression, I much prefer using template literals (or "backtick strings", I've heard them called. It removes the need to create a variable in the first place, since any expression can be piped in this way. Try something like this:

Filter(stfs, `Stand_No = '${v['Stand_No']}'`)