Penultimate Linked Record

I’m trying to wrap my head around an idea.

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 :thinking:

Anybody has an idea how to achieve this?

There is no easy way using the link formula column. You’ll have to write a little script.

It’s not easy, but possible and it is a pain in the … if you want to maintain it! :slight_smile:

grafik

If you use the column {ttt1} it automatically creates a string list sperated by “;”.

mid(
  {ttt1},
  find(';', {ttt1}, 1) + 1,
  find(';', {ttt1}, find(';', {ttt1}, 1) + 1) - (find(';', {ttt1}, 1) + 1)
)

And you will have a problem, if there is a “;” character in the items.

Good effort!

Just one note: The formula requires that the records are sorted in a particular order. I would probably not rely on it.

Thank you both :pray: 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 :stuck_out_tongue_winking_eye:) 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.

Thanks again for your contributions!

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.