How to get a value from the previous row?

I’m trying to create a formula that calculates a running total by adding the previous value from the same column to a value from a different column in the same row. I emailed support, and they said I could use the Table.lookupOne() function to look up the value from the previous row. However, I’m not clear on how to specify the previous row.

This is a database to track travel days. Each row has a Start Date column and an End Date column. It has a “Days” column, which is calculated by DATEDIF($Start_Date, $End_Date, "D"). So to identify the prior row, I guess I need to run a query that finds the row containing the Max End Date that’s less than the current Start Date. I have no idea how to create such a query, or if it’s even possible using the lookupOne() function.

Another related question:

In the Days column, I need to account for trips that are ongoing and have no End Date. Therefore, I tried using the following formula to count the days:

IF($End_Date == "", DATEDIF($Start_Date, TODAY(), "D"), DATEDIF($Start_Date, $End_Date, "D"))

The idea is to use today’s date, when the cell is empty. However, it doesn’t work. It displays the following error in the rows that have no End Date:

TypeError: unsupported operand type(s) for -: ‘NoneType’ and "datetime type’

For your second question, a blank date is seen by formulas as the python object None, not an empty string. So this should work:

IF($End_Date is None, ...)

Little Python trick, you can simplify your whole formula to this:

DATEDIF($Start_Date, $End_Date or TODAY(), "D")

Thanks, Alex. I used your simplified formula and that worked well. Any ideas about how to select the prior row?

Also, I thought I saw something in the documentation about a multi-line formula editor, but I can’t figure out how to switch it to multi-line mode. It’s really hard to work with long formulas in a single line.

Hi Brad. Regarding multi-line mode, you can type Shift + Enter/Return to manually enter new lines.

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
Days.all.End_Date

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


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

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

  @grist.formulaType(grist.Int())
  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

  @grist.formulaType(grist.Int())
  def Total_Days_in_PR(rec, table):
    return 0


@grist.UserTable
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.

https://docs.getgrist.com/prnCnWvMxuSV/Untitled-document/m/fork

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
else:
  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.