Subtracting in field - Record from Record

You could probably create something like that, but you’ll need two tables at least. Maybe some ‘master’ date table (one record for each day) and a table with the log records (changes + date reference).

Then you’ll be able to play with rollup, sum, lookup, grouping, etc. between both tables.

Thanks Tuur,

I have attempted what you suggested but I am still having problems. Would it be possible for you to provide a brief example that I could build from?

Thanks

Can you share a view (in a private message)?

Here is the link to the view:

I hope that it is self explanatory. I am simply trying to calculate the number of “Counts Per Day” by subtracting yesterday’s “Counter” value from today’s.

I really appreciate the help

You have a private message. :slightly_smiling_face:

I am looking for a solution similar to what I read in this thread. I use a scenario a lot, and a spreadsheet did the job. But I would prefer using AirTable. In a scenario records refer to each other. The first item will take 5 minutes, the next one 15 and so on. The table should accumulate the minutes in each record.
I wonder if anyone has a solution for this application. Would be awesome!
I tried building something with 2 tables but I keep ending up in circular reference errors.

You should be able to build it with only this table and a reference to the former record.

So the second record has a field that links to the first one, the 3rd record links to the 2nd, etc.

I suppose you refer to an earlier message. There’s the message with a reference to a view, but I don’t find the database with the example. Do you have more information for me?
Thanks!

I was referring to your screenshot. Turn that into a table and add a linked field to itself. Link to the former record and you should be able to take it from there by adding a look up field to the ending time and doing calculations for example.

Does this help?

@Tuur
I spent some time trying to apply your suggestions. I do get stuck every time, because of this:
There are only 2 items that are entered manually:

1. The starting time (“00”), and
2. the duration.

Everything else is calculated. And that causes circular references every way I try. Maybe you can go a little deeper in finding a solution? I just don’t find the proper solution.

Regards.

Turns out I was wrong about linking back in one table. I thought I’d done that in the past… :thinking:

Anyway, you can do it with two tables. It’s not pretty, but it works. If you want to copy the base just let me know. :slightly_smiling_face:

Thank you @Tuur!! This is what I’ve been looking for.
I would like to have the base, because of the formula’s and the link between tables.

Regards
André

PM incoming in 5, 4, 3, 2, 1…

whats the private message for ??! i am trying to do the same. can you share your solution

@Tuur why PM :slightly_smiling_face: could you tell us your solution

The solution is already posted. The PM was only for sharing the base.

But I suspect you’d like a link too?

@Tuur
yes please send me the link to base

Sure.

You’ve got a PM.

@Tuur thanks for sharing … i d like to have a good feedback on this, but it is really hard to imagine how we can implement a similar solution in a good scale and production.its un-managable
i really liked airtable but i am constantly hitting walls with limitations of the design.

thanks

My pleasure.

Best of luck with finding a solution! :slightly_smiling_face:

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.