I am creating a new table, say for example with daily price of gold,
I have three fields,
Date, Price and Change in Price
I want the field “Change is Price” to be the difference in price since yesterday, to be calculated automatically.
In spreadsheet, I could do it as
=b2-b1
Is it possible to do this in Airtable, Please help.
Thanks in Advance.
Yes, it is possible — but not in the same manner as with a spreadsheet.
Your post, along with a few others recently, started me thinking about Airtable and spreadsheets. I’d often wondered if the spreadsheet metaphor might not be a double-edged sword for Airtable. Clearly, the familiar rows-and-columns interface makes it less threatening for newcomers to give it a try — but the application’s insistent refusal to behave like Excel undoubtedly turns some away.
But now, though, I’m starting to think, rather than the spreadsheet metaphor, the larger issue is the spreadsheet as metaphor. One of the things that makes spreadsheet software so easy to learn to use is that often relationships among cells can stand in for relationships among the things the cells represent.
For instance, in the example you gave, you mentioned a hypothetical spreadsheet in which cell B2 represented today’s closing price, and B1 represented yesterday’s. Now, the spreadsheet didn’t care which two cells you chose; they could just as easily have been A32 and YY917. For the user, though, there’s an implicit relationship between B1 and B2, one that speaks to precedence and sequence. (For instance, you could just as easily have used B1 for ‘today’ and B2 for ‘yesterday’ — but B1->B2 makes more sense visually, with B3 the logical choice for ‘tomorrow.’) Once the user has mastered how visual relationships map against those in the real world, it becomes almost second nature to use or enhance the spreadsheet. It is a genius bit of interface design — and it is nearly as hard to forget as it was easy to learn.
It’s important to keep in mind Airtable’s similarity to spreadsheets is almost entirely visual, rather than logical or functional. (The functional similarities that do exist are all at the UI/UX level – for instance, click-and-drag to duplicate records or values.) Logically, though, Airtable — as currently implemented — is all database. That Row 3 follows Row 2 and precedes Row 4 is merely chance; nothing can be inferred about the relationships among those three records.
It must be noted this is a strength as well as a weakness. True, one loses the ability to define relationships through visual position that spreadsheets offer; on the other hand, functionality and data integrity are no longer so immutably bound to screen layout. Who, when working with spreadsheets, has never risked losing hours of work with a poorly timed ‘sort’ or arrived at an invalid conclusion when formulas in newly added rows were either missing or contained incorrect references? With Airtable, on the other hand, while one must make explicit the relationships among records, the integrity of the base no longer depends on the order in which records are displayed. This allows for functionality, difficult if not impossible to achieve with a spreadsheet, based on the ability to present and aggregate the same data across a variety of different views.
Unfortunately, in its current incarnation, Airtable doesn’t make it easy to define relationships between records or to define persistent values. (A ‘run once’ tick box on a formula would make things much easier… while, no doubt, sowing additional complexity and confusion at the same time. Sigh.) However, there are ways to do so.
Case in point: I’ve tossed together an example base to demonstrate one possible implementation of the functionality you described.
The first three fields of the ‘Daily Closing Price’ table — {Date}, {Price}, and {Change in Price} — came directly from your post. To these I’ve added a handful (hmm… 6, actually; would that be a “baker’s handful?”) of additional fields. {DateCardinal} and {PreviousCardinal} establish the cardinal day value of {Date} and the most recent prior trading date, which I define as the Monday through Friday, previous. {DatePrice} is simply {DateCardinal} and {Price} concatenated with a colon (’:’) as separator.
The most essential added field is {Aggregate}, which is a linked record field linking each record in the DCP table with the single record in Aggregation. Not only does the latter table contain only one record, that record contains essentially only a single meaningful field: {DailyPrices}, a lookup of all {DatePrice}s from DCP. The Aggregation table is needed only because Airtable presently does not support a lookup or rollup of values from within the same table; accordingly, here {DailyPrices} aggregates daily price data from DCP to Aggregation, and {AllPrices} performs a complementary lookup of the aggregated values back to DCP.
The last additional field in DCP, {PreviousPrice}, scans {AllPrices} in search of a closing price recorded on the day specified by {PreviousCardinal}. If one is found, the amount is extracted; if not, a blank value is returned. Finally, if the value of {PreviousPrice} is not BLANK(), {PreviousPrice} is subtracted from {Price} and the difference returned in {Change in Price}.
Admittedly, that is a bit more complicated than B2 - B1 — but, honestly, it is far easier to look at and understand than it is to describe. The only significant difference in its use, as compared with a spreadsheet-based implementation, is that an explicit link to Aggregation must be defined for each new DCP record. Fortunately, given there is but a single record in Aggregation, to do so only requires two mouse clicks: once on the plus sign (’+’) in {Aggregate}, to add a link, and a second time on the Aggregation record itself. (As is my habit for such links, I named the Aggregation record ‘
’, the White Heavy Check Mark emoji. This allows for quick visual confirmation in DCP that each record is properly linked.)
Take a look at the example base and see if it makes sense. (As you can’t examine the configuration of cells in a read-only base, I’ve documented each field using the field description, including the formula used. Alternatively, you can copy the base, which will give you full access to examine or change any field.)
Finally, notice the row order. The first five records I entered in sequence, as one would in a spreadsheet. The next five records, though, are for dates preceding the first five; they are also entered in random order. Since the algorithm does not depend on row order, the calculated values are still correct. In addition, when I entered the record for September 8, the ‘Change in Price’ field in the first row, which had been set to BLANK(), was automatically updated to the correct value.
I would LOVE to see a video tutorial on this. Is there any thing available?
Sigh.
Somehow, when I wasn’t paying attention, the future of the internet turned out to be… television. I’ve always felt the higher barrier of entry for written documentation versus the YouTube version tended to enforce a minimum quality level painfully absent from most video tutorials. That said, undoubtedly my dinosaur brethren went extinct while waiting for someone to put together a crisp and concise handout summarizing that long, rambling YouTube video on comets that went viral with the proto-mammals…
Give me a little time to locate and update the quiver of tools I used a while back when I was asked to turn a client’s in-house productions into something that conceivably could be viewed with no ill effects by an actual human being. Assuming I can figure out what I did in that base (which is not always a given), I’ll try to put something together over the next week or so.
The problem with this example ist the string searching with a fixed length of 7 characters so it fails shortly with smaller prices (<1000)… this needs a proper detection of the delimiter “,” to compute the real length. Unfortunately the formula doesn’t support regular expressions so it’s a mess with nested “search”.
It needs a cumulative formula function for such very often needed stuff, maybe also regular expressions or some more robust text manipulation functions.
I didn’t anticipate much need to support gold prices of less than $1,000/ounce. 
These demo bases are intended to be exactly that: demonstrations of approaches or techniques, not finished applications. (It may not have to support gold at $999, but January 1st definitely follows December 31st, and the base will fail then, as well.) Typically the poster has a much more complex base under development but has hit a snag in one area; if I think I may have a way to help him or her get beyond it, I’ll throw together enough of a base to document my suggestion, but I make no claims for providing a universal solution. Often, I’ll identify areas that need to be strengthened or that rely upon arbitrary limitations — that assumed 7-character string-match length, for instance.
(Speaking of, should you need to support a wider range of values, you could left- or right-pad the strings or re-engineer the actual matching code.)
I recently implemented a newer version of this algorithm in my reply to another post. The improved method can be found in this demo base:
Here is a tighter, much simplified implementation of the Gold Price demo. It eliminates three fields, including the ‘mega-field’ of concatenated prices that previously was replicated with each record. It also replaces the cardinal day values with ‘days since epoch’ values, allowing the calculation to roll over properly on January 1; this version should operate properly through January 18, 2038.
For those of you keeping score at home, the improvements underlying this version are largely the result of replacing rollup functions with rollup formulas. (See the definition of {PreviousPrice} for more details.) Other efficiencies were realized by moving what were previously standalone calculations (for instance, to determine {DateCardinal} and {PreviousCardinal}) into the {DatePrice} and {PreviousPrice} formulas.
Hi @W_Vann_Hall
First of all, thank you for sharing your formula to the community.
I’m fairly new to Airtable, but I have seen all your examples of an Excel formula that references on the daily change of amount.
However, may I request for you to create a formula in which a person checks his Bank Account and Balance everyday?
For example:
01/21/2018: Credit - Debit = Current Balance
01/23/2018: Credit - Debit = Current Balance
01/26/2018: Credit - Debit = Current Balance
How do you do that? Thanks.
After reading this post I feel Airtable is in dire need of serious relational operators in formulas, like JOINs, IF EXISTS, IN… and so on
Unfortunately, if I understand your intention correctly, you’re going to bang up against Airtable’s attempts to prevent circular references. That is, you can’t use {Current Balance} as an input to calculate {Current Balance}. In most cases, this makes perfectly good sense; however, it’s an annoyance with some of my routines where, if I could, I would extract part of a rolled up string in order to define a different part of that string. (Of course, there’s no reasonable way to expect Airtable to realize that, nor is there any way for it to guarantee the extracted and defined areas of that string will never overlap.)
The routines and bases described and referenced in this admittedly not-all-that-easily-read post include suggestions for and cautions regarding calculating running balances. I apologize for its opaqueness: Some of it is caused by the extremes to which one must go to work around Airtable, but part of the confusion undoubtedly stems from deficiencies in my explanation…
Thank you for the thoughts on the shortcomings of the Spreadsheet vs Airtable metaphor. This has helped me put Airtable in some perspective. I would venture to say the metaphor is worse than a double-edged sword. In my case, it is damaging to my impression of the Airtable user experience. I just signed up for Airtable and have been using it for 30 minutes and I am already banging my head because I cannot edit a formula directly and quickly. Having to click a dropdown menu and then click an option before editing or even seeing the formula seems crazy to me. With a spreadsheet, you just click on a cell and you can see what it is, formula or not. With Airtable it seems “the guts” are hidden behind multiple layers of UI.
As a result, I have now decided to scrap what I have been doing and approach the Airtable concept from 0 without any “spreadsheet” ideas at all. If the first 30 minutes is any indication, if I don’t, the Airtable UI will drive me crazy. That being said, I am sure that in time I will learn to appreciate the power Airtable offers and get comfortable with the UI.
You just need to double-click the field header. In a spreadsheet, to need to double-click the cell (or click the cell and look at the formula bar). It’s the same work.
Thank you for the tip Elias!