I have a hosted Feature Layer (SampleSites) with water quality sampling locations. The sample data is saved in a hosted Table (BacteriaSamples) which has the SITEID field in common. From this table I am looking at the MPN_ECOLI field to designate a "rating" for each sample collected, based on the following criteria:
MPN_ECOLI <100 'Good'; > 405 'Poor'; 101-405 'Fair'
I have a When function inside a For Loop to create the rating for each sample and have tested that it works by returning a list in my expression (see my code example saved here). But what I really want to do, and cannot figure out, is how to use the ratings created inside the loop as input for additional functions.
Ultimately, my goal is to designate an Ecoli Rating for each site, as defined by the lowest quality samples collected at that site. For example, if any samples are rated as 'Poor', the Ecoli Rating for the site is deemed 'Poor'. If no samples are 'Poor', but some are 'Fair', then 'Fair'. Else 'Good'.
I tried using Distinct() inside the loop and got nothing. I tried Distinct outside the loop and it returned [Poor], but if it was working it should have returned all three. I know this because the first site that arcade "looks at" inside my expression has 49 bacteria samples, representing Good, Fair, and Poor samples. Interestingly, the last sample in the list is rated as 'Poor'. (Is Distinct somehow looking at my last sample rating only?)
Couple other things to note: I've been working on this expression in "configure popup" in ArcGIS Online. The hosted data is in Portal, but when I tried this expression in a web map in Portal, it doesn't work.
I'm thinking (hoping) Xander Bakker will have an answer for me 🙂
Thanks!
Lynn
p.s. ultimately, I want this expression to update the symbology of the sites, but as I understand it, that functionality is not yet available with arcade.
/*
store SiteID as a variable so it can be used to query
the BacteriaSamples table for matching records
*/
var siteId = $feature.SITEID
/*
Access Bacteria Samples (related table) as a FeatureSet with three
relevant fields and filtered on QA/QC status
*/
var samplesAll = Filter(FeatureSetById($map, /* Watershed - BacteriaSamples */ "Watershed_962", ['SAMPLEDATE', 'SITEID','MPN_ECOLI']), "QAQC_COMPL = 'yes'")
/*
Use SQL filter statement to access variable with "@";
essentially this creates a relate
*/
var filterStatement = "SITEID = @siteId"
//filter the table to find related records at each site
var samplesBysite = Filter(samplesAll, filterStatement)
//Store count of resulting records as a variable
var cnt = Count(samplesBysite)
Console("Number of Samples: " + cnt)
/*
loop thru the records to calculate a rating for each
sample based on following criteria:
MPN_ECOLI<100=Good; >405=Poor; 101-405=Fair
*/
var rating = ''
if (cnt > 0) {
for(var f in samplesBysite){
//returns a list of each sample rating
rating += When(f.MPN_ECOLI < 100, 'Good', f.MPN_ECOLI > 405, 'Poor', 'Fair') + TextFormatting.NewLine
}
return (rating)
} else {
rating = "No sample data"
}
/*
Next, determine final ecoliRating for the site. If any sample rating is 'Poor', the final ecoliRating for the site is 'Poor'. If no samples are 'Poor' but some are 'Fair', then 'Fair'. Else 'Good'. Help!!!
*/
