How to create an arcade expression to show most recent Related Table data.

[Anonymous User]

I have a geodatabase with a pump station layer and an inspection table related to the pump station layer with a one to many relationship.  I created a survey using the related table so new surveys simply add to the related inspection table.  

I have a map with my pump station layer and related tables.  I created a custom URL in the popup to launch Survey123 to perform an inspection on the selected pump station.  The Custom URL passes the pump station ID to Survey123, then the user fills out the rest of the inspection. One of the inspection questions requires the user to write in the current water level.  The user has to monitor the change in water level, Which mean the user needs to be able to reference the water recorded in the previous inspection for that pump station.  

I'd like to add that information to my custom url so the most recently recorded water level for a pump station is simply passed to a field specific in the Survey.  The user than can see the previous water level, enter the current water level, and the next survey question automatically calculate the change in water level.  

I planned to create a custom arcade expression to reference the most recent water level for each pump station, but I'm unsure how to write the expression, and need some guidance.  I've done a lot of research and testing and I'm worried this just isn't possible. 

@XanderBakker   Added your name since you seem to be the Arcade Scripting King 🙂 

[DougBrowning]

First part is to go grab the matching records from the other layer.  This is FeatureSet - can use by name or by relationship name if you have one.

var sql = "PointID = '" + $feature.PointID + "'";
var tbl = Filter(FeatureSetByName($map,"Points", ["*"], false), sql);

Then you can grab the first. Or do a sort, max etc.

return First(tbl).DesignLat

Or loop through them and/ or sort.

This example is pattern matching

var sql = "PlotKey = '" + $feature.PlotKey + "'";
var tbl = Filter(FeatureSetByName($map,"LPI"), sql);

var txt = ''
for (var f in OrderBy(tbl,"LineNumber")) {
    txt = txt + f.LineNumber + ' '
}
txt = Left(txt,Count(txt)-1)
if (txt != '1 2 3') {
    return "\n----LPI Line Number Issue! Found: " + txt
}
else {
    return ''
}

I use it a lot, works great.  Note FeatureSet does not work in Collector but does in the new field maps.

Hope that helps

[Anonymous User]

Thank you so much for your quick help on this!!   Bear with me, still confused on things!  I'm unsure of what I should swap out where in your example script and how to alter it to fit my needs.  This is what I have so far and I'm not sure where to go from here.  I apologize, I tried to read through the arcade logic documentation and just can't seem to wrap my brain around it. 

Also I don't understand the reasoning between the empty quotes ' '.  I tried googling it to educate myself but couldn't immediately find anything to explain it. 

FACILITYID is the field I use to relate the table to the pump stations
REPDATE is the inspection date
H2OLEV is the field I'm trying to list the most recent recorded value for each pump station. 

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

var txt = ''
for (var f in OrderBy(tbl, 'REPDATE DESC')) {
    txt = txt + f.REPDATE + ''

@XanderBakker 

[DougBrowning]

Try this

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

return First(OrderBy(tbl, 'REPDATE DESC')).H2OLEV

[Anonymous User]

What you wrote makes logical sense to me.  But when I test it, I get an error "Runtime Error: Cannot call member method on null. H2OLEV"  Does this mean I need to tell it to ignore nulls? Or do nothing if there are nulls?  This is a brand new inspection table, I only have one inspection record for one pump station in there that is just tester data.  

[DougBrowning · Accepted solution]

Well yea you gotta test on some real data. 🙂 

but you could stick in a if

if count(tbl) == 0

     return "no records found"

else

return First(OrderBy(tbl, 'REPDATE DESC')).H2OLEV

[Anonymous User · Accepted solution]

@DougBrowning 

You, Sir are a lifesaver! Thank you so much for your help!!!  I got it to work!   I see why you have "MVP" next to your name.  😁

Below is my final code if anyone else needs to use it.

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')).H2OLEV
}

[Krocha]

Can you please explain how to set this up?? I've been trying for hours.

[dommybo123]

How would you suggest getting the entire inspection record? For some reason, whenever I use the First() function, no records show up.

I tried a loop function, but it doesn't seem to be working:

var sql = "GUID = '"+$feature.GlobalID + "'";
var tbl = Filter(FeatureSetByName($datastore, "Pruning Maintenance"), sql)

if ((Count(tbl))==0) {
return "No Inspection Data to Show"
}
else {
var output = ''
for (var record in OrderBy(tbl, 'InspectionDate DESC')){
output += Text(First(record.InspectionDate), 'MM DD YYYY') + TextFormatting.NewLine +
First(record.RiskRating) + TextFormatting.NewLine + First(record.Notes)
}
return output
}

It also doesn't work when I do: 

for (var record in First(OrderBy(tbl, 'InspectionDate DESC')){

Are there any workarounds you can think of? As soon as I delete the "First()", the pop-up magically works. But that defeats the purpose because I want the most recent record.

[DougBrowning]

If you just want the first one why are you looping?

I think this would work

var sql = "GUID = '"+$feature.GlobalID + "'";
var tbl = Filter(FeatureSetByName($datastore, "Pruning Maintenance"), sql)

if ((Count(tbl))==0) {
return "No Inspection Data to Show"
}
else {
var output = ''
var tblOrder = OrderBy(tbl, 'InspectionDate DESC')
output = Text(First(tblOrder.InspectionDate), 'MM DD YYYY') + TextFormatting.NewLine +
First(tblOrder.RiskRating) + TextFormatting.NewLine + First(tblOrder.Notes)

return output
}

  Try that