topic Re: Arcade Data Expression- Related Table in ArcGIS Dashboards Questions

Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 13:55:43 GMT

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?

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 15:09:30 GMT

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.

Re: Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 15:23:00 GMT

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!

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 15:32:23 GMT

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.

Re: Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 15:52:37 GMT

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

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 16:32:29 GMT

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.

Re: Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 16:45:20 GMT

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

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 16:53:41 GMT

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.

Re: Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 16:58:43 GMT

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

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 17:01:27 GMT

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

Re: Arcade Data Expression- Related Table

TSmith — Tue, 21 Dec 2021 18:29:11 GMT

Realized there was a missing comma in the code, it works now. My only question now is how to highlight these features on the map?

Thanks so much!

Re: Arcade Data Expression- Related Table

jcarlson — Tue, 21 Dec 2021 18:44:52 GMT

Glad to hear it! Adding the spatial component is not too difficult.

In the FeatureSetByPortalItem function, set the final parameter to "true".

In the section when we define the feat object, we need to include that geometry:

var feat = {
    attributes: {
        assetid: f['assetid'],
        flushingdate: latest
    },
    geometry: Geometry(f)
}

When we establish the outdict object, put "geometryType: 'esriGeometryPoint'", or whatever geometry type we're dealing with here.

I always default to leaving geometry out of data expressions, as it can make the expression more resource-intensive to evaluate. But once you make those changes, you should be able to use the appropriate Action to zoom to / filter data in the map.

Re: Arcade Data Expression- Related Table

TSmith — Wed, 22 Dec 2021 15:15:44 GMT

You have been a tremendous amount of help. Is there any way I could grab data from fields in the related table?

When I try this:

f (latest < year_ago) { var feat = { attributes: { 'valveID': f['assetid'], 'vcycle' : f['vcycle'], 'last_inspection': latest, 'fintorque': rel_fs['fintorque'], 'turns': rel_fs['turns']

it doesn't like trying to add those fields. Even when I set a variable

var turns = rel_fs['turns']; var fintorque = rel_fs['fintorque'];

do I have to call a function like var turns = Text(rel_fs['turns']); or something like that?

Re: Arcade Data Expression- Related Table

KimberlyK — Mon, 26 Aug 2024 18:48:53 GMT

Hi Josh @jcarlson - I use quite a few of your examples that you post in the Community to help us out with data expressions. Thank you so much for generously addressing questions and offering your expertise.

I am currently working on a dashboard that includes the need to use related tables. I'm particularly stumped by the syntax needed in a data expression to use grouped values from a related table that are related to the feature layer by a projectID, and still be able to zoom to the location on the map that corresponds to those projectIDs in the feature layer. The related table is many:1 so the attributes in the table have to be grouped to be useable in a category selector widget or a table widget. When I read your reply, I thought maybe the bit of alteration you mention might be the key: "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."

I updated my expression to include the spatial component, but I am still unable to use the select or zoom action on the feature layer in the map. If you have any additional suggestions, I would appreciate your help. Thanks.

Re: Arcade Data Expression- Related Table

jcarlson — Mon, 26 Aug 2024 19:48:15 GMT

It would probably be best to post this as its own question, and include the expression you've got so far.