topic Re: Arcade Data Expression- Dictionary from multiple tables in ArcGIS Dashboards Questions

Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 16:04:14 GMT

Hello,

I am interested in feeding a dashboard with values from multiple tables. I have the following code below:

I'm wanting to grab two values from the parent table (diameter and assetID) and other values from the most recent inspection record. I'm not sure why the code isn't working, but the error I keep getting is

Execution Error:Runtime Error: Cannot call member property on object of this type.

Any help is appreciated! I got something similar working, however all of the values come from the same table.

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 16:28:16 GMT

Your expression looks good, for the most part. However, since you use First to return the first feature from your relatedrecords, there's no reason to include a for loop over recentrecord, as it is already a singular feature.

Iterating over a feature goes through its fields. So when you say "for var r in recentrecord", you're telling the expression to loop through each field in the feature, and that's why you're getting the error, since the individual attribute key/value pairs don't have the attributes you're trying to pull out. To demonstrate:

var fs = $layer var f_1 = First(fs) for (var f in f_1){ console(`${f} : ${f_1[f]}`) }

Returns this in the messages:

BASEELEV : 141.36
BLDGHEIGHT : 10.74
BUILDINGID : 0712218018
EAVEHEIGHT : 10.74
FEATURECODE : 561
FLOORCOUNT : 0
LASTEDITOR : ESRI
LASTUPDATE : 2010-01-18T18:00:00-06:00
OBJECTID : 1
ROOFDIR : 0
ROOFFORM : Flat
SHAPE__Area : 55.71875
SHAPE__Length : 30.689584083596625

Just drop the "for r in recentrecord" loop and define the feat object in the larger "for v in valvefs" loop.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 16:44:36 GMT

That solved the error, but now my output is empty.

I believe the dictionary needs an identifier to map the fields, and with the for loop gone, how do I map the fields only present in the related record?

for (var v in valvefs){ var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["*"],false),'insdate DES'); var recentrecord = First(relatedrecords) var feat = { attributes: { assetid: ["valveid"], insdate: ["insdate"], diameter: v["diameter"], turns: ["turns"], fintorque: ["fintorque"], } } Push(features,feat); }; var joinedDict = { fields: [ { name: "assetid", type: "esriFieldTypeString" }, { name: "insdate", type: "esriFieldTypeDate" }, { name: "fintorque", type: "esriFieldTypeDouble" }, { name: "turns", type: "esriFieldTypeDouble" }, { name: "diameter", type: "esriFieldTypeDouble" }, ], geometryType: '', features: features }; return FeatureSet(Text(joinedDict))

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 16:50:34 GMT

Ah, okay. So, we still need to tell the expression where it's getting those attributes. recentrecord is the feature, so try something like this in defining your feat object. ~~I don't actually know which fields are coming from which feature, so I made a guess.~~

var feat = attributes: { assetid: recentrecord["valveid"], insdate: recentrecord["insdate"], diameter: v["diameter"], turns: recentrecord["turns"], fintorque: recentrecord["fintorque"], } }

EDIT: I just re-read your original expression. Just replace those "r"s with "recentrecord" and it should work.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 16:55:21 GMT

ah, simpler than I thought. It doesn't like null values though, and I'm not sure which field specifically is null. (Cannot call member method on null) maybe some additional logic to change nulls to "unknown"?

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 17:14:40 GMT

Null fields are fine to work with, it's the fact that you're probably trying to pull attributes from a null. You end up with a null when your related records featureset is empty.

Throw it inside of a big if that checks the Count of the relatedrecords featureset, and only proceeds to add a feature if records exist.

if (Count(relatedrecords)>0){ // do your recentrecord and feat stuff }

If you want to add something to the output for features with no related records, you can add an else statement and manually define the feat object with strings like 'No inspections on record', rather than pull related attributes.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 17:33:57 GMT

Making more progress, but when I test the code it now outputs empty rows.

for (var v in valvefs){ var relatedrecords = OrderBy(FeatureSetByRelationshipName(v, 'System_Valve_Inspection', ["*"],false),'insdate DES'); if (Count(relatedrecords)>0){ var recentrecord = First(relatedrecords) var feat = { attributes: { valveid: recentrecord["valveid"], insdate: recentrecord["insdate"], diameter: v["diameter"], turns: recentrecord["turns"], fintorque: recentrecord["fintorque"], } } Push(features,feat); }; }; var joinedDict = { fields: [ { name: "assetid", type: "esriFieldTypeString" }, { name: "insdate", type: "esriFieldTypeDate" }, { name: "fintorque", type: "esriFieldTypeDouble" }, { name: "turns", type: "esriFieldTypeDouble" }, { name: "diameter", type: "esriFieldTypeDouble" }, ], geometryType: '', features: features }; return FeatureSet(Text(joinedDict))

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 17:49:02 GMT

Can you share a screenshot of the output? Unless your data has empty rows to begin with, that shouldn't be happening.

Also, your joinedDict object defines a field "assetid", but the feat object has a "valveid" attribute now, so it's not going to pick that attribute up.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 17:53:47 GMT

It takes a long time to run so it's doing something. There are only 900 valves, but over 10,000 inspection records. I did change the field names so they all match. I wonder if it has something to do with the order of my loops?

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 18:13:55 GMT

You could try manually specifying the output fields in the relatedrecords featureset. Setting ['*'] returns all the attributes, which adds to the processing time, especially if there are many fields. Try

["insdate", "turns", "fintorque"]

Couldn't "valveid" be pulled from the parent feature, too?

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 18:40:33 GMT

I appreciate all the help here, I am still getting a result with an empty table.

would there be a better way to accomplish this? I'm really just interested in grabbing turns, torque, and diameter from the most recent inspection.

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Thu, 23 Dec 2021 18:50:41 GMT

I mean, if you're working in a dashboard, could you just have a list of valves that filters a details widget for the most recent related record upon selection? Or do you want the inspections part of the valves so the list can be sorted based on inspection data?

I still can't quite understand how you're getting an empty result. Any chance you could export an anonymized / obfuscated subset of the data so that I can test it against live data? I'm assuming the layer isn't public.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Thu, 23 Dec 2021 19:00:08 GMT

there might be a better way to do it, but my goal is to filter inspection data to show on a dashboard which valves exceed thresholds (i.e. # of turns greater than a specified amount, or torque greater than a specified amount) the dashboard filtering capabilities are perfect for this, but I need the data in a format the dashboard can use.

Unfortunately, working with related tables is proving to be quite frustrating. Would like to just highlight valves due for inspection (this part I have working) but then also flag ones based on past inspections (most recent) which I can't seem to load a dictionary for so far.

Re: Arcade Data Expression- Dictionary from multiple tables

ahassa22 — Thu, 23 Dec 2021 19:59:36 GMT

Are you getting a data access error or unable to execute arcade script?

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Mon, 27 Dec 2021 18:01:02 GMT

No, no syntax errors or anything like that. It executes and returns an empty featureset with the field names (really odd)

Is there perhaps a simpler way to accomplish this? I'm realizing Arcade can do a lot, but I'm new to using it in this capacity.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Wed, 29 Dec 2021 13:17:26 GMT

casting the date as a number solved the issue. My only further question now is this:

if I wanted to reduce processing time by filtering the related table down to the top 2000 records, or say records only in the past 5 years, would that go before the for loop, or within? I have also considered just creating a hosted view of the data to point this expression to.

Thanks so much for your input!

Re: Arcade Data Expression- Dictionary from multiple tables

jcarlson — Wed, 29 Dec 2021 18:30:01 GMT

Glad it's working!

Since it's the related records, you'd do a filter like that within the loop, after pulling the related records. Something like

var time_endpoint = DateAdd(Now(), -5, 'years')
var filt_records = Filter(relatedrecords, 'insdate >= @time_endpoint')

Then proceed as before, but reference filt_records in place of the original relatedrecords.

Re: Arcade Data Expression- Dictionary from multiple tables

TSmith — Tue, 04 Jan 2022 14:17:29 GMT

I thought that would work, but I get an execution error, probably having something to do with trying to do math on a date field. Maybe creating a new field in memory that's a number? I also know that there's ways to call a sql variable in the filter function, but I'm not sure what the syntax would be.

Something like:

var sql = 'insdate>= DATEADD(year, -5, GETDATE()'

it may also be easier to just filter on number of rows.

Re: Arcade Data Expression- Dictionary from multiple tables

abul123 — Fri, 19 May 2023 15:50:24 GMT

Thanks for sharing solution for this issue, we got it too. Converting the date field to number fix it!

Re: Arcade Data Expression- Dictionary from multiple tables

DavidNyenhuis1 — Thu, 15 Jun 2023 19:27:39 GMT

With this week's update to Arcade and ArcGIS Dashboards, date fields in feature set constructors now just work. You no longer have to wrap dates with Number() if you pass the dictionary into the FeatureSet() function (which as of this release accepts a dictionary as opposed to only text based JSON).

Instead of:

return FeatureSet(Text(dict))

Do this:

return FeatureSet(dict)

Learn more in this blog post.

NOTE: For Enterprise users, this update is targeted for 11.2