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.
- 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.
- 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.)
- 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.)
