Arcade Data Expression- Related Table

1130
12
Jump to solution
12-21-2021 05:55 AM
TSmith
by
New Contributor III

Hello,

 

I am relatively new to arcade, and attempting to figure out how to use a data expression to feed indicators/gauges based on an inspection date field in a related table. 

I currently have a pop-up within the map that uses this expression:

 

 

 var BlowoffHistory= FeatureSetByRelationshipName($feature,"Blowoff_Flushing_Inspection")var LatestDate = Max(BlowoffHistory,"flushingdate") var FormatDate = Date(LatestDate) return FormatDate 

 

my goal is to reference related data and check to see if the most recent inspection date occurred within the last year. If it did not, the feature should be passed through to an indicator widget, displaying the assets that are overdue for inspection. Any pointers/tips on how to accomplish this? 

 

 

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Honored Contributor

So, the difference between the popup and the dashboard widgets is that the dashboard can potentially reference all features at once, whereas the popup is specific to a single feature.

There are a few ways you can do this, but it depends on what you want the output to look like. Do you want a list of just those features which are overdue? That might look like this:

// Set portal and layers

var portal = Portal('your portal URL');
var fs = FeatureSetByPortalItem(
    portal,
    'itemID of service',
    layer index, // Probably 0, but maybe different if you have multiple layers/tables in the service
    ['list', 'of', 'fields'],
    false
);

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

// Set "one year ago" date
var year_ago = DateAdd(Now(), -1, 'years');

