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:
- Grab the sorted list we created in the Global table, and save it in the
sorted_list variable.
- Using
sorted_list, find the “index” (i.e. position) of each record/row and save it in the index variable.
- 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.
- 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!
