Arcade - return a related record only if it passes filter

[ZachBodenner]

Hello,

I have a set of data (Trees) with a related table (Maintenance Performed on Trees), both added to a web map. The related table contains records of tree removals, chemical treatments, inspections, etc.  I want to set up an Arcade expression to get related records only if they satisfy the criteria of "Maintenance_Performed" = "Removal". The easy solution would be to set a filter on the table in the web map structure, but the users need all of the records for purposes beyond just this expression. However, the table can contain multiple related records, and my expression is currently having trouble returning only removal records:

var asset_id = $feature.GlobalID
var relatedTable = FeatureSetByName($map, "Tree Inventory Form",['Maintenance_Performed','Date','TreeInv_Rel']);
var sortedTable = OrderBy(relatedTable, 'Date DESC')
//var onlyRemoved = Filter(sortedTable,'Maintenance_Performed = Removal')
var relatedRecord = First(Filter(sortedTable, "TreeInv_Rel = @asset_id"))

console ("Related Record:",relatedRecord)

var recordDate = relatedRecord.Date
var maintPerformed = relatedRecord.Maintenance_Performed
console ("Record Date: ",recordDate,TextFormatting.NewLine,"Maintenance performed: ",maintPerformed)

if(relatedRecord == null) { 
  return 
  }

else if (!isEmpty(recordDate) && maintPerformed == 'Removal'){
  return Text(recordDate,'MMM DD, YYYY')
  }

else if(isEmpty(recordDate) && maintPerformed == 'Removal'){
  return ' an unrecorded date'
  }

else {
  return
}

I've tried this a couple ways: the way that the expression is shown above is trying to set the return based on Maintenance_Performed as a variable. I think this doesn't work though because the filter isn't necessarily accessing the right record. For example, I have a tree with two records: record one on 12/15/2021 is Removal, and record 2 on 7/28/2022 is a stump removal. If I access the most recent record, the filter pulls the stump grind record even though I want the removal record. 

My next thought was to try another filter so that only "Maintenance_Performed = Removal" passes the filter. That's the "//var onlyRemoved = Filter(sortedTable,'Maintenance_Performed = Removal')" line, but when I uncomment that out and change the next variable to use onlyRemoved, I get the error "Unknown Error."

I'm close, can anyone get me over the hump?

[KenBuja]

You need to put quotes around Removal in your filter

var onlyRemoved = Filter(sortedTable,'Maintenance_Performed = "Removal"');

[ZachBodenner]

I thought that might be the issue too, but updating the expression to this:

var asset_id = $feature.GlobalID
var relatedTable = FeatureSetByName($map, "Tree Inventory Form",['Maintenance_Performed','Date','TreeInv_Rel']);
var sortedTable = OrderBy(relatedTable, 'Date DESC')
var onlyRemoved = Filter(sortedTable,'Maintenance_Performed = "Removal"');
var relatedRecord = First(Filter(onlyRemoved, "TreeInv_Rel = @asset_id"))

console ("Related Record:",relatedRecord)

var recordDate = relatedRecord.Date
var maintPerformed = relatedRecord.Maintenance_Performed
console ("Record Date: ",recordDate,TextFormatting.NewLine,"Maintenance performed: ",maintPerformed)

if(relatedRecord == null) { 
  return 
  }

else if (!isEmpty(recordDate) && maintPerformed == 'Removal'){
  return Text(recordDate,'MMM DD, YYYY')
  }

else if(isEmpty(recordDate) && maintPerformed == 'Removal'){
  return ' an unrecorded date'
  }

else {
  return
}

gives me the error with no indication of where it's coming from (though presumably the new filter statement somehow):

Expected "'", "(", "+", "-", "@", "CASE", "DATE", "EXTRACT", "FALSE", "INTERVAL", "N'", "NULL", "POSITION", "SUBSTRING", "TIMESTAMP", "TRIM", "TRUE", "`", [ \t\n\r], [0-9], or [A-Za-z_\x80-￿] but "\"" found.

[KenBuja]

Is it possible that the Maintenance_Performed field uses a domain?

[ZachBodenner]

Oh yeah it does for sure! Where would be the place to add Domain() to the expression? When I try to add it to the filter statement like Filter(Domain(... the expression builder doesn't like it.

[KenBuja]

var onlyRemoved = Filter(sortedTable,'Maintenance_Performed = 1'); //or whatever the domain code is for Removal

You should use the numeric domain code, since the SQL function won't understand how to work with them.

[ZachBodenner]

I'm very sorry, I'm not entirely sure I understand. So if the domain is a text string only coded domain, there's no way to use the attribute in a filter? If that's the case, is there an alternative way where I can ensure that only removal records get returned in the expression?

[KenBuja]

What does your domain look like?

[ZachBodenner]

Pretty straightforward I think. This is a referenced feature service, so the domain is stored in the egdb:

[KenBuja · Accepted solution]

That SQL statement is picky! Try this instead (changing the quotes)

var onlyRemoved = Filter(sortedTable,"Maintenance_Performed = 'Removal'");