topic Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator in ArcGIS Dashboards Questions

Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 23 Apr 2025 15:47:06 GMT

I have a hydrant inspection layer that I have configured a dashboard for and I am trying to get the indicator widget to reflect the correct number of hydrants still needing inspections. Currently the number is inflated because there are multiple inspections per hydrant. I have been working on trying to create a data expression to create a FeatureSet to filter most recent inspections per hydrant, but I am having no luck. Every code I have tried multiple code combinations, and it just returns an empty dataset.

// Load the hydrant inspections layer
var inspections = FeatureSetByPortalItem(
Portal("https://maps.casselberry.org/portal"),
"f3a4a8879ce34241a5fd139ddfaf727a", // Inspection Layer Item ID
0
);

var maxDate = Text(Date(Max(fs, 'dateinspected')), 'YYYY-MM-DD');
return Filter(fs, 'dateinspected = @maxDate');

This code is based on this github post I had found

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

KenBuja — Wed, 23 Apr 2025 16:23:38 GMT

The dataset in the Github repository example has that date in a text field, not a date field. If your field is a date field, you can do this much easier using the OrderBy and First functions

var inspections = FeatureSetByPortalItem( Portal("https://maps.casselberry.org/portal"), "f3a4a8879ce34241a5fd139ddfaf727a", // Inspection Layer Item ID 0 ); return First(OrderBy(inspections, "dateinspected DESC"));

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 23 Apr 2025 16:26:18 GMT

Thank you for your prompt response! I tested it and it appeared to work till I clicked done and then got this.

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

KenBuja — Wed, 23 Apr 2025 16:39:40 GMT

Ah, I forgot the first rule of Data Expressions...always return a FeatureSet.

This should work

var inspections = FeatureSetByPortalItem( Portal("https://maps.casselberry.org/portal"), "f3a4a8879ce34241a5fd139ddfaf727a", // Inspection Layer Item ID 0 ); var feat = First(OrderBy(inspections, "dateinspected DESC")); var dict = { fields: Schema(inspections ).fields, features: [feat] }; return FeatureSet(dict);

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 23 Apr 2025 16:45:30 GMT

Unfortunately still not working..

Will this code return most recent for EACH hydrant not just most recent inspections done for all hydrants? so most recent inspection per hydrantid.

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

KenBuja — Wed, 23 Apr 2025 17:10:25 GMT

Are you using an older version of Enterprise? If so, you'll have to use

return FeatureSet(Text(dict));

And yes, this code only returns the most recent inspection.

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

KenBuja — Wed, 23 Apr 2025 17:28:18 GMT

To get the latest inspection for all of the hydrants, this code should work. Make sure you change the name of 'HyrantID' to the correct field name

var inspections = FeatureSetByPortalItem( Portal("https://maps.casselberry.org/portal"), "f3a4a8879ce34241a5fd139ddfaf727a", // Inspection Layer Item ID 0 ); GroupBy(inspections , 'HyrantID', {name: 'Latest', expression: 'dateinspected', statistic: 'MAX'})

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 23 Apr 2025 18:24:29 GMT

OMG this worked! Thank you soooo much! I have been going crazy with this code for days! I appreciate it!

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 30 Apr 2025 18:36:59 GMT

@KenBuja Your code that you gave worked beautifully however, I have now some hydrant inspections that have defaulted the null value for date 12/30/1899 and I also need those to return in the feature set. Is this possible with editing your original code?

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

KenBuja — Wed, 30 Apr 2025 19:19:07 GMT

You can add multiple statistics to the GroupBy function. This will return the latest and earliest inspections for each hydrant. However, this does not include dates that are null

GroupBy(inspections , 'HydrantID', [{name: 'Latest', expression: 'dateinspected', statistic: 'MAX'},{name: 'Earliest', expression: 'dateinspected', statistic: 'MIN'}])

Re: Data Expression To Return Most Recent Date for Hydrant Inspection in Dashboard Indicator

MelanieBass — Wed, 30 Apr 2025 20:37:00 GMT

This worked! thanks again. 🙂