Latest Contributions by LynnBerni

I am having trouble doing a 1:M join in arcgis pro.  But the thing is, when I first tried it worked perfectly when I used the Add Join tool. For my first try, I joined 182 points (SampleSites) to a related table containing the recurring sample data that is collected at each site (BacteriaSamples).  The resulting join has over 6,000 records with the warning (yellow triangle with exclamation mark) about "duplicate rows."  Exactly what I wanted. When I try joining SampleSites to a different table (EcoliRatings_Quarters_Years), it only completes a 1:1 join.   According to the Add Join  reference page, "t he input table must have an Object ID field to perform a one-to-many join." , https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/add-join.htm. My data has an ObjectID field. Not sure if this matters, but there is one difference between the two tables. The   BacteriaSamples   is an Enterprise GDB Table.    EcoliRatings_Quarters_Years   is a File GDB Table. It was an excel sheet that I imported to my Pro project, using the Excel to Table tool.  The point file is also in the EGDB.  Somebody please advise, thanks! Lynn ... View more

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. T he 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!!! */         ... View more