Referencing the previous record/row's value

[prologic]

There needs to be a function or way to reference a record's previous value in the previous row, optionally provide a default or initial value.

Going through the community forum(s) there's a few "solutions" but are honestly a lot of effort for something that in typical spreadsheet-like apps is pretty trivial since you can just reference the first row (with manually inserted data).

[BillMarklyn]

Been a year, but maybe still relevant... Does it need to be a function? What about a .prev member of the Record class (so, rec.prev in a formula)? That could give access to the previous record(s) in the current record set (i.e. in the current widget) using for all formulas, including triggers and conditional formatting, and would be consistent across sorting and filtering.

[fflorent]

I feel like it's complicated by design, not especially due to technical limitation: the notion of precedence here is linked to sorting.

From a user prospective I don't know whether it's intuitive to have record values updated when sorting is changed.

[BillMarklyn]

True, I can see cases for using the visible values (i.e. in the current filtered view), or the raw data. Even something simple like cumulative value can apply in both cases. Maybe a .prevVisible and a .prevRaw so the programmer can decide? I'm sure there's more to it, just some ideas.

[dsagal]

Let me share a proposed design.

Firstly, keep in mind that @fflorent is right: the notion of precedence is linked to sorting, but sorting is a property of a view, while formulas work on the data level. There may be different views showing different subsets of data in different order. Access rules may limit visible records too. But formulas are only aware of the underlying data, not who is looking at it, or in which view.

The proposal is to add the following functions:

• PREVIOUS(rec) (with no other arguments) finds the previous record in the full table, sorted by the internal column used to rearrange records manually (manualSort column). It would match the order you'd see when a table is shown without sorting by any column. (Note that rec in Grist formulas refers to the current record.)
• PREVIOUS(rec, group_by=..., sort_by=...) finds the previous record to rec, according to group_by / sort_by. For example, PREVIOUS(rec, group_by="Account", sort_by="Date")
• NEXT(...) is just like PREVIOUS(...) but finds the next record.
• RANK(rec, group_by=..., sort_by=..., order="asc") returns the rank of the record within the group, starting with 1. Order can be "asc" (default) or "desc".
• All the new functions work in amortized O(log N) time per cell (which is a big reason for this effort, since working with sorted data efficiently is very hard to achieve today).

The sort_by argument will support tuples, e.g. ("Category", "Date") and any column ID may be prefixed with - to reverse the order, e.g. ("Category", "-Date"). This allows matching more complex sort orders that can be set in the UI.

All the new functions are based on an enhancement of lookupRecords. When lookupRecords() is used with the sort_by parameter, it will now allow efficient search in sorted results, with the following syntax:

Table.lookupRecords(..., sort_by="Date").find.le($Date)

This will find the record with the nearest date that's <= $Date. The find.* methods are le, lt, ge, gt, and eq, all having O(log N) performance.

Here is an example to show the usefulness of the find.* methods. In the Payroll template, each person has a history of pay rates, in the Rates table. To find a rate applicable on a certain date (for the Per Hour column), here is how it is done today:

# Get all the rates for the Person and Role in this row.
rates = Rates.lookupRecords(Person=$Person, Role=$Role)

# Pick out only those rates whose Rate_Start is on or before this row's Date.
past_rates = [r for r in rates if r.Rate_Start <= $Date]

# Select the latest of past_rates, i.e. maximum by Rate_Start.
rate = max(past_rates, key=lambda r: r.Rate_Start)

# Return the Hourly_Rate from the relevant Rates record.
return rate.Hourly_Rate

With the new methods, it would be much simpler (and also much faster, when there are many rates for the same person):

rate = Rates.lookupRecords(Person=$Person, Role=$Role, sort_by="Rate_Start").find.le($Date)
return rate.Hourly_Rate

[BillMarklyn]

Thanks for the detailed explanation! I'm new to Grist and still learning the details - I didn't realize formulas only operated on underlying data and didn't have a way to use the current view. This all then makes more sense. I figured you'd all had time to think more deeply about it! That current view record set must exist somewhere? If so, it's too bad not to have access to it. But this sounds great, looking forward to future versions.

[fflorent]

@dsagal Looks like this issue is solved, doesn't it? :)

https://support.getgrist.com/newsletters/2024-07/#cumulative-functions-previous-next-and-rank

Thanks for that! :tada:

[dsagal]

Yes! To conclude, here is the new function that addresses the question: PREVIOUS.

To use it to get a value from the previous row with an optional default, you can do this:

previous_record = PREVIOUS(rec, order_by=None)
previous_value = previous_record.FieldOfInterest if previous_record else default_value_to_use

For a common example, like calculating a cumulative amount, it could be a one-line formula. E.g. in a column named Cumulative of type Numeric (type matters to ensure that the default value of this column is 0 rather than None):

PREVIOUS(rec, order_by=None).Cumulative + $Amount

As for matching the view, order_by and group_by can be used to match the view's sorting and filtering.

[yohanboniface]

Just discovered this today, and it's fantastic for trigger formulas! Thanks a lot :100: