Referencing a previous row using a formula in a Lookup Column type

Hi everyone.
New to Coda but super excited to see the potential :clap: :clap:

The issue I am dealing with now is as follows:
I have a table with milestones and I want to create a dependency between each milestone to the one before.
Is there a way to link the rows automatically using a formula?

I have found in previous threads that it is necessary to use the Rank formula so that each row receives a specific number and therefore it is possible to reference it, but I can’t seem to complete the action.

Thanks in Advance!

Hi @Ranny_Shor ,
and welcome to Coda Community! :handshake:

I think that both RowId() and/or Rank() can be tricky as they might lead to inconsistencies; the former due to potential missing values, the latter because of row reordering.

At the same time, I think that if you build up a phases-based process - from a data modelling perspective - you need to have either

  • a sequence numbering (for templates)
  • a date range (for instances).

At that point, the discriminant in your filter would be one of the two.
In the first, simpler case, your dependency column might look like:

thisTable.Filter([Process Sequence] = thisRow.[Process Sequence]-1).First()

Let me know if this makes sense to you.

Cheers!

1 Like

Hi Federico, thanks for the quick answer :+1:
I didn’t fully understand how I can accomplish what I want without using the Rank formula, and why it might lead to inconsistencies.
I did manage to solve the issue by using the formula you suggested while using the Rank formula in a different column.

Still new to the whole idea of the formulas, I guess I’ll get better with time.
Thanks Again!
Ranny

Hi @Ranny_Shor ,
Feel free to ask anytime.

In the meantime, familiarise with Coda and enjoy!