Referencing the cell above

I personally think that there should NOT be a generic previousRow variable because as you said there is lot of ambiguity of what it should refer to. E.g.

  • If it’s a row whose RowID is the preceding one (i.e. the last undeleted row before this row)
    — it will be confusing for those who filter and sort and group.
    — not friendly to Coda newcomers (those who’d need this variable in the first place, since the more advanced users can just write a formula they need)

  • If it’s a visually previous row in current table or view
    — it will change every time you filter or sort or group
    — and besides, it would then have to calculate differently for different views, which isn’t easy to implement.

  • Make it a function with parameters like a series of filters and sorts
    — at this point it becomes no easier than adding that column and writing that formula (and actually seeing which rows it calculates)

So yeah, I think that it’s better to keep it an explicit thing. Besides, the formulas for finding the previous row are not that hard.

for RowID-based order (need to add RowID column as thisRow.RowID()):

thisTable.Filter(CurrentValue.RowID < thisRow.RowID).Sort(true, RowID).Last()

for visual position-based order (based on drag-n-drop row order in the master table, pre-sorting and pre-filtering):

thisTable.Nth(thisTable.Find(thisRow) - 1)

for visual position-based order in a filtered view (based on drag-n-drop row order in the master table, still pre-sorting):

MyView.Nth(MyView.Find(thisRow) - 1)

Same as above, but you need to account for ordering on ColumnA first, ColumnB second:

MyView.Nth(MyView.Sort(true, ColumnB).Sort(true, ColumnA).Find(thisRow) - 1)

for arbitrary value order where you know that values are sequential, unique, and increase at constant rate (e.g. one row per day with no days skipped):

thisTable.Filter(CurrentValue.Date = thisRow.Date - 1).First()


Note that in either case the previous row calculation will be a “heavy formula” and become exponentially slower as your table becomes larger. So if you can employ some other strategy than a live calculation of a running total / reference to the previous row etc, consider some other strategy:

Also my original post is somewhat invalid after all this time. @shishir’s sample doc shows how it’s best done (with a separate column).