Help

Either: Display X most recent linked records OR Display all linked record up to a year prior to most recent record

Topic Labels: Formulas
2763 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Brett_Bailey
4 - Data Explorer
4 - Data Explorer

Imagine I have a table of books and a linked table of status updates such as coming soon, pre-order, published, out of print, each with an associated date. Dates can be in the past or the future. I have a custom book view with all the books, but I only want to list the most recent 2 or 3 status updates. Some books may have last been updated years ago while others were updated just this month.

I was unable to figure out how to do that. However I was able to display a year worth of updates leading up to the most recent one for each book. In the books table I created a Rollup field that showed that Max (latest) status date. Then in the status updates table I created two additional fields:

  1. link to the Max status date for each status entry
  2. A formula field called exclude: IF(Date < DATEADD({StatusMax},-365,‘days’),1)

Finally in my custom book view I only include linked records where exclude is not equal to 1.

Is there any way to accomplish filtering by X most recent dates OR is there any easier way to accomplish my method?

Thanks for any insight!

-B

2 Replies 2

Welcome to the community, @Brett_Bailey! :grinning_face_with_big_eyes: This is definitely doable with a little work. Going forward, I’ll use [Updates] and [Books] to refer to your two tables.

First off, create (or select) a view in the [Updates] table that sorts the update records by date, from oldest to newest. In that view, add an autonumber field named {Autonumber}, then make a formula field named {ID} using the following formula:

"|" & REPT("0", 4 - LEN(Autonumber & "")) & Autonumber

That will turn the number into a four-digit ID with a bar as a prefix (I’ll explain later why the bar is necessary). The total length of each ID is 5 characters, which we’ll use later on.

Screen Shot 2020-11-05 at 6.18.08 PM

In the [Books] table, add a rollup field named {IDs} that rolls up all of those IDs based on the links to their updates.

Screen Shot 2020-11-05 at 6.25.03 PM

Screen Shot 2020-11-05 at 6.31.32 PM

Now determine how many updates you want to see. Let’s say you choose 3. Make a formula field in [Books] with the following formula:

RIGHT(IDs, MIN(LEN(IDs), 15))

This will grab the rightmost 15 characters (3 IDs of 5 characters each) from the mashed up ID list. If there aren’t that many characters to grab, it will grab what it can.

Screen Shot 2020-11-05 at 6.29.44 PM

Back in [Updates], make a rollup field to bring in the value from this {Last 3} field:

Screen Shot 2020-11-05 at 6.34.07 PM

Screen Shot 2020-11-05 at 6.34.30 PM

Add a formula field named {ID Match} using the following formula:

FIND(ID, {Last 3}) > 0

That will return a 1 for any update record that’s part of that list of the last 3 for its associated book, and a 0 for all other fields. The leading bar before each number helps to delineate where each number begins; without it, a false match might be found between adjacent numbers. It’s also possible to use the default comma separation between numbers when building the initial rollup string, which makes the leading bar unnecessary. Either way works fine.

Screen Shot 2020-11-05 at 6.38.02 PM

Finally, make a new view named “Last 3 Updates”. Add a filter to this view that only shows records where {ID Match} contains a 1. This will only show the last 3 updates for each book. Group by book and sort by date to so that updates for each book are easier to review.

Tristan_Scifo
6 - Interface Innovator
6 - Interface Innovator

This is a clever solution, but OHHHH so clunky. It would be great to have a more seemless solve for this, which doesn't require so many moving parts.