Using Arcade to bring related table info into pop-up - so close!

[FPGIS_FrancescaRohr]

Hello,

I have a feature service of tree points with a related table of inspections (1:M). I would like the pop-up to display the inspection date for those trees that have been inspected (related table field: Inspected_Date) . 

The FeatureLayer/RelatedTable relationship is based on GlobalID/GUID but there is also matching data in the UNIQUE_ID/Unique_ID fields.

I have an Arcade expression that correctly returns 'No inspections' for those trees that have not been inspected. For the trees that have been inspected, the expression returns the same date '5/23/2022' for all trees instead of the actual inspection date.

What do I need to tweak to make it work?

var tbl = FeatureSetByName($datastore,"CALFIRE_UrbanForestry_Inspections", ['Unique_ID', 'Inspection_Date'])
var uniqueid = $feature["Unique_ID"]
var sql = "UNIQUE_ID = @uniqueid"
Console(sql)
var inspections = Filter(tbl, sql)
var cnt = Count(inspections)
var history = ""
if (cnt > 0) {
history = "inspections: " + cnt
for (var inspections in tbl) {
var insp_date = Text(inspections.Inspection_Date, 'MM/DD/YYYY')
history = "Inspected: " + insp_date
}
} else {
history = "No inspections"
}

return history

All help is greatly appreciated,

Francesca

[JohannesLindner · Accepted solution]

It's because you iterate over tbl, not over the filtered inspections. Your expressions returns the date of the last entry in the inspection table.

var tbl = FeatureSetByName($datastore,"TestPoints", ['UNIQUE_ID', 'Inspection_Date'])
var uniqueid = $feature["Unique_ID"]
var sql = "UNIQUE_ID= @uniqueid"
Console(sql)
// Filter by UNIQUE_ID and sort the result by date
var inspections = OrderBy(Filter(tbl, sql), "Inspection_Date")
var cnt = Count(inspections)
// return early if there are no inspections
if(cnt == 0) {
    return "No inspections"
}
// get all inspections in a list
var history = [
    `${cnt} inspections`
    ]
for (var inspection in inspections) {
    var insp_date = Text(inspection.Inspection_Date, 'MM/DD/YYYY')
    Push(history, `Inspected: ${insp_date}`)
}
// return the concatenated list
return Concatenate(history, TextFormatting.NewLine)

If you only want to return the latest inspection, you can do it like this:

var tbl = FeatureSetByName($datastore,"TestPoints", ['UNIQUE_ID', 'Inspection_Date'])
var uniqueid = $feature["Unique_ID"]
var sql = "UNIQUE_ID= @uniqueid"
Console(sql)
// Filter by UNIQUE_ID and sort the result by date DESCENDING
var inspections = OrderBy(Filter(tbl, sql), "Inspection_Date DESC")
var cnt = Count(inspections)
// return early if there are no inspections
if(cnt == 0) {
    return "No inspections"
}
// get the last inspection date
var insp_date = Text(First(inspections).Inspection_Date, 'MM/DD/YYYY')
var history = [
    `${cnt} inspections`,
    `Last inspection: ${insp_date}`
    ]
return Concatenate(history, TextFormatting.NewLine)

[FPGIS_FrancescaRohr]

Wonderful, thank you! I think the piece that I still don't understand is 

for (var inspection in inspections) 

because I haven't defined var inspection, but hey, it works!

[JohannesLindner]

You define inspection directly in the for loop declaration. That's what the var does: It creates a variable. It's a shortcut. You could also do it outside the loop. These two loops work exactly the same:

var arr = [1, 2, 3]

var i
for(i in arr) {
    Console(arr[i])
}

for(var j in arr) {
    Console(arr[j])
}