Arcade Data Expression- Related Table

[TSmith]

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? 

[jcarlson · Accepted solution]

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.

[TSmith]

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!

[jcarlson]

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.

[TSmith]

// 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? 

[jcarlson]

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.

[TSmith]

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

[jcarlson]

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.

[TSmith]

// 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. 

[jcarlson]

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