Help

I would like to find the cumulative balance by referring to the previous record value.

Topic Labels: Formulas
1517 4
cancel
Showing results for 
Search instead for 
Did you mean: 
taehui
4 - Data Explorer
4 - Data Explorer

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.

Nocontentincomespendbalance
1apple10 10 
2banana20 30 (=10+20-0)
3cherry 1515  (=30+0-15)
4hotel 510  (=15+0-5)
5fox 100  (=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.

4 Replies 4
BrandyN11
4 - Data Explorer
4 - Data Explorer

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.

Best regard,
MyDestinyCard
AlliAlosa
10 - Mercury
10 - Mercury

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 😀).

AlliAlosa
10 - Mercury
10 - Mercury

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!

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.😊