Producing most recent field by date attached

[Dani_Oliver]

Not sure how to describe this one…

I have a primary field in one tab of Publications. Each publication has a Pub Date column.

In another tab, I have Last Name as a primary field (of authors) and want to create a column that tells me the most recent publication they were attached to.

If I use Roll-Up with max value, I can get the pub date itself (1/12/20) to appear rather than the name of the most recent publication the author is attached to. So I think I need a formula that would produce, instead, the most recent Publication the author is attached to.

Any thoughts?

[Zollie]

That’s a tough one. Not a great or complete solution here, but here are some thoughts. I know you can pluck the first record from a list of linked records like this:

LEFT(""&{publications},FIND(",",ARRAYJOIN({publications}))-1)

But the order of the linked records matter then. For that, you might be able to use something like batch update as recommended by @kuovonne

[kuovonne · Accepted solution]

This can be done with a system of rollups, lookups, and formula fields. No scripting required, and the order of the linked records doesn’t matter.

In the parent table, use a rollup to identify the latest date. In the child table, use a lookup to see the latest date for the set and compare that date with its own date to see if it is the latest. Finally, have the parent table use a conditional lookup to show only the latest of its children.

Here is a sample that shows both the earliest and latest linked records.

The parent table (Authors)

The child table (Books)