Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 20, 2024 05:04 PM
Hello, I am a user who studies airtable. I would like to inquire about the function of using airtable.
In Excel, a fomula can be calculated by referring to previous row (previous record) values.
So it's easy to implement how to get the following balance, but I haven' t found a way yet in airtable.
No | content | income | spend | balance |
1 | apple | 10 | 10 | |
2 | banana | 20 | 30 (=10+20-0) | |
3 | cherry | 15 | 15 (=30+0-15) | |
4 | hotel | 5 | 10 (=15+0-5) | |
5 | fox | 10 | 0 (=10+0-10) |
I would like to add or subtract this record value to the previous record value so that the cumulative balance comes out.
I asked the same question a few days ago, but I didn't receive the answer I wanted, so I'm requesting it again.
Thanks. Have a nice day.
May 21, 2024 04:44 AM
Hi,
In Airtable, creating a running balance like in Excel is not straightforward because you can’t refer to the previous row directly. However, you can use linked records to connect each record to the previous one and then use a rollup field to calculate the running total. This requires setting up a more complex base structure but achieves the desired outcome.
May 21, 2024 07:41 AM
As @BrandyN11 mentioned, the concept of a "previous" record does not exist in a database.
You can accomplish this with linked records by linking each transaction to all the transactions that came before it (based on a date/time or in your case, a sequential transaction number).
I'm going to be demonstrating this live on the BuiltOnAir podcast in ~20 minutes - you can watch here (and I will update this thread with a link to the episode later 😀).
May 22, 2024 04:36 AM
Just checked and the link I posted yesterday does still work! You can see the demo at around 51 minutes in (but the whole episode is worth watching) 😊 hope it’s helpful!
May 22, 2024 04:32 PM
Hello, I watched the video. Thank you very much for the detailed explanation. I haven't used the automations feature yet, so I haven't tried it yet.
I showed you a sample of five rows, but the actual data is hundreds of rows and the top and bottom rows can be changed or inserted from time to time, so I wonder if this data will work the same way. I will reproduce it with what you guided me and ask you if I have any questions.
Thank you. Have a nice day.😊