Airtable formula contest: the results are in!

Katherine Duh -

In our March newsletter, we sent out a call for you to submit your favorite Airtable formulas. We asked: What formulas have you made that have improved your workflows? What formulas have you made that are a little off-the-beaten-path? What formulas have you made that have meant a lot to you?

We received so many great submissions that it was hard for us to choose. (In fact, we ended up sending out several pairs of honorable mention socks because we so appreciated the love and hard work you all put into your formulas.) In the end, we wanted to call out three of our favorite formulas that were submitted. With these formulas, you can:

Your very own roshambo-bot

Here's a fun one: a person writing in as Rock, Paper, Airtable devised a formula that allows someone to play rock, paper, scissors against an Airtable base.

First, they set up a form that the human player can use to submit their choice of either rock (🗿), paper (📜), or scissors (✂️), using a single select field.

Next, Airtable picks one of rock, paper, or scissors using a formula based on the created time of the record.

IF({Human_Choice},
  SWITCH(
    MOD(
      VALUE(
        DATETIME_FORMAT(CREATED_TIME(),'s')
      ),3
    ),
    0,'🗿',
    1,'📜',
    2,'✂️'
  )
)

Another formula in the primary field evaluates the matchup to determine who won.

RPA even set up a page designer block to make the results of each matchup more dramatic:

HTML table → Zapier → Gmail

Alli in New Hampshire uses two different formulas to create an HTML table. She's also set up a Zapier integration using Airtable and Gmail that automatically takes the formula results and turns them into an email containing weekly or monthly reports.

A redacted example email generated by the formulas and Zap, courtesy of Alli.

The first formula creates a static text header for the table:

"<td>" & {Item 1} & "</td><td>" & {Item 2} & "</td><td>" & {Item 3} & "</td><td>" & {Item 4} & "</td><td>" & {Item 5} & "</td>"

The second formula uses a rollup field to make the rest of the table body, and automatically adjusts to how many records have been linked.

"<tr>" & ARRAYJOIN(values, "</tr><tr>") & "</tr>"

A redacted screenshot courtesy of Alli showing how the rollup field generates the HTML tables.
A redacted screenshot courtesy of Alli showing how she set up the Airtable-Gmail Zap.

“The formula has been extremely helpful in generating all sorts of emailed reports. We’ve saved hours of time already,” Alli says.

Quantifying time saved with Airtable

David the Deduper was so excited about moving his work onto Airtable that he devised a formula to calculate just how much time he's saved.

(({Time Saving}/7)/60)*DATETIME_DIFF(TODAY(),{Release Date},'days')

"I wanted to show how much time Airtable had saved since moving from Excel," he says. "I added this field to our product release tracker so we could map it out in a chart block!"

A screenshot of David's base, complete with a chart block.

Coming up

Has reading this given you formula FOMO? Fret not, we'll run another formula-based contest soon—stay tuned to our monthly newsletter.

In the meantime, why not submit for our April contest? We're looking for the most useful, quirky, cool, and all-around-interesting Airtable Universe bases. Come one, come all, and if you're lucky, you might get a pair of Airtable socks out of it! We're taking submissions until May 1, 2019.

Sign up for Airtable for free