I have an app where I manage projects and project meetings:
I am trying to see if it is possible to use a formula field to display a link to the previous project meeting when I am working on the current project meeting (i.e. the meeting with the greatest date prior to the date of the current meeting entity). I was trying to use the filter function, but couldn’t figure out how to both refer to the [Project Meetings] collection’s date field and the current meeting entity’s date field (pseudocode):
It’s not possible to refer to an entity’s own fields in the filter expression (you can only use the fields of the entity collection the filter applies to).
It has been discussed before, in another context
Anyway, I have made a demo app that sort-of solves your problem.
It uses a formula in the project type to find the date of the latest meeting, and then adds a ‘Latest’ flag to the whichever meeting has the matching date.
Then there is another project formula to find the latest date from all meetings that don’t have the ‘Latest’ flag set, in order to find the date of the penultimate meeting. The meeting that matches this date is flagged as ‘Penultimate’.
Finally, all meetings have a formula field to list the meetings that have either flag set (‘Latest’ and ‘Penultimate’). In this way, whichever meeting you are curently looking at, you can quickly navigate to the latest or penultimate.
I know it doesn’t exactly provide what you asked for, but I hope it helps your use case.