Tutorial: Getting values from previous row with the help of automation rules

To get the value from a previous row is most of the time only possible by using javascript or python.

Here is one option to connect two or more rows via linked column and automation rules.

  1. Create a table with a date column.
    For example, if you have monthly records you will have entries like 2023-01, 2023-02, 2023-03 and so on.

  2. Create a new „Link to other records column“, select the same table and give it the name „Previous row“

  3. Now you have to create two other formula rows for comparison. The first row creates a string with the year and month of the current date and the other row contains the string of the previous month.
    First column:
    Name: Current month
    Formula: 'Y' & text(year({Date})) & 'M' & text(month({Date})) << will create Y2023M1
    Second column:
    Name: Previous month
    Formula: 'Y' & text(year({Date}) - if(month({Date}) = 1, 1, 0)) & 'M' & text(if(month({Date}) = 1, 12, month({Date}) - 1)) << will create Y2022M12

  4. Now you can create an automation rule that can compare the values and find the related previous month link
    Event: Records meet specific conditions after modification
    Automated action:
    Add links
    Select link column in the table: Previous row
    if column: Previous month
    column: Current month
    Save the rule

  5. Every new row after selecting a date in the Date column will search for the previous month row.
    And you can create Link formula columns to get previous values.