Re: What are your favorite Airtable tips & tricks?
A while back, I began work on a book[let] compiling Airtable tips, tricks, and hacks gleaned from the ‘Ask the community’ (née ‘Support’) forum. This work stands unfinished, as @Jeremy_Oglesby helpfully pointed out a few months ago. :winking_face: I still believe a close reading of the forum is undoubtedly one of the two best sources of information on Airtable best practices.¹
As far as my own favorites, I have to go with these:
-
Automatic generation of linked records by pasting one or more values into a linked-record field. I was quite pleased with myself when I discovered this trick last January — and it has remained one of my favorite tricks, even after I learned not only was it a well-known item of Airtable functionality, it was actually thoroughly documented. My most far-ranging use of this trick was in a base I prepared for a client that used a single copy-and-paste action to trigger the creation of 468 linked records. (The base also made use of an incredibly ugly 468-branch²
IF()statement to turn each newly created record into a single entry in a multi-page evaluation check list.)A related linked-record trick involves linking multiple records to a single linked record by pasting the same value into multiple linked-record fields. If pasting multiple unique values into a single linked-record field causes the creation of multiple records, then pasting a single value into multiple linked-record fields will cause all of those records to be linked to a single new or existing record. I typically use this technique in support of the following item.
-
Linking all records in a table to a single record in another table to allow cross-record calculations. They say, to a person with a hammer, everything looks like a nail. That is meant to be a cautionary note — but the fact remains, every now and then the person with the hammer legitimately turns out to be standing in a nail-rich environment. Thus was the case with this Airtable trick. As best I recall, I first made use of it to turn a column of data into a row of monthly subtotals. From there, I used the same technique to calculate a daily change in price and, later, a running balance. Perhaps most significantly, I showed how this method could be used to create extremely flexible, implementation-specific de-duplication routines that went far beyond simply enforcing unique values for a field. Admittedly, at first I wondered if I was hammering in too many screws, but the lack of alternative methods proposed to date leads me to conclude these are indeed all nails. (The biggest downside of this technique is the intermediate field used to pass data from one record to another can easily become extremely large, causing noticable lag in processing and response. This impact to performance can be minimized, but not eliminated, by use of the following item.)
-
The use of aggregation formulas, as opposed to aggregation functions, in the configuration of rollup fields. A little-known and, to date, officially undocumented³ feature is the ability to define full formulas in the aggregation function section of the rollup field configuration window.⁴ Doing so appears to provide two benefits: First, it helps reduce visual clutter by cutting the number of fields required, and, second, it seemingly improves performance. While the first of these claims is the easier to prove, it is the second that is potentially of greatest importance.
Traditionally, one makes use of a value retrieved from a linked record through a two-step process: First, a lookup or rollup field is created in the current record to surface a value (or values) from one or more linked records; second, a formula field is created in the current record that incorporates the linkup or rollup field in its calculations. As a result, this requires the creation of at least two fields for every formula making use of a previously unreferenced value from a linked record. This in turn widens the current table’s footprint, leading to increased horizontal scrolling, and raises the number of named fields, increasing the potential for confusion.
The second potential benefit is harder to quantify, given my lack of visibility into Airtable internals. Likewise, given the large number of variables that can affect perceived response and performance times concerning web-based apps, it is extremely difficult to reach any definitive conclusions as a remote user short of analyzing a statistically significant sampling of A/B comparisons performed under a range of conditions. I can imagine an architecture where there would be essentially no difference between an implementation using aggregation formulas and one using aggregation functions; I can imagine one where there would be immense differences. However, in my entirely subjective opinion, formula-based implementations seem to be ‘snappier,’ more responsive, than function-based ones. Assuming that is true, the following paragraph offers purely speculative reasons as to why that might be the case.
Depending on the number of linked records, the size of the field queried, and, in the case of rollup fields, the aggregation function used, it is possible for a looked-up or rolled-up value to become quite large. Should that value be surfaced back to the originating table — as is the case with the all-to-one links discussed in the previous item — this could conceivably greatly increase base size. For instance, a few days ago I create de-duplication routines for a 5,500-record table that required the construction of two match strings. The first string contained a match key assembled from the first and last names of each individual listed in the table; the second key was based on the person’s last name and employer. Together, these keys totalled about 160kB. If a separate copy of both keys had to be instantiated for each record in the table, this would add around 890MB to the base — 890MB that potentially could be avoided through the use of aggregation formulas. (Even if Airtable is capable of more-intelligent memory management, using a looked-up or rolled-up field per-record would necessarily add processing time to some actions — paging through a grid view of records, for instance — because of the time required to recalculate the field afresh for each record containing it that is displayed.)
There are limitations to aggregation formulas. For instance, as the configuration editor does not officially support them, they must be built without the assistance of Airtable’s standard field and function pick list and context-sensitive help. In addition, while I’ve yet to identify an unsupported function, reportedly they support only a subset of functionality available within a formula field. Finally, an aggregation formula may address only a single field within the linked record; if a calculation involves two or more such fields, the others must be accessed through a standalone lookup or rollup field. (However, it is possible to concatenate multiple values into a single, portmanteau⁵ value that can be retrieved and broken into component parts by an aggregation formula.)
For my part, I have essentially ceased to use lookup fields. Instead, I define a rollup field, specifying the same table and field as I would for the corresponding lookup. For the aggregation function/formula, I enter only the keyword
values. This returns an array value, the same as would the comparable lookup; also the same as for the lookup, it may need to be converted to the appropriate field type for use in calculations (for instance, by appending&''to cast it to a string). By defining even lookup-like fields as rollups, I greatly simplify the task of reconfiguring them into aggregation formula-driven rollup fields later on, if so desired. -
Creating multi-line text fields with newline. At some point, I stumbled across this post; while I had no use for it at the time, I kept it in mind. Aided and abetted by recent enhancements to Airtable, I’ve since come up with a handful of ways to make use of this feature.
In brief, by embedding the Unix
newlinecharacter ('\n') inside a text field, one can force a line break. At first, this seemed to be of limited utility, as Airtable treatednewlines in single-line text fields as a white space character in grid view. While it did honornewlinein long text fields, those fields had intrinsic line break support baked in: Pressing the carriage return while entering or editing long text inserted a line break without having to futz around with'\n'. (Even in the case of long text fields, though, grid view treatednewlines as white space; one had to display the expanded record to see the line breaks.)The first practical use for embedded
newlinecharacters came with the release of gallery and kanban views. While those views still atenewlines in single-line text fields, displaying, unsurprisingly, only a single line of text, they did display the first four lines of text from a long text field. Furthermore, it was possible to define'\n'as the separation character optionally specified as a second parameter to theARRAYJOIN()aggregation function. While the resulting rollup was treated as a single-line text field in kanban and gallery views, it was possible to copy and paste the rollup value into a long text field and have the first four lines of that field displayed. I used just that trick in my Wardrobe Manager base in Airtable Universe to display a garment’s first four dimensional measurements; the user could perform the copy-and-paste manually, typically of the entire column at once, or choose to have it done automatically by means of a Zapier Zap whose configuration I provided.It was with the advent of Airtable Blocks, though, my use of embedded
newlines really took off. I discovered the Page Designer Block honored'\n'in both long and single-line text fields, displaying as many lines of data as would fit in the space allocated. Embeddednewlineswere an essential part of the Page Designer trick described in the following item. Finally, although officially released the month before Blocks, but a number of months after the opening of the Blocks beta test, variable line height made'\n'meaningful in grid mode. With configurable line heights of ‘short,’ ‘medium,’ ‘tall,’ and ‘extra-tall,’ grid view now displayed up to one, two, four, or six lines of text, respectively, and it supported embeddednewlines in either single-line or long text fields. -
Layered fields can be used to fake conditional formatting in the Page Designer Block. I initially conceived of this as little more than a smart-ass trick to show off as part of my Black Mirror base in Airtable Universe, but I quickly realized it offered potentially significant display capabilties otherwise unachieveable with Airtable alone. It can be amazingly frustrating and time-consuming to implement, thanks primarily to inconsistent and erroneous support for web fonts inherent to every browser I tested; however, in the hands of someone with more self control than I, it can be a valuable technique.
It’s probably easier to illustrate than explain. First is a Page Designer Block from the afore-mentioned Black Mirror base showing the distribution of nineteenth place through first place rankings for all ‘Black Mirror’ episodes to date. Placement left to right indicates the rank assigned; at each grid position, the number and, more importantly for this discussion, the color used indicate how many critics assigned a given episode the specified rank. (For instance, the most consensual votes recorded were from the five critics who found ‘The Waldo Moment’ the worst episode so far.)
The second illustration is from a base that attempted to mimic an Excel win/loss table that used conditional formatting to color underlying cells.
In both instances, different colored layers are used to set the color for elements of a similar value — all episodes receiving three votes for a given rank, for example, or the background for all matches at a 50%/50% win/loss ratio. By stacking layers in an appropriate order, a multicolored effect can be created from multiple monochromatic elements.
Aligning the various overlays properly can be an unbelievably maddening process. If at all possible, use monospaced fonts. When that is not an option, or when you have to use a mixture of two or more fonts, a chart showing how many pixels wide each of the Unicode space characters measures in your chosen font becomes essential. (Another essential reference is Google’s web font preview page. Note that Airtable only supports a subset of these fonts — a large subset, true, but a subset nonetheless.)
-
Finally, there is the trick that started me down the path towards Airtable ninja-hood: Use
LEN()andSUBSTITUTE()to count items in an array. At first glance that might seem less than Earth-shattering, given Airtable has no explicit provisions for array manipulation, but this technique works on any collection of values that can be returned as an array. This includes any lookup field, many rollup fields, collaborator fields, and linked-record fields.All versions of the technique are based upon a reply by a user named @Simon_Brown. Brown seemingly logged into the forum once, for five minutes, two years ago, during which time he posted a single reply that just happened to reveal the trick to counting the number of [blanks] in an array of [blanks]:
IF( LEN( {ArrayString} )=0, 0, LEN( {ArrayString} )-LEN( SUBSTITUTE( {ArrayString}, ",", "" ) )+1 )Essentially, this subtracts the length of the array (here presumed already to have been cast as a string) with commas removed from the the length of the array. The difference in length is equal to the number of commas in the original string, plus one, to account for the last item in the array. This works only if there are no commas embedded in any of the array’s items. If there are, and the string was initially returned by the
ARRAYJOIN()function, one option would be to designate a non-comma separator character (personally, I’m fond of'|', thevertical barcharacter) as the second parameter toARRAYJOIN()and replace the comma (',') in theSUBSTITUTE()function with the custom separator character.The number of instances of a specific value in the array-string can be found using the following variation:
LEN( {ArrayString} )-LEN( SUBSTITUTE( {ArrayString}, {MatchItem}, '' ) )/LEN( {MatchItem} )Obviously, many, many variations on this basic structure are possible, but they all hearken back to that one post from @Simon_Brown.
It was stumbling across this trick, along with a reply from @Matt_Bush, that first opened my eyes to how much could be accomplished by thinking even slightly outside the Airtable box. Not only has virtually everything I’ve done in Airtable the past 16 months been inspired by those two posts, I still make use of each of them at least once a week.
So, those are my [current] favorite tips, tricks, and hacks. If you’d asked two weeks ago, or if you ask two weeks from now, the list might be different. As far as I can tell, the only way to stay up-to-date with whatever clever schemes users have found to wring even further functionality is through regular, comprehensive visits to Airtable Community…
…either that, or convince Airtable to pay me to finish my book. :winking_face:
. ______________
-
The other would be by polling Airtable’s dedicated enterprise support people. As most enterprise users do not appear to make use of Airtable Community — either that, or they simply don’t identify themselves as such — it’s difficult to determine from outside the company whether enterprise and non-enterprise users have roughly equivalent skills in using Airtable, or if one group greatly leads or lags the other.
-
Actually, two
IF()statements containing a total of 468 branches, along with a third field combining the outputs of both. This two-part structure was introduced once it was discovered Airtable could only handle statements with a maximum of about 350 branches. -
While the feature is presently undocumented, enough implementations make use of it, it is expected to be supported going forward.
-
To define an aggregation formula, the user configures the first two parameters of a rollup field as usual, specifiying the field that links to the target record and the remote field to be referenced. In the third parameter, which would ordinarily contain a simple aggregation function, the user inserts a full-blown formula, the only difference being the referenced field is always addressed through the keyword
values. -
Terminology courtesy Humpty-Dumpty.
