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

[Brett_Bailey]

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

[Justin_Barrett]

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.

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

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.

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

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.

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]

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.