Skip to main content

I know there has to be a solution for this, but I just haven't found it - trying myself.

Pretty Simple I'm trying to make a simple purchase/expense tracking table for the different program team since we're an NPO and have to account for what is spent in the programs as part of the audits, especially with grants.  Each program has a specific budget amount.

In excel this is a piece of cake.

Fields are that I need to be able 

Deposit  (Field type Number: Currency)

Expense (Field type Number: Currency)

Row Charge (Formula)  [Deposit - {Expense Charge}]

I now have two fields  that are formulas that I just can't figure out how to make work

End Balance [{Current Balance} + {Row charge}]

Row 1 has End Balance and I want to bring that information into a field called Current Balance which starting Row 2 would auto populate what ever was in previous row field End Balance 

Currently I am having to manually type in to the next row End Balance information into Current Balance .

In Excel it is easy with a reference - but how to you do it in AirTable?

The safest way to do this would be with a script that calculates this line by line I reckon

If the data entry is pretty slow you could attempt to create a non-script automation for this?  It'd involve creating a new table and linking all your data records to a single record in it called 'Summary' or something.  You would need to create a rollup field to display the Current Balance, and this rollup field would be on the 'End Balance' field from the data record

You'd then have an automation that would trigger whenever a new data record gets created and paste the value from the rollup field into the 'Current Balance' field of the data record, as well as link it to the 'Summary' record

This should work, but if you ever key in data faster than the automation can run then the math will be wrong I'm afraid

 

Hello @RonniEloff_VKDe 

The challenge you're experiencing is common when transitioning from Excel to Airtable. For your specific use case of tracking running balances, I recommend two primary approaches:

Approach 1: Linked Records and Rollup Fields:

Create a "Transactions" table with these fields:

  1. Date
  2. Deposit
  3. Expense
  4. Program (linked record)

Use a Rollup field to calculate the running balance:

Create a "Running Balance" field
Use a rollup that sums previous deposits and subtracts expenses
This automatically tracks your balance across rows without manual entry

Approach 2: Script Extension (for more complex tracking)

If you need more precise control, you can use an Airtable script extension to:

  1. Automatically calculate running balances
  2. Populate "Current Balance" based on the previous row's "End Balance"
  3. Ensure accurate tracking across multiple programs

Recommendation:
Start with the Rollup field approach. If that doesn't meet your needs, consider a script extension or consulting an Airtable expert who can customize a solution for your nonprofit's specific requirements.


👍

Reply


Terms & Conditions