topic Re: Arcade - return a related record only if it passes filter in ArcGIS Enterprise Questions

Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 14:36:52 GMT

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?

Re: Arcade - return a related record only if it passes filter

KenBuja — Wed, 22 Nov 2023 15:08:04 GMT

You need to put quotes around Removal in your filter

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

Re: Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 15:18:16 GMT

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.

Re: Arcade - return a related record only if it passes filter

KenBuja — Wed, 22 Nov 2023 15:36:03 GMT

Is it possible that the Maintenance_Performed field uses a domain?

Re: Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 15:42:24 GMT

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.

Re: Arcade - return a related record only if it passes filter

KenBuja — Wed, 22 Nov 2023 16:05:09 GMT

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.

Re: Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 16:10:53 GMT

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?

Re: Arcade - return a related record only if it passes filter

KenBuja — Wed, 22 Nov 2023 16:28:51 GMT

What does your domain look like?

Re: Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 16:37:13 GMT

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

Re: Arcade - return a related record only if it passes filter

KenBuja — Wed, 22 Nov 2023 16:59:08 GMT

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

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

Re: Arcade - return a related record only if it passes filter

ZachBodenner — Wed, 22 Nov 2023 17:36:05 GMT

That's so finnicky, especially since everywhere else Arcade is quote-type-agnostic. Thanks for the help as always Ken, you're a pro!