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.
-
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. -
Create a new „Link to other records column“, select the same table and give it the name „Previous row“
-
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 -
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
-
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.