// Iterate over features in main layer
for (var f in fs){
    
    // Get related records
    var rel_fs = FeatureSetByRelationshipName(
        $feature,
        "Blowoff_Flushing_Inspection"
        ['flushingdate'],
        false
    );

    // Get most recent date from related records
    var latest = Max(rel_fs, 'flushingdate');

    // If latest date not within a year, add feature to output array
    if (latest < year_ago) {
        var feat = {
            attributes: {
                'some_id': f['id_field'],
                'last_inspection: latest
            }
        }
        
        Push(features, feat);
    };
    
// Create output dictionary
var outdict = {
    fields: [
        { name: 'some_id', type: 'esriFieldTypeString'},
        { name: 'last_inspection', type: 'esriFieldTypeDate'}
    ],
    geometryType: '',
    features: features
};

return FeatureSet(Text(outdict))

It's hard to say for certain if this is what you want, lacking more information about your dashboard and not having a live layer to test this against, but this should at least get you a list of overdue inspection features.

If you wanted to add a spatial component to this list, say, have the list items zoom to a location on a map, the expression would need to be altered, but not much.

- Josh Carlson
Kendall County GIS

View solution in original post

12 Replies
jcarlson
MVP Honored Contributor

So, the difference between the popup and the dashboard widgets is that the dashboard can potentially reference all features at once, whereas the popup is specific to a single feature.

There are a few ways you can do this, but it depends on what you want the output to look like. Do you want a list of just those features which are overdue? That might look like this:

// Set portal and layers

var portal = Portal('your portal URL');
var fs = FeatureSetByPortalItem(
    portal,
    'itemID of service',
    layer index, // Probably 0, but maybe different if you have multiple layers/tables in the service
    ['list', 'of', 'fields'],
    false
);

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

// Set "one year ago" date
var year_ago = DateAdd(Now(), -1, 'years');

// Iterate over features in main layer
for (var f in fs){
    
    // Get related records
    var rel_fs = FeatureSetByRelationshipName(
        $feature,
        "Blowoff_Flushing_Inspection"
        ['flushingdate'],
        false
    );

    // Get most recent date from related records
    var latest = Max(rel_fs, 'flushingdate');

    // If latest date not within a year, add feature to output array
    if (latest < year_ago) {
        var feat = {
            attributes: {
                'some_id': f['id_field'],
                'last_inspection: latest
            }
        }
        
        Push(features, feat);
    };
    
// Create output dictionary
var outdict = {
    fields: [
        { name: 'some_id', type: 'esriFieldTypeString'},
        { name: 'last_inspection', type: 'esriFieldTypeDate'}
    ],
    geometryType: '',
    features: features
};

return FeatureSet(Text(outdict))

It's hard to say for certain if this is what you want, lacking more information about your dashboard and not having a live layer to test this against, but this should at least get you a list of overdue inspection features.

If you wanted to add a spatial component to this list, say, have the list items zoom to a location on a map, the expression would need to be altered, but not much.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

Yes, this is very helpful. I was trying to wrap my head around how the $feature argument works, as trying to use the FeatureSetByRelationshipName function kept giving me an error of "invalid identifier $feature" 

It would be cool to zoom to the features on the map! I believe that can be set as an "action" within the list element itself. Thanks so much for your reply!

0 Kudos
jcarlson
MVP Honored Contributor

Ah, my mistake. Since that's happening in the for loop, you need to put "f", instead of "$feature".

You can set that zoom action on the list, but only if the list is looking at a spatial layer. We're working with a Data Expression, which synthesizes layers on the fly; the expression I posted, even though it pulls its data from a spatial layer, does not have a spatial component included in the output.

- Josh Carlson
Kendall County GIS
TSmith
by
New Contributor III

 

// Set "one year ago" date
var year_ago = DateAdd(Now(), -1, 'years');

// Iterate over features in main layer
for (var f in fs){
    
    // Get related records
    var rel_fs = FeatureSetByRelationshipName(
        f,
        "Blowoff_Flushing_Inspection"
        ['flushingdate'],
        false
    );

    // Get most recent date from related records
    var latest = Max(rel_fs, 'flushingdate');

    // If latest date not within a year, add feature to output array
    if (latest < year_ago) {
        var feat = {
            attributes: {
                'some_id': f['assetid'],
                'last_inspection': latest
            }
        }
        
        Push(features, feat);
    };
};
// Create output dictionary
var outdict = {
    fields: [
        { name: 'some_id', type: 'esriFieldTypeString'},
        { name: 'last_inspection', type: 'esriFieldTypeDate'}
    ],
    geometryType: '',
    features: features
    
};
return FeatureSet(Text(outdict))

 

 

I get a "Execution Error:Runtime Error: Cannot call member property on object of this type" when I test this expression. Any ideas? 

0 Kudos
jcarlson
MVP Honored Contributor

Looks like Max returns a number, so we've got to change that. Instead, we'll sort the related records by date, then return the first record in the set. Replace the "var latest" line with these:

var recent_record = First(OrderBy(rel_fs, 'last_inspection DESC'))

var latest = recent_record['flushingdate']

Any chance these layers are public? It's much easier to troubleshoot if I can test this against real data.

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

Unfortunately, no. I still run into the same error here, and unfortunately arcade isn't highlighting which line has the issue. Just a general runtime error. 

 

The layer is Flushing and Blowoff, the relationship is AssetID to BlowoffID in the related table, and it is worth noting the parent table does not have the most recent inspection date populated. Flushingdate only exists in the related table

0 Kudos
jcarlson
MVP Honored Contributor

Yes, I assumed the attribute was only on the related records.

That error usually means we're trying to pull an attribute from something that won't allow it.

Try commenting out the line "'some_id': f['assetid'],". That's the only place we're attempting to grab an attribute.

- Josh Carlson
Kendall County GIS
0 Kudos
TSmith
by
New Contributor III
// Empty feature array and feat object to populate output
var features = [];
var feat;

// Set "one year ago" date
var year_ago = DateAdd(Now(), -1, 'years');

// Iterate over features in main layer
for (var f in fs){
    
    // Get related records
    var rel_fs = FeatureSetByRelationshipName(
        f,
        "Blowoff_Flushing_Inspection"
        ['flushingdate'],
        false
    );

    // Get most recent date from related records
    var recent_record = First(OrderBy(rel_fs, 'flushingdate DESC'))

    var latest = recent_record['flushingdate']

    // If latest date not within a year, add feature to output array
    if (latest < year_ago) {
        var feat = {
            attributes: {
                //'assetid': f['assetid'],
                'flushingdate': latest
            }
        }
        
        Push(features, feat);
    }};
    
// Create output dictionary
var outdict = {
    fields: [
        { name: 'assetid', type: 'esriFieldTypeString'},
        { name: 'flushingdate', type: 'esriFieldTypeDate'}
    ],
    geometryType: '',
    features: features
};

return FeatureSet(Text(outdict))

 

Still a runtime cannot call member property error. This code and your workflow is definitely what I'm trying to do. I guess if I can't figure this out, I could always just write a python script to populate most recent inspection dates in the parent table. 

0 Kudos
jcarlson
MVP Honored Contributor

Very strange. You might look through some of the other Data Expressions for more ideas: https://github.com/Esri/arcade-expressions/tree/master/dashboard_data

- Josh Carlson
Kendall County GIS