What are your favorite Airtable tips & tricks?

It’s too bad you can’t wrap those formulas in an IF/ISERROR and format them as a date. E.g.,

IF(ISERROR(DATEADD({Publication Date},-14,‘days’),’’,DATEADD({Publication Date},-14,‘days’))

This would allow for the draft date calculations to not show #ERROR! when there is no Publication Date

Tracking and planning the fieldwork I do for my job has definitely improved. With a few inputs (location coordinates and City names) I am able to create google maps and weather underground URLs for easily checking locations and forecasts. The Map block has also helped visualize all our outstanding fieldwork at once making it easier to plan multiple projects that are near each other (rather than having to manually lookup all the locations each time).

For reference, here are my google maps and weather underground formulas:
Location
IF({Location Input}!="", “https://www.google.com/maps/place/”&SUBSTITUTE({Location Input}," “,”%20"),BLANK())
Weather
“https://www.wunderground.com/weather/us/ca/"&SUBSTITUTE({City}&""," “,”-”)

Edit: Apologies for somewhat OT reply.

This

IF(
    {Publication Date},
    DATEADD(
        {Publication Date},
        -14,
        'days'
        )
    )

does what you want.

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:

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

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

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

4. 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 newline character ('\n') inside a text field, one can force a line break. At first, this seemed to be of limited utility, as Airtable treated newlines in single-line text fields as a white space character in grid view. While it did honor newline in 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 treated newlines as white space; one had to display the expanded record to see the line breaks.)

   The first practical use for embedded newline characters came with the release of gallery and kanban views. While those views still ate newlines 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 the ARRAYJOIN() 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. Embedded newlines were 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 embedded newlines in either single-line or long text fields.

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

6. Finally, there is the trick that started me down the path towards Airtable ninja-hood: Use LEN() and SUBSTITUTE() 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 '|', the vertical bar character) as the second parameter to ARRAYJOIN() and replace the comma (',') in the SUBSTITUTE() 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:

. ______________

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

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

3. While the feature is presently undocumented, enough implementations make use of it, it is expected to be supported going forward.

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

5. Terminology courtesy Humpty-Dumpty.

IF(
    {Publication Date},
    DATEADD(
        {Publication Date},
        -14,
        'days'
        )
    )

That is terrific. I never would have figured that out on my own. Also, I never realized you could structure formulas across multiple lines for readability!

(Quick note: There is a missing comma at the end of line 2)

Yeah, it’s a great, little-known feature of Airtable. Even better, even though the system seemingly condenses the formula when you click ‘save,’ it remembers indentation and line breaks, so if you copy the formula from a formula field and paste it into your editor, the formatting is preserved.

‘My editor?’ you ask. Yes, because it is virtually impossible to craft truly complex formulas using the built-in configuration editor. That’s not a slap at Airtable; they expect anyone writing in-depth code to be doing so in an external editor that can provide such things as automatic delimiter matching (no more '{'s without a matching '}') and indentation.

For example, take a look at the formula included in this reply. I suspect I could spend the rest of my waking life trying to code that directly into the configuration window and still never get it to work, Editing it offline in Notepad++ didn’t make the code any easier to write, but it did mean I didn’t have to worry about counting parentheses and checking for missing commas, too.

And speaking of commas…

Got it — thanks!

When managing/working with multiple contact data sources, to create and update one record per contact, create a master contact table with a primary field that is in all of your other sources (email, for instance - or company name). Create an import table linked to the contact table, and when you copy and paste your data from other sources, it adds any new contacts. Use calculated, lookup and rollup fields to generate an update view- for instance, you’ve just added 20 contacts who attended an event you hosted on 7/18/18. Your view in the contact table ‘Update’ is filtered by the lookup field ‘import table creation date’. and predefined import table rollup and lookup fields - like the date of the last event attended - can be used to update fields in the contact table with a simple column copy and paste.

You can even create zaps in zapier to auto-update your import table, and filter the Update view to reflect updates of your defined fields for the month or week. Useful for managing contacts from multiple online data sources like email subscribers, event attendees, linkedin connections, blog subscribers, product sales, fellow chamber members, prospect lists and more.

With regard to…

I’m not finding this to be the case.

For me, import or paste of a cell value that contains \n does not render that as a line return. It just comes in as the text ‘\n’ – as described at Importing long fields with text breaks.

Can you comment please?

(I am awaiting a reply from Airtable to an email support request on this issue.)

Well, that’s a slightly different use case than I was describing. The newline character — represented as '\n' — when used in a concatenation, is converted to its, um, I was going to say ‘ASCII’ but I guess it’s UTF-8, encoding. Until recently, this typically didn’t buy you much, as single-line text fields are, unsurprisingly, single lines, and Airtable treats the newline character as white space. However, with two recent enhancements to the product, Airtable will treat a newline as a new line, even in single-line text fields, in the Page Designer Block and in grid views with row height set to anything other than ‘short.’ In addition, as has been the case, you can copy-and-paste strings with embedded newlines from a calculated single-line text field (which includes roll-up fields that return a string) into a long text field, and Airtable will treat the '\n's appropriately.

Note this is an Airtable-to-Airtable paste; it does not work when pasting from another source — or, at least, I’ve not been able to find a method that works. The same goes for importing text with embedded newlines. I think your best bet is to substitute a different character (I’m always partial to '|') for newlines in your source data and then replace them after import using SUBSTITUTE(), as @jn08 recommended in the other thread.

