I’m used to work with spreadsheets where you can refer to a cell in a previous row.
And I know spreadsheets (data in rows) and database (data in records) can’t be compared.
But I would like to build a project where I can see the evolution of a certain value.
Here you have a simple table where the first column (datum = Dutch for date) is chronologically ordered and each value is unique.
In the third column, I entered the difference between the current status and the status of the “previous month” manually to show what I want.
Is there any possibility to get this done in Airtable? With formulas in another extra table?
Page 1 / 1
In Airtable, you can only refer to other rows by linking to them and then bringing in values from those other rows with lookup fields or rollup fields.
So, on your 2nd record, you can create a linked record field called “previous record” that links to the first record, and then create a lookup field called “previous record’s status” that brings in the number from that first record.
Then, you can create a formula that calculates the difference between the 2 fields in your current record (the number field and the lookup field).
On your 3rd record, you would use the same exact linked record field to link to the 2nd record… and you would repeat this process for each record.
The idea is to set up a linked field to the same table, then add a lookup to grab the linked record’s ‘Number’ value, then use a formula field to find the difference
The automation is set up with a ‘Find Record’ action to look for all records that have already been linked and grab the latest one (I’m going with the assumption that the record you’re adding has the latest date), and then link it. Can be a bit confusing, so I’d recommend duplicating the base into your own workspace and playing with it!
Wow…
I have to change the topic of my subject to “it is possible”
I’m new to Airtable, and I will try it on my own workspace.
VERY THANKFULL !!
I’m new to Airtable, and I will try it on my own workspace.
I recommend that you enrol in Airtable’s official course. It is an excellent resource that will provide you with valuable knowledge and skills.
Answers above are great. As mentioned/shown by both Adam and Scott you absolutely need to link each record to the previous/next record. Early last week this could have been challenging if you wanted to keep the sync of previous AND next records. However, backlinks were released a few days ago and this is now way easier (you can check out this other post on Backlinks here). Super happy about it, so wanted to share this here as well.
This could be achieved without the linkage with some additional workarounds (e.g. scripting on the automation, or additional helper fields on your table). However, I would strongly suggest to to with the links as that would allow you to keep your data dynamic (e.g. if you ever make changes to one of the records, your “difference” calculation would automatically be updated!)
