Need help making a formula to keep a running balance

[Steve_Madar]

Thanks!

[Kamille_Parks]

Airtable formulas only have access to data stored in fields on the current record. There is no concept of “the record above/below this one”. If you want to use information originating from another record the standard way would be to link the records together.

If you add a Link-type field for {Previous Transaction} you can add a Lookup-type field for the {Remaining Balance} field of the previous transaction. Then you could convert the {Remaining Balance} field into a formula that adds (or subtracts) the {Balance} field to the {Balance Lookup} field.

You could also do this with scripting but I would recommend linking records together. To quickly link all your records together, assuming all records have a unique primary field value and are sorted sequentially, you could copy the entire first column and paste it into the Link field offset by one row.

[kuovonne]

The problem with trying to create a running balance with linked record fields is that you will end up with a circular reference, which Airtable does not allow. There are ways to create a gap in the circular logic, and then have something that bridges that gap, but if you use automations to do this, you can quickly us up a lot of automation runs.

I recommend looking into why you really need a running balance, and then if a running balance is absolutely necessary, use scripting.

[Kamille_Parks]

That’s true, this method runs into a circular reference (I forgot about it because logically it isn’t a circular reference, but is one under-the-hood).

As mentioned an automation could copy the {Previous Remaining Balance} lookup into a plain currency field and that will resolve the formula’s error but the number of automation runs to sustain this setup would add up.

Then there’s following non scripting, non automation methods as covered in these threads
Simple-ish:

Subtracting last two created linked record Ask the community

[34] I am keeping track of an inventory of dogs in foster homes. We try to weigh these dogs almost every week. The weigh-ins are recorded in a separate table. That table has weight , dog name and weigh in date. I want to be able to automatically subtracts the last two weigh in to know if the dog lost or gained weight. I am assuming it could be done through a roll up column. If so how do I select the only the last two weigh ins Thank You

OG Solution:

Multi-record calculations (c.f. 'running balance,' 'row number,' and the like) Show and tell

tl;dr version: A frequent query and/or complaint concerns users who wish to access data from a record for use in a calculation performed in a different record from the same table. Often this takes the form of a user asking ‘how to retrieve a value based on row number’; other times the user wishes to perform a calculation that assumes knowledge of the previous record — for instance, maintaining a running balance. A recently published base contains four approaches to this problem, three involving …

Another option requiring basically doubling the size of your base, indefinitely

How to create a running total or balance Apps

Hey, I got the same question from my client, so I created a video tutorial: How to show a Running total on Airtable? (6:40 min) Here is the link to the base used in the tutorial. Hope it helps! Noam [Thumbnail-Running-Total]

As you can see from those threads getting a running total is harder than it sounds.

kuovonne:

I recommend looking into why you really need a running balance

I would agree. You currently have employees as a single select field. Would you benefit from having a table for employees and simply seeing the total balance for each employee as opposed to a running total on every transaction?

[kuovonne]

Kamille_Parks:

As you can see from those threads getting a running total is harder than it sounds.

@Steve_Madar Notice that all of those solutions were created before Scripting existed. In fact, one of the example scripts in the scripting documentation is actually for calculating a running total. It is a simple enough script that you may be able to get it to work by simply changing the table, view, and field names to match your base. However, this script takes a long time to run when you have a significant number of records, and it is not suitable for running as an automation.

Also none of the formula/rollup solutions scale well to large bases. Having rollup fields with many linked rcords will slow down a base. And some of the solutions have limits on how the data is structured.