How to auto-calculate one of the 2 fields - amount and balance - to update based on the value in another

I am new to glide and I am trying to create an expense tracker for my personal use, just to get off the ground with a real project.

I have the following columns:
date, amount,balance, category.

When I enter the amount, I want the balance to be updated by taking the previous balance (from the previous row). Sometimes, I just want to enter the balance and want an entry made for the amount.

Should I create balance as an explicit column in my table (and a typical excel formula to take the previous row value and subtract the amount to get the value for current row) , or should I use a calculated field (something like a running calculation in DB terms) - any guidance or pointers would be appreciated.

So do you have a Users table and an Expenses table, and want to show a live balance to the user?

I am sorry for the delay in responding - yes, i have these 2 tables and want to show a live balance to the user, ideally as he enters each transaction

Maybe a spreadsheet of the end result you’d like could help?

1 Like

Assuming you store the amounts in the same column (positive and negative, accordingly), it would just be a rollup on top of a relation to sum that column, but as Xavier said, we might have a better recommendation if you show us your data structure.

1 Like

Here is a screenshot from the google sheets I am using. When the user enters the amount, the balance should be displayed to him - here when he enters 200, he should see the closing balance as 19,300 by taking the opening balance of 19500 (from the previous entry) and deducting the 200.

Got it!
It’s probably a lot easier to do in google sheet this way than in Glide tables :wink:
I gave it a try, but I end up with a circular dependency

Not having to define the related balance for each individual record is a lot easier though.
The user may need to see the balance only when he adds a new expense.

Some ideas discussed here.

I’d probably write the previous balance when creating each row, then use a math column to figure out the new balance based on the credit or debit.

You could get that previous balance before creating a new row by using a Single Value column or a Rollup column. Either way you should have a balance value that you can pass through a form when you write a new row, and then the math column will give you the new balance.

2 Likes

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.