With ‘Link Formula’ utilizing the findmax expression it is easy to retrieve the latest entry (evaluating a date column in this case) for a given record in the linked ‘lower’ table. Is there a way to retrieve the penultimate record as well? As an example, imagine Table A holds a persons record, Table B holds various records that are linked to this specific person. One column in Table B is a text field (let’s say it’s called comments).
Now in table A, I want to have a column that reflects the latest comment of the person (solved by findmax) and another column that reflects the penultimate comment of that same person (ignoring any older comments) How could I go about that? I was trying to do this using date formulas but so far a piece of the puzzle is missing
Thank you both Interesting approach! I will have to think about and tinker with it a bit more but that gave me some good food for thought and a nice starting point.
I just wanted to share a quite simple way that allowed me to solve this. This is by no means a true and comprehensive solution but it works for my use-case.
I utilized the findmax Link Formula as before but with the addition of a condition for the date → Date is before today. This way at least at the time of creating the new record (and until today becomes yesterday ) I will get my penultimate entry. Limitations of the use-case are that the entries have to be days apart and that you have to process the record (create PDF or export etc.) within a certain timeframe. All of those are true for this specific project and the solution does what I need.