Dashboard Data Expression with Related Tables

[M_M]

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']

[M_M]

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

[M_M]

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

[M_M]

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']}'`)

[SveinungBertnesRåheim]

I have a simple relation ship with not to many registrations. I have set up a layer view that summerize the related record. The data is about free parkings spaced in 7 different streets at three specific times.
Te joined view-layer summarizes and give me average number of free spaces per street. I would like to show the relative amount of free spaces. We have the total amount of car spaces in the origin dataset. In the joined summarized dataset it shoud be fairly easy to divide mean free spaces to total car spaces. 
I have tried for hours to put this up. Keep getting an error like this:
Test execution error: Execution error - Key not found - Gatenavn. Verify test data.

If I write more or less the exact same code, but set a standard feature set as source, I do not get an error. So I suspect that it is not possible to build a data expression on a joined view-layer.

Or at least you have to do something extra to get it work. Maybe I could group the relate-table in the dataexpression and join it to the original table? How to I write that join? The join fields are GlobalID and Gater_GlobalID

[SveinungBertnesRåheim]

Here is my code. I have a dataset with a one feature class containg 7 streets and a related table containing registration of free carpark-spaces in the 7 streets. I would like to make a serial chart that automatically shows the ratio of free parking in the different streets. Then I need to join the street table with grouped result for the free parking table, then finally calculate the ratio. 

Here is my arcade script:

var portal1 = Portal('https://www.arcgis.com/')

//Create a feature set for each point, line, and polygon layer and include the cost feild

var A_fs = FeatureSetByPortalItem(

  portal1, '5ce61deae7354ce4b91255b46a450883', 0, ['Gatenavn', 'TotaltAntallParkeringsplasser', 'GlobalID'], false)

var Table_fs = FeatureSetByPortalItem(

    portal1,

    '5ce61deae7354ce4b91255b46a450883',

    1,

    [

        'Gater_GlobalID',

        'AntallParkeringsplasserLedig',

    ],

    false

);

var group_gate = GroupBy(Table_fs,

                    ['Gater_GlobalID'],

                    [{

                        name:'AVG_GATER',

                        expression:'AntallParkeringsplasserLedig',

                        statistic:'AVG'}]

);                    

//Create an empty array and variable for the new FeatureSet that you will push/combine your features into

var features = [];

var feat;

for (var f in group_gate) {

    var tableID = f['Gater_GlobalID']

    for (var t in Filter(A_fs, 'GlobalID =' +tableID)){

        feat = {

            'attributes': {

                'Gater_GlobalID': f['Gater_GlobalID'],

                'Gatenavn': t['Gatenavn'],

                'TotaltAntallParkeringsplasser': t['TotaltAntallParkeringsplasser'],

                'AVG_GATER': f['AVG_GATER'],

                'AndelLedigParkering': Round(f['AVG_GATER']/t['TotaltAntallParkeringsplasser']*100,2),

        }

    };

    Push(features, feat)

}

Console('Ferdig med join')

//Create final FeatureSet from combined features

var joinedDict = {

    'fields': [

        { 'name': 'Gater_GlobalID', 'type': 'esriFieldTypeGUID' },

        { 'name': 'Gatenavn', 'type': 'esriFieldTypeString' },

        { 'name': 'AVG_GATER', 'type': 'esriFieldTypeDouble' },

        { 'name': 'TotaltAntallParkeringsplasser', 'type': 'esriFieldTypeDouble' },

        { 'name': 'AndelLedigParkering', 'type': 'esriFieldTypeDouble' },

    ],

    'geometryType': '',

    'features': features,

};

//Return dictionary as feature set, altså som tabell

return FeatureSet(Text(joinedDict))}

I get this error when trying to run it:
Test execution error: Expected ".", [0-9], or [eE] but "d" found.. Verify test data.

[SveinungBertnesRåheim]

No one answers, but google helps....
This thread more or less solved the problems for me:

https://gis.stackexchange.com/questions/461814/arcgis-online-dashboard-join-layers-through-arcade-sc...

I defined the variable "i" and then "i++" to populate. My script, which now works is like this. I am not sure, but I think the error has something to do with the interpretaion of the join of globalID's. 

var portal1 = Portal('https://www.arcgis.com/')

//Create a feature set for each point, line, and polygon layer and include the cost feild

var line_fs = FeatureSetByPortalItem(

  portal1, '5ce61deae7354ce4b91255b46a450883', 0, ['Gatenavn', 'TotaltAntallParkeringsplasser', 'GlobalID'], false)

var Table_fs = FeatureSetByPortalItem(

    portal1,

    '5ce61deae7354ce4b91255b46a450883',

    1,

    [

        'Gater_GlobalID',

        'AntallParkeringsplasserLedig',

    ],

    false

);

var grouptablefs = GroupBy(Table_fs,

                    ['Gater_GlobalID'],

                    [{

                        name:'AVG_GATER',

                        expression:'AntallParkeringsplasserLedig',

                        statistic:'AVG'}]

);                    

//Create an empty array and variable for the new FeatureSet that you will push/combine your features into

var i = 0;

var joinedDict = {

        fields: [

            { name: "GlobalId", type: "esriFieldTypeString" },

            { name: "Gatenavn", type: "esriFieldTypeString" },

            { name: "TotaltAntallParkeringsplasser", type: "esriFieldTypeDouble" },

            { name: "AVG_GATER", type: "esriFieldTypeDouble" },

            { name: "AndelLedigParkering", type: "esriFieldTypeDouble" },

        ],

        'geometryType': '',

        'features':[]

    };

// Populate Feature Array

for (var t in grouptablefs) {

    var tableID = t["Gater_GlobalID"];

var f = First(Filter(line_fs, "GlobalID = '" + tableID + "'"));

var feat = {

        attributes: {

            FeatureID: tableID,

            'Gater_GlobalID': t['Gater_GlobalID'],

            'Gatenavn': f['Gatenavn'],

            'TotaltAntallParkeringsplasser': f['TotaltAntallParkeringsplasser'],

            'AVG_GATER': t['AVG_GATER'],

            'AndelLedigParkering': Round(t['AVG_GATER']/f['TotaltAntallParkeringsplasser']*100,2),

        }

}

    joinedDict.features[i] = feat;

    i++

}

// Return dictionary cast as a feature set

return FeatureSet(Text(joinedDict));