How to get attribute with last date modified from related table using Arcade

[KellyDoss1]

Hello all,

I'm pretty new to Arcade and I'm having problems.  I believe I need to be using FeatureSets in Arcade.

I have a hosted feature layer with several related tables, some of which are 1:M.  In the CIP_Test pop-up, for the 1:M related tables, I only need to show the data from the rows with the latest "Date Modified".

I've attached a screenshot of the related table.  Where there are duplicate AcctNo, I only want to show the ProjectPhase (and maybe other fields) for the record that was last modified.

Any suggestions on how to do this are much appreciated!  I have gotten nothing but errors in everything I've tried from any forum posts I've found....

[TimFlynn]

Xander,

This works but now I've realized that for multiple inspections it will still only return the first inspection date.  What do I need to add to return the most recent inspection date?

My code is as follows:

var relatedrecords = OrderBy(FeatureSetByRelationshipName($feature,"AST_Inspection"), "insp_date");
var cnt = Count(relatedrecords);

var relatedinfo = "";
if (cnt > 0) {
 var info = First(relatedrecords);
 relatedinfo = Text(ToLocal(info.insp_date), "MM/DD/Y");
}

return relatedrecords‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I tried adding "Top(OrderBy(...),1)"  but that doesn't seem to make a difference.  

Thanks,

Tim

[TimFlynn]

Never mind Xander Bakker I figured it out!  I simply added "insp_date DES" and that did the trick!  Also added a nifty else statement for empty lists.

Thanks for all the help in your many responses on these forums.

[XanderBakker]

I didn't see your responde before, but I'm glad you resolved it. It was the same thing I was proposing.  

[Anonymous User]

I'm working on something similar and I'm not quite sure how I can alter @TimFlynn's code to do what I need it to do.  
I have a pump station layer with a related table of inspections with a One to Many relationship. My client needs to be able to reference the last recorded gallons of water.  ie. for each pump station I want to list the last recorded gallons of water from the related inspection table.  I then want to display that value in the popup.  I'm still new to Arcade, so I've been researching but can't figure how to set it up.  I really wish there was a "LAST" function.  
Can you guide me on the best ways to set this up?  @XanderBakker 

Thanks,

Katie

[XanderBakker]

Hi @Anonymous User ,

I have change the code a bit and included some comment that should guide you to create the expression:

// get the related records
// change "AST_Inspection" by the name of the relationship class you have
// change "insp_date" by the date field in your related data, add any additional fields you need further on
var relatedrecords = FeatureSetByRelationshipName($feature, "AST_Inspection", ['insp_date'], false);

// get a count of the related records
var cnt = Count(relatedrecords);

// initialize a variable that will contain the information to return
var relatedinfo = "";

// only proceed if you have related records
if (cnt > 0) {
    // sort those records by date descending 
    // change "insp_date" by the date field in your related data
    var sortedrecords = OrderBy(relatedrecords, "insp_date DES");

    // get the first record of the sorted related info
    var latestinfo = First(sortedrecords);
    
    // get the last date and other information if relevant
    relatedinfo = Text(ToLocal(latestinfo["insp_date"]), "MM/DD/Y");
    
} else {
    // in case you don't have related info
    relatedinfo = "No related information available...";        
}

// return the result
return relatedinfo;

If you have any questions, feel free to reach out.

[Anonymous User]

Thank you!! That is super helpful!!

[Anonymous User]

So this won't work if viewing the popup in Collector, Explorer, or Field Maps, and it also won't work if reference in custom URL to pass to survey123 due to the "FeatureSetBy" function.  I'd like to suggest that ESRI adds this functionality to at least fields maps.

[XanderBakker]

Hi @Anonymous User ,

I think something else might be going on. I just opened the web map created for this blog post: https://community.esri.com/t5/arcgis-online-documents/using-featuresetby-functions-in-arcade-to-drill-down-to-other/ta-p/918691 in ArcGIS Field Maps and as you can see in the post, it contains Arcade in symbology and the pop-up. Most of the layers have a FeatureSetByName function in the arcade expressions and work fine in Field Maps with the exception of returning date with a newline.

In the example below, on the left, it will intersect the larger sector polygon with the subsectors and order the result, the middle does an intersect of sub-sectors on hydrants and create some summary statistics and on the right, it will use a filter query a standalone table and return a list of maintenances at the hydrant. The only thing that doesn't work is the new line in the list of maintenances.

In another map, I have connection points and in the pop-up I have a link to a survey in which I prepopulate a number of fields. This also works as expected. 

[Anonymous User]

Interesting... I ended up using the arcade expression below.  Do you see anything in there that could be causing it to not work in field maps?  It works fine when I access the popup in ArcOnline in a browser. Maybe my date field is throwing it off?  Tomorrow morning, I'll try the code you listed above see if it works in field maps.

var sql = "FACILITYID = '"+$feature.FACILITYID + "'";
var tbl = Filter(FeatureSetByName($map,"pumpstation_gdb - PumpStationinspection"), sql)

if ((Count(tbl))==0) {
    return "no records found"
}
else {
    return First(OrderBy(tbl, 'REPDATE DESC')).WaterLevel
}

[XanderBakker]

Hi @Anonymous User ,

Not sure what could be causing the problem. I don't see anything wrong with the code. I just split the code into a couple more lines. Maybe you can check if this has different results.

var facilityid = $feature.FACILITYID;
var sql = "FACILITYID = @facilityid";
var fs = FeatureSetByName($map, "pumpstation_gdb - PumpStationinspection", ["REPDATE", "WaterLevel"], false);
var tbl = Filter(fs, sql);

if (Count(tbl) == 0) {
    return "no records found";
} else {
    var orderedtbl = OrderBy(tbl, 'REPDATE DESC');
    var latest = First(orderedtbl);
    return latest["WaterLevel"];
}