Help

Subtracting in field - Record from Record

Topic Labels: Formulas
8313 25
cancel
Showing results for 
Search instead for 
Did you mean: 
Outside_Operato
5 - Automation Enthusiast
5 - Automation Enthusiast

Good Day,

Looking to subtract a running total collected daily from the previous total to give me the value used since the last entry.

Better explanation (I hope)
Jan 1 my total is: 1111
Jan 2 my total is 1112, so Jan 2 total - Jan 1 total = 1
Jan 3 my total is 1115, so Jan 3 - Jan 2 total = 3

The running total is kept in the same Column/Field and I want the next Column/Field to auto-calculate the difference from day to day.

In excel: Cell B2 I would use =A2-A1
Cell B3 would be =A3-A2
and so on and so on forever since the total never stop rising.

Clear as mud i hope. I’m likely just overthinking it, and not seeing the whole picture. Airtable is new to me, but if I can get this to work, it will be perfect for our needs.

Thanks,
Marc

25 Replies 25

My pleasure.

Best of luck with finding a solution! :slightly_smiling_face:

EastEndTom
6 - Interface Innovator
6 - Interface Innovator

Hi Tuur, I too am having difficulties implementing something similar for a rolling estimated bank balance / cashflow scenario. Would you be able to send me your base too, please, so I could take a look at the formulas and links?

Many thanks,
Tom.

This post and the example base it references demonstrate how one can perform cross-record calculations; one of the examples it implements is a rolling balance. The routines provided support balances calculated daily, every business day, or at an arbitrary interval. They also handle the December 31/January 1 rollover seamlessly.

Two caveats: First, as with any cross-record algorithm I’ve come up with, this approach requires every record in your primary table to be linked to a single record in a calculation table. This is easy to add to an existing table and adds only two mouse clicks to the effort to add a new record. Second, because of the prohibition against circular references, there’s a three-keystroke, manual copy/paste action that has to be performed for each record.

Thank you, I’ve read your post and it certainly is helpful. It was useful to look at the formulas and relationships, too, thanks for sharing.

But I’m trying really hard to remove the copy / paste necessary here. Each monthly projected total in my Base is calculated from many changing factors behind it, (when an invoice is issued, is paid late , new business opportunities, tax calculations, cost of sales etc), and I’d like a cumulative estimated total to dynamically adjust as figures around it adjust, too.

I’ve built a complicated Base and it is close to being incredibly useful. But this circular reference error is very frustrating.

I already use Zapier, and thought that this could be a solution here, but Airtable currently only triggers Zapier on new records, not when a record value changes. So I think I’ve hit a brick wall unless you have any other ideas, please?

Thanks,
Tom.

Well, the copy/paste is only required when a record is added, so conceivably it could be handled through Zapier or Integromat. Since you’d be updating the same record that triggered the Zap, it’s considered a two-step Zap, which means it can be run from a free Zapier account. Should you decide to go that route, in the [Documentation] table in my Wardrobe Manager base published in Airtable Universe, there’s a document called something like Wardrobe Manager Zapier Users Guide that provides step-by-step instructions in configuring a Zap that, when triggered by a new record, copies the value of one field and pastes it into another.

Now, admittedly, if you are looking to keep a true running balance — that is, adjusting the balance after each transaction, rather than daily — you could be talking about a substantial number of records needing to be updated. This would almost certainly push you over Zapier’s 100-task monthly limit.

While Integromat’s free account offers a larger number of actions per month, the larger problem is that Airtable doesn’t currently support webhooks, which means it actually doesn’t ‘trigger’ a middleware provider when a new record appears; instead, an integration service has to poll Airtable to see if any new records have been created since it last checked. Both Zapier and Integromat poll only once every 15 minutes for their free accounts, which means your real-time running balance won’t be all that real-time. (Zapier offers paid accounts with a 5-minute polling interval, Integromat with 5- and 1-minute intervals. I had thought Integromat, at least, had a 5-record limit per polling cycle, but I just changed ‘maximum records’ on an Integromat scenario [comparable to a Zap] from 5 to 15 and it didn’t complain, so I’m either mistaken or living in the past.)

If you don’t need the monthly projection to be calculated more often than daily, but you want to record every factor influencing it, you could track those factors on per-transaction records and, at the end of the day, roll up the day’s transactions and calculate the running balance accordingly. (Each transaction record would be linked to a balance record for that day —which conceivably could be an automated step, as well.) You’d still have to perform the copy/paste, either manually or through automation, but only once daily.

This last scenario opens the door to an interesting possibility: A little after midnight, a series of Zaps or scenarios could retrieve every transaction record created the previous day¹ and link them to a daily running balance record.² Once that was done, another Zap or scenario could link the newly created balance record to the calculation table and then update it with the circular reference-avoiding copy/paste. That would allow transactions to be logged and monitored (using standard Airtable views) in real time and eliminate the need to create the transaction-to-balance or balance-to-calculation record links manually, at the cost of only recalculating the balance daily.³

I should note there are a number of suggested solutions to your problem outlined on Airtable Community in earlier posts that use only middleware, avoiding all of the linked-record rigmarole needed for cross-record calculations. My concern — potentially unfounded, but based in the belief that if anything can go wrong, it will — is that any solution that depends upon a seamless meshing of Airtable and middleware risks data corruption. (For instance, if an implementation keeps track of the most recent balance and updates each new record accordingly, what happens if for some reason a record isn’t updated? How does one recover? Similarly, what if it’s discovered that a transaction amount 10, 20, 50 transactions ago was fumble-fingered? If the running balance is calculated afresh each transaction based on a ‘hard-coded’ previous balance inserted into the record by middleware, how does one gracefully back out of that problem? Even more critically, what if the amount of the current balance may itself trigger transactions — transferring funds from another account, perhaps?)

Accordingly, my preference is to rely as much as possible on core Airtable functionality, being as they’re the ones with skin in the game, and I know where they live. (Well, where they work, at least.) I look to middleware to simplify the process and eliminate potentials for error — a user forgetting to create a link to another record, for instance, or linking to the wrong record. If there’s a disconnect between Airtable and the middleware service, no data should be lost or corrupted, and it should be possible to fall back to a fully manual procedure with minimal fuss.


  1. Either by searching for the actual date or (my preference) by polling for records newly added to a view, the view in question being one where the created date is equal to yesterday.
  2. At least, I assume this could be done; I’ve not yet tested the ability of Zapier, Integromat, or IFTTT to insert a value into a linked-record field. (A quick test with Integromat suggests it can, as does a quick scan of earlier community messages.)
  3. Just spit-balling here, but another possible approach would be to have Zapier, et al., create a new transaction record each morning with a transaction type of ‘current balance’. This would be the first record of the day, so that during the day, an Airtable view filtered for that day’s transactions would display a real-time running balance in the summary bar (and, optionally, in a Summary Block). After midnight, [Your Choice of Middleware Here] would create the links, perform the copy/paste, and create a new ‘current balance’ transaction record. (For why I would continue to use cross-record calculations and middleware, see the last two paragraphs of my post.)

Thank you for the detailed answers and thoughts, that’s all really helpful and useful. I’m going to pursue some Zapier routes here to avoid the dreaded circular reference. I have a Professional Plus plan on Zapier due to another workflow we use needing a shed load of Zaps, there are options here. And your Wardrobe Manger documentation will be useful to help experiment.

The plan is to focus on some sort of daily synchronise, as you suggest, to update the balance with copy paste. Hopefully it won’t prove too complex in Zapier…!

Thanks again,
Tom.