Skip to main content

Formula for a balance tracker / running balance

  • September 3, 2021
  • 3 replies
  • 81 views
A

Hey AirTable brains trust,

I’m trying to build a balance tracker in AirTable so that I can not only set up a budget, but also forecast what future account balances might be.

I feel like I’m SO CLOSE. But I’ve found 2 flaws:

  1. the formula only seems to work if there’s an entry for the day before it. If there are no transactions for a few days, then the formula breaks somehow.

  2. the formula can’t cope if there are two transactions on the same day.

I suspect the problem is found in the DateAmt column / formula (but that’s mostly an educated guess).

I’ve straight copied the formula from the base that I’ve modelled it off (created by @W_Vann_Hall, of course). Here’s the base:

And here’s the formula:

(VALUE(DATETIME_FORMAT(Date,'X'))/86400)&
":"&
Amount&
IF(
    FIND(".",Amount&"")=0,
    ".00",
    REPT("0",7-LEN(Amount&""))
    )

According to the column description, the original formula (which I’ve modelled this off) “Concatenates 5-digit ‘days since epoch’ value of Date + “:” + Price to create day-specific persistent price record. Adds decimal and trailing zeroes if needed to ensure price portion of string is 7 characters long.”

But I have no idea what that means or how to change it, so that it works the way I want.

Here’s a link to my attempt so you can see the shambles for yourself:

I’ve based this on what I’ve learned (with my admittedly limited understanding) from @W_Vann_Hall’s responses to these 3 posts, which are based on similar concepts:

  1. Multi-record calculations (c.f. ‘running balance,’ ‘row number,’ and the like) (a lot of the explanation in this thread was so technical that it went over my head, which could be the problem!)

  2. Formula for percentage increase

  3. Help with Formula for price of gold

Any ideas of where I’ve gone wrong or how to fix it would be DEEPLY appreciated! It feels like there’s something fundamental that I’m missing here.

3 replies

D
Forum|alt.badge.img+17

It’s getting a bit late on my end of the world, so apologies in advance if I glanced over this piece of info, but is there a particular reason why you’d want to get this done with formulae?

Because it’s stretching the limits of what that feature is meant to accomplish. Not to say it cannot be done, just that it probably shouldn’t.

Because even if you manage to get something that works, that solution is basically guaranteed to be entirely inflexible and impossible to maintain (and someone who got to the point you did without touching the Scripting app will never be truly satisfied with their software, I can promise you that haha).

So, maybe it’s time to graduate to the Scripting app? Especially since it just got another six-month extension on its free period? Not to mention there’s a decent chance you’ll never have to pay to use it. Some1 of the most veteran Airtable users are so certain this won’t happen that they have even started shamelessly gambling with food, right here on the official boards. :laughing:

Doing this still won’t be a walk in the park, especially for a newcomer to JS, but it’s not going to be more time-intensive than what it took you to get to this point. And you’ll be left with not just a cool applet that you can tweak more easily, but also with crucial skills that are likely to forever change the way you work and think (for better or worse).

1 “Some” === at least one. :joy:

A
  • Alyssa_Martin
  • Author
  • Participating Frequently
  • September 6, 2021

It’s getting a bit late on my end of the world, so apologies in advance if I glanced over this piece of info, but is there a particular reason why you’d want to get this done with formulae?

Because it’s stretching the limits of what that feature is meant to accomplish. Not to say it cannot be done, just that it probably shouldn’t.

Because even if you manage to get something that works, that solution is basically guaranteed to be entirely inflexible and impossible to maintain (and someone who got to the point you did without touching the Scripting app will never be truly satisfied with their software, I can promise you that haha).

So, maybe it’s time to graduate to the Scripting app? Especially since it just got another six-month extension on its free period? Not to mention there’s a decent chance you’ll never have to pay to use it. Some1 of the most veteran Airtable users are so certain this won’t happen that they have even started shamelessly gambling with food, right here on the official boards. :laughing:

Doing this still won’t be a walk in the park, especially for a newcomer to JS, but it’s not going to be more time-intensive than what it took you to get to this point. And you’ll be left with not just a cool applet that you can tweak more easily, but also with crucial skills that are likely to forever change the way you work and think (for better or worse).

1 “Some” === at least one. :joy:


I’m trying to do it with formulae because that’s the only way I’ve thought of to do it. What makes it:

???

This is the first I’ve heard of the Scripting app, but if all it takes to get the functionality I want is to upgrade to the paid plan, then I’m into it. But then, I guess I’d have to learn JS and that doesn’t excite me :frowning:

D
Forum|alt.badge.img+17

I’m trying to do it with formulae because that’s the only way I’ve thought of to do it. What makes it:

???

This is the first I’ve heard of the Scripting app, but if all it takes to get the functionality I want is to upgrade to the paid plan, then I’m into it. But then, I guess I’d have to learn JS and that doesn’t excite me :frowning:


Well, Airtable already tricked you into learning JavaScript when you first started using formulas in your tables :slightly_smiling_face: . But I think my first impression wasn’t quite accurate (regarding how close you might be to having something that works), so let’s see if I can help you decipher this formula you posted:

VALUE(DATETIME_FORMAT(Date,'X'))/86400)
This part returns the number of years between the Date in your ‘Date’ field and January 1st, 1970. Whether the output is a decimal number or gets rounded up to an integer would depend on your formula field settings.

& ":"
This turns the above result back to a string along the lines of “51:” (which renders without the quotation marks inside your base cells but cannot be manipulated like a number without another call to VALUE).

& Amount

This adds the value from your ‘Amount’ field to the above string.

& FIND(".",Amount & "")
The ‘& “”’ at the end turns the value in the Amount cell from a number to a string , which can then be searched by character. Guess what comes next? Searching the result by character!

More specifically, it finds the first index number of the “.” character inside a given ‘Amount’ cell.
If none are found, this will return 0.

IF( FIND(".",Amount&"")=0, ".00",

The first part of this IF formula wrapper deals with cases that do return a zero. By adding “.00” to the result where no decimal points have been found.

REPT("0",7-LEN(Amount&""))
As for those with decimal points, the second part of this IF formula adds as many zeroes to the end of your Amount field cell as are needed for the final output to be 7 characters long.

Can you see where your cod… formula is failing?

    Terms & ConditionsAccessibility statement