Arcade Expression - Show latest date from related table

[DylanW_TOC]

I'm trying to write an arcade expression that pulls the latest date from a related table for that feature. For example, if a sewer line was cleaned on 01/10/2023 and 05/10/2023, I would like the most recent date to populate. Another issue is that not all sewer lines have been cleaned; for those that haven't been cleaned, I'd like to return "Never cleaned". The code below is as far as I've gotten. Any suggestions on how to achieve this?

var relatedTable = FeatureSetById($datastore, '2'):
var relatedFeatures = Sort(relatedTable, Descending('CLEANDATE');
var latestFeature = First(relatedFeatures);
var dateValue = latestFeature['CLEANEDDATE'];
var formattedDate = Text(dateValue, 'MM/DD/YYYY'); 
return "Date: " + formattedDate;

[KenBuja · Accepted solution]

Here's a way to get the related records for a feature, using the FeatureSetByRelationshipName function. You'll have to look at your service to get the correct Relationship name. It's also using template literals to format the output.

var related = FeatureSetByRelationshipName($feature, 'your relationship name');
var output;

if (count(related)== 0) {
  output = `Never cleaned`;
} else {
  var relatedFeatures = OrderBy(related, 'CLEANEDDATE Desc');
  var latestFeature = First(relatedFeatures);
  output = `Date: ${Text(latestFeature['CLEANEDDATE'], 'MM/DD/YYYY')}`;
}
return output;

[DylanW_TOC]

Worked like a charm! Thank you so much!