Help

Producing most recent field by date attached

Topic Labels: Formulas
Solved
Jump to Solution
698 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Dani_Oliver
6 - Interface Innovator
6 - Interface Innovator

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?

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

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)
image

The child table (Books)
image

See Solution in Thread

2 Replies 2
Zollie
10 - Mercury
10 - Mercury

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:

Screen Shot 2020-08-31 at 12.21.28 PM

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
18 - Pluto
18 - Pluto

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)
image

The child table (Books)
image