I’m not familiar enough with how data moves to and from the clipboard (in any OS) to suggest possible workarounds. I have learned any copy-and-paste from a field containing any sort of formatting characters or embedded commas contains surrounding quotes — even when it seemingly doesn’t. (Apps and/or the OS often hide the surrounding pair, even when that’s not intended; they can still be found in a hex-dump.) Similarly, marking and copying the contents of a cell containing newlines — that is, by selecting before the first character and dragging beyond the last, as opposed to copying the entire cell at once — will bypass the surrounding quotes — but attempting to paste that value into a cell will cause only the data up to the first newline to be pasted in that cell, with everything between the first and second newlines pasted into the cell below, everything between the second and third ito the cell below that, and so on…

Along the same lines, Airtable appears to do a similar massaging of newline-containing data. Assuming I can take the API documenting interface literally — which we already know I can’t, as API keys, at least, are intercepted before display — a calculated single-line text field containing a newline is represented as

"\\"Line 1\nLine 2\\""

When pasted into a long text field, that data would be represented as

"Line 1\nLine 2"

But attempting to paste into either text field from an external source inevitably results in something along the lines of

"\\"Line 1\\nLine 2\\"" or "Line 1\\Line 2"

In other words, Airtable escapes the escaping character and, as such, embeds a literal '\n'.

Wish I had a lower-impact workaround than search and replace, but I don’t…

Thanks so much for the detailed reply.

As you suggest, I’ll try a pipe character as a placeholder with SUBSTITUTE().

For what it’s worth, Airtable support replied to my request with:

“We are in the process of building out a solution that should handle this [line break] situation cleanly, though it’s a ways off.”

In replying to a different message, I realized I hadn’t mentioned the source of some of my favorite tips and tricks: The list of format specifiers for DATETIME_FORMAT() and DATETIME_PARSE(). In fact, my very first Airtable Community post dealt with a DATETIME_FORMAT() trick.

Two of my current favorites are

1. Getting the epoch day.
   Based on the Unix time since epoch specifier, which returns seconds ('X') or milliseconds ('x') since midnight, January 1, 1970, you can calculate the epoch day, or days since January 1, 1970. This will provide a unique, sequential day value for every date from 1/1/1970 through 1/19/2038, when the Unix date system [currently in use] breaks down. I like it in particular because it supports calculations based on tallies from the previous day, but it doesn’t need manual assistance to help it past New Year’s Eve, as many of us, and algorithms based on the cardinal day, do. The formula —

   INT(VAL(DATETIME_FORMAT({Date},'X'))/86400)

   — can easily be reversed by multiplying epoch day by 86400 and using DATETIME_PARSE():

   DATETIME_FORMAT(
      DATETIME_PARSE(
          ({Epoch Day}*86400)&'',
          'X'
          ),
      'M-D-YYYY h:mm'
      )
   

2. Restating local time as GMT.
   Occasionally, one may need to restate one’s own local time as GMT or need to compare two local times. Clearly, this is done by adding or subtracting the local time zone’s offset from GMT to the base datetime with DATEADD(). But how does one determine the offset — a big, ugly IF() statement mapping timezones to hours before or after GMT? A far easier method is to use DATETIME_FORMAT() with the 'ZZ' specifier, which returns the date’s positive or negative offset in hours and minutes without a separating colon.¹ As a result, you can use the following formula to restate a date as GMT:

   DATEADD(
       NOW(),
       ((VALUE(
           DATETIME_FORMAT(
               SET_TIMEZONE(
                   NOW(),
                   '>>>Timezone Specifier<<<'
                   ),
               'ZZ'
               )
           )/100)*60)+
           MOD(
               VALUE(
                   DATETIME_FORMAT(
                       SET_TIMEZONE(
                           NOW(),
                           '>>>Timezone Specifier<<<'
                           ),
                       'ZZ'
                       )
                   ),
               100
               ),
     'minutes'
     )
   

   Where you see '>>>Timezone Specifier<<<' replace it with the appropriate specifier from this list. If you want to restate a date or time other than NOW() replace NOW() with the appropriate field name.

   Note that if you’re working on an application where you can be assured of never running into any partial-hour outliers — that is, dates from timezones offset from GMT by an amount of hours and minutes — you can use this somewhat simpler formula:

   DATEADD(
       NOW(),
       VALUE(
           DATETIME_FORMAT(
           SET_TIMEZONE(
               NOW(),
               '>>>Timezone Specifier<<<'
               ),
           'ZZ'
           )
           )/100,
       'hours'
       )
   

1. My uncle had one of those; he said it was extremely uncomfortable. [ba-Da-BUM!]

Is there an external editor you can recommend to write airtable formulas…?
I’m trying to get by with the built in editor, but things are getting very confusing and I think I’ll soon have to ‘graduate’ to an external editor

I started to reply to your question, but Discourse stopped me to say I’d already linked to my go-to editor for Airtable earlier in this thread — so I guess I should point you towards this reply. :winking_face:

Hey Alex, this is super cool, and something I’m looking to implement into my airtable for similar-ish field work jobs. Right now we have the lat/long coordinates for each location, and I’d love to derive weather stats for that based that. But it looks like for your formula’s you used city names. Any chance you know how to translate that?

Hi Paul :wave: The variables you can use in a formula to build a URL for this will depend on what’s supported by the weather service/website you’re using. Does the website you’re using allow for searching by coordinates? Or, when you open a given location for weather data through the site, are coordinates shown in the URL?

If either of the above is true, you could theoretically build that into your formula :thumbs_up: