Show the Most Recent Edit Across Multiple Tables
TL;DR: This expression returns the most recent edit summary across multiple tables.
Why, Exactly?
I'll use a concrete example. One of our county departments tracks health complaints. These are organized into investigations (a point layer), with a running log of notes, attachments, etc., as well as a list of violations associated with the investigation (two related tables).
Staff interact with these investigations through a series of Dashboards and Survey123 forms. From a management perspective, however, we are interested in seeing the latest activity on an investigation, regardless of the specific table.
Can I do this without an expression?
Sort of! It is possible to browse through my related records, at least in the Classic Map Viewer. But that process, in clicks, looks like this:
- Click the feature
- Click Show Related Records
- Click the table of interest
- Optionally, click to sort the related table by date to more easily find the most recent edit
- Click Show Related Records
- Click the next table
And so on. And then I've got to remember each value, which was which, compare in my head (since they can't be displayed side-by-side), then I've got it.
Maybe building selection/filtering actions into my end-user Dashboard or Experience could reduce some of the clicking around, but at best, this just gets my recent edits per layer into my view for me to compare myself.
What I really want here is to remove that final step, too. Do the comparison and give me the single most recent edit so that I can tell at a glance what's been edited, when, and by whom.
So, let's get to the Arcade.
NOTE: This assumes your layers all use the standard editor tracking fields last_edited_date and last_edited_user.
Building the Expression
To get the output we want, we need the single most recently edited feature per related table. This can be done as follows:
var related_records = FeatureSetByRelationshipName($feature, "relationship_name")
var sorted_records = OrderBy(related_records, 'last_edited_date DESC')
var most_recent_record = First(sorted_records)
Custom Functions, Combining Functions
That's a lot of unnecessary intermediate outputs that would be repeated for each table. Whenever possible, it's desirable to combine functions together. But maybe not so much that your code becomes hard to understand.
Consider:
First(OrderBy(FeatureSetByRelationshipName($feature, "EH_Complaints_violations"), 'last_edited_date DESC'))
That's a valid expression, but it's pretty long. Feel free to throw in some line breaks if it helps you.
First(
OrderBy(
FeatureSetByRelationshipName(
$feature,
"EH_Complaints_violations"),
'last_edited_date DESC'))
In this example, though, we're going to have to type a lot of this out for each related table in our layer. Personally, I like to use custom functions for things like this. Here, I will use a custom function MostRecent to combine the First and OrderBy functions. I can then use that with each related table to get my most recent features very concisely.
function MostRecent(lyr){
return First(OrderBy(lyr, 'last_edited_date'))
}
var violations = FeatureSetByRelationshipName($feature, "EH_Complaints_violations")
var narrative = FeatureSetByRelationshipName($feature, "EH_Complaints_narrative")
var most_recent_v = MostRecent(violations)
var most_recent_n = MostRecent(narrative)
Comparisons
This is all well and good, but how do I get the most recent feature out of these individual features? I can't use OrderBy, because these are separate features, not a FeatureSet. There is the function Sort, though. But it takes an array and a comparison function as its arguments, and we're lacking those.
Easy enough for the array. Just stick brackets and commas around your features!
[most_recent_v, most_recent_n, $feature]
The comparison is simple. By taking the last_edited_date of our features, we just see which is greater, that is, more recent.
function DateSort(a, b){
if (a['last_edited_date'] > b['last_edited_date']){
return -1
} else {
return 1
}
}
var sorted = Sort([most_recent_v, most_recent_n, $feature], DateSort)
var most_recent_feature = First(sorted)
Now we've got an array of features sorted from most to least recently edited, and we can just take the First value of that array.
Except... how do we know which table it came from?
Backtrack and Include Table of Origin
Somewhere earlier in our expression, we've got to tie some table identifier to the individual features. The simplest way to do this is to use a dictionary. Or rather, an array of dictionaries. We'll format it as follows:
[
{table: 'Complaint Details', feat: $feature},
{table: 'Violations', feat: most_recent_v},
{table: 'Narrative', feat: most_recent_n}
]
Our DateSort function can still work the same way, but now we'll need to access the feature as a value of the larger dictionary. That is,
a['feat']['last_edited_date']
and so on. Now we've got our single feature, together with its table of origin. All that remains is to build an output string.
return `${most_recent_feature['table']} layer
edited ${most_recent_feature['feat']['last_edited_date']}`
But I've gone on long enough and given enough pieces of the expression. Let's wrap this up.
The Final Expression
Here it is, all thrown together. With a couple other tweaks, too, like adding the user's full name.
// Custom function to return most recent feature
function MostRecent(lyr){
return First(OrderBy(lyr, 'last_edited_date'))
}
// FeatureSets for each related table
var violations = FeatureSetByRelationshipName($feature, "EH_Complaints_violations")
var narrative = FeatureSetByRelationshipName($feature, "EH_Complaints_narrative")
// Array of feature dicts with table of origin
var feats = [
{table: 'Complaint Details', feat: $feature},
{table: 'Violations', feat: MostRecent(violations)},
{table: 'Narrative', feat: MostRecent(narrative)}
]
// Sorting function
function DateSort(a, b){
if (a['feat']['last_edited_date'] > b['feat']['last_edited_date']){
return -1
} else {
return 1
}
}
var most_recent = First(Sort(feats, DateSort))
// Extra lines for getting a user's full name
var portal = Portal('https://maps.co.kendall.il.us/portal')
var editing_user = GetUser(portal, most_recent['feat']['last_edited_user'])
// Return a formatted output string based on the most recent feature
return `${most_recent['table']} table
edited ${Text(most_recent['feat']['last_edited_date'], 'DD-MMM-YYYY')}
by ${editing_user}`
And after all that, here's the output:
It may seem like a bit much for three lines of text, but now the user who oversees this program can get at a piece of information they want in a single click.