How to get a value from the previous row?

Thanks, George. That works, but it seems like the UI could be better in this area. There should be an expand button that switches to a multi-line editor, with a drag handle to increase the size further.

Any suggestions regarding selecting previous records? It would be nice if there was a function that would select the prior record based on a specified column. For example:

lookupPrevious("End_Date", $End_Date or TODAY())

This first parameter would specify the column to compare against, and the second parameter would specify the value to compare against that column. So, in this example, it would compare the current end date with the end date column, and if the current end date is blank, it would compare today’s date with the end date column.

Adding an easy way to reference the previous record is definitely on our radar.

In the meantime, let me see if I can come up with an alternative solution for you. The method below can be a little confusing but I’ll do my best to explain each step.

First, go ahead and make a new table in your document called “Global”, and rename the first column in that new table to “sorted_list”. In the “sorted_list” column, go ahead and start editing the first row, and paste in the following formula, substituting YOUR_TABLE_NAME on the last line with the name of the table that has your travel days.

from sortedcontainers import SortedList
SortedList(YOUR_TABLE_NAME.all, key=lambda r: r.End_Date or TODAY())

Go ahead and save the formula, and if all is well, you should see a value in the first row that starts with something like SortedListWithKey.

What this formula does is sorts all of the records in the table titled YOUR_TABLE_NAME in order of increasing End_Date. On the last line of the formula, the part after key=lambda r: is where the sort behavior is specified. Here, r.End_Date or TODAY() is telling the formula to sort (in increasing order) using the End_Date column of your table, and if that column is blank (i.e. r.End_Date is None) for a given row, use today’s date instead (TODAY()). You are free to tweak this as needed – if End_Date will never be blank for instance, the or TODAY() isn’t necessary.

Now that we have this special column in the “Global” table, let’s return to the travel days table and try it out in a formula. For this example, go ahead and make a new formula column and paste in the following formula:

sorted_list = Global.lookupOne().sorted_list
index = sorted_list.index(rec)
prev_record = sorted_list[index - 1] if index > 0 else None
return prev_record

Going through each of the lines above:

  1. Grab the sorted list we created in the Global table, and save it in the sorted_list variable.
  2. Using sorted_list, find the “index” (i.e. position) of each record/row and save it in the index variable.
  3. Given that we want to grab the previous record, look inside sorted_list again but this time access the (previous) record at position index - 1, and save the record in the prev_record variable. If we’re on the first record already (such that index - 1 would be negative), we instead store None in prev_record.
  4. Finally, return the entire record.

Instead of returning the record, you are free to do whatever you like with it instead, such as accessing columns/fields on it by doing prev_record.End_Date. The first 3 lines above can be used in any formula column to access the previous record – the example above was done in a new column for demonstration only.

One final note: the names “Global” or “sorted_list” have no special meaning. You can use any names you want instead for the table/column as long as any mentions in the 2 formulas are also updated.

Let me know if you have any questions. Hope this helps!

Hi George,

Thanks for the detailed response!

I created the Global table and pasted in your formula, replacing YOUR_TABLE_NAME with Days, which is the name of my other table. It accepted the formula without any errors, but it didn’t display anything in the cell.

Then I created a new column in the Days table and pasted in your second formula. Here’s a screenshot of the error it displayed:

Image 1

Formula in the screenshot looks good to me. It looks like it’s having trouble finding the sorted_list column in the Global table (which makes sense since the formula cell appeared blank in the Global table).

Here’s a screenshot of what I put for the first formula:
Screen Shot 2021-09-08 at 9.19.16 AM

I’m not sure why the Global cell is blank but I’m looking into some possibilities.

Yeah, that’s the same as the formula I entered.

What does the cell look like if you try this formula instead?

from sortedcontainers import SortedList

This just returns a list of all End_Date entries from Days. Trying to narrow down if the problem is with getting the column’s data or with sorting it. Thanks.

After entering your new formula, the cell is still empty.

Here’s a link to a document I set up that has a Days table with a Previous_Record column that uses the formula: Grist

One other thing we can look at is the Code View page, which is accessible in the left pane under the Tools section. On this page, each of your tables is listed under @grist.UserTable, and each one reports details about the formulas and columns for each table.

For example, here’s what it looks like on the document I shared:

Are you able to share a screenshot (or just the text) of what this page shows for the Days and Global table in your document (should be under class Days and class Global? Thanks.

I renamed End_Date to Exit_Date, but everything else is the same:

import grist
from functions import *       # global uppercase functions
import datetime, math, re     # modules commonly needed in formulas

class Days:
  Enter_Date = grist.Date()
  Exit_Date = grist.Date()

  def Days_in_PR(rec, table):
    return DATEDIF(rec.Enter_Date, rec.Exit_Date or TODAY(), "D")

  def Remaining_Travel_Days(rec, table):
    return DATEDIF(IF(rec.Exit_Date == "", lambda: (TODAY()), lambda: (rec.Exit_Date)), DATE(2021, 12, 31), "D") - 183 + rec.Total_Days_in_PR

  def A(rec, table):
    sorted_list = Global.lookupOne().sorted_list
    index = sorted_list.index(rec)
    prev_record = sorted_list[index - 1] if index > 0 else None
    return prev_record

  def Total_Days_in_PR(rec, table):
    return 0

class Global:

  def B(rec, table):
    return None

  def C(rec, table):
    return None

  def sorted_list(rec, table):
    from sortedcontainers import SortedList
    return Days.all.Exit_Date

Thank you. I’m still not sure what’s going on, but I made a new document with the same columns/tables as yours and it seems to be working ok.

You can make edits to it which will make a private copy, and you can save that copy to your documents by clicking the Save Copy button at the top.

Hey Brad, one other thing that’s worth checking is whether you have at least one row in the Global table. It’s possible to set a formula on a column with no rows/records (except for the placeholder row which is highlighted in a different color). In that case, the formula doesn’t actually evaluate since there are now rows to store its results in.

Here’s how it looks on my end. If you only have the placeholder row, you can insert a new row by clicking on the row number to open a menu, and then clicking Insert Row Below.

Yes, that was the problem with the sorted_list column. After adding a record, it populated the first row like in your screenshot.

Now, in the Days table, I created a column “prev” and entered the original formula you sent. The value for the first row is empty, which I guess is correct, since there’s no previous row. The value for the second row shows Days[1], which I assume is a reference to the first row. However, the value for the third row displays the following error:

Image 2

Hi Brad.

Yeah, Days[1] is just a reference to the record at row 1.

Can you try forcing the formula in Global to re-evaluate by making an edit to it and saving (any change is fine), and then editing and saving back to the way it was. It looks like there might be some hiccups with this approach, but doing the above fixed the issue for me.

That didn’t help.

Also, I get errors when I try to use the prev column in a formula. When I try to reference it by the prev_record global variable, I get this:

Image 4

I also tried to reference it by the column name and got a different error, which I can’t post in this reply, due to it only allowing me to embed one image.

I truly appreciate all the effort you’ve put into this, but I’m thinking maybe we should just table this for now, as the approach seems buggy and an awkward workaround for something that should really be a native function. I’m not in any big rush to get this done, so if you’re planning to create a function for this, I can just wait for that.

On the other hand, if you want to keep going, as a means of identifying possible bugs within Grist, I’m willing to continue testing it with you. I just don’t want you to continue investing time in this unnecessarily.

Here’s the second error message:

Image 3

The formula should be something like:

sorted_list = Global.lookupOne().sorted_list
index = sorted_list.index(rec)
prev_record = sorted_list[index - 1] if index > 0 else None

if prev_record:
  return $Days_in_PR + prev_record.Total_Days_in_PR
  return $Days_in_PR

The prev_record variable unfortunately isn’t global – it’s only visible to the formula you are editing. To access it in other formulas, you can paste the first 3 lines of the above formula to grab it again. The conditional at the end is necessary for the case where there is no prev_record (the first travel record), in which case we just return the number of days by itself.

Sorry about all the trouble trying to get the formula to work. We’re definitely planning on adding native previous/next functions so hopefully it won’t be long until we have a solution for your use case.

Yeah, like I said, I appreciate your effort, but the solution has become too complex to justify. I’ll just wait for a native function. Thanks again!

1 Like

Hi there! I’ve struggled with this same thing for a while and my view is:

  1. We need to mark criteria(s) by which to identify $Previous. It seems that Grist doesn’t see rows/records sequence in a way we see before our very eyes, so we are to tell what is “previous”.
    The most reasonable criteria i think of (currently, but i believe it could be other field like date…) is $id.
    So, in my case i first created another column to numerate rows/recs which are dynamically filtered, yet (obviously) their ids not always consistent or sequential

Numeration column (in the same table):

filtered = MyTable.lookupRecords(Field=$Field).id
ids = sorted(filtered)
inc = 0
for i in range(len(ids)):
  if ids[i] == $id:
    inc += i+1

return inc

Attempt to find previous (based on rec.ids order):

prev = 0
for i in range(len(ids)):
  if i > 0:
    if ids[i] ==
      prev = (ids[i - 1])

return prev

Then, pin that row by $id:


the above worked for me ))

Sorry, must admit this (above) is not a bulletproof solution, just came across another more elegant one below:
Live & learn :))