Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 28, 2020 08:08 AM
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?
Solved! Go to Solution.
Aug 31, 2020 11:49 AM
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)
Aug 31, 2020 10:23 AM
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
Aug 31, 2020 11:49 AM
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)