Leveraging formulas in Airtable

Formulas allow you to compute values based on values in other fields. With the judicious use of lookup and/or rollup fields, you can also compute values based on values from other tables. Formulas are incredibly powerful, and when in doubt, there's probably a way to do what you need to do using formulas. Read on to learn the best ways to get started with formulas.

Using a formula as the primary field

The first column of every table acts as its primary field and it only allows specific field types to promote its use as a unique identifier for each and every record. While text, numbers, and dates tend to fit most use cases, you can utilize formulas to automatically populate your primary fields. You’ll find this useful when you want to combine multiple fields into one, such as episode names and numbers for a TV show or multipart inventory codes. Furthermore, you’ll almost always need a primary field formula when creating junction tables.

When you have two tables with information you want to combine, you can create a third junction table. Perhaps you want to create a payroll timeline that consists of every paycheck issued to each individual employee, or you need to create groupings of student grades for report cards. Junction tables help solve these types of problems by allowing you to manage your other tables separately while automatically populating a third with a combination of information from both.

Example 1: Junction tables using DATETIME_FORMAT

Junction tables can help you manage your hiring process. Rather than keeping track of all the information associated with every interview in both the candidate’s and interviewer’s records, you can create a third table to log it all and connect those records together. When employing a third junction table to create a log, you’ll want to use the primary field to maintain a consistent format otherwise your records will have confusing, variant titles.

You can solve this problem by using a formula for your primary field that combines the candidate’s name, interview type, and the date and time that interview took place:

Candidate&"'s "&{Interview Type}&": "&DATETIME_FORMAT({Date/Time},'M/D h:mm a')

Now your records will maintain more consistent and informative titles automatically.

When you combine dates with text, numbers, and other fields using a formula you should always remember to include the
DATETIME_FORMAT function—as seen in the example above—so it looks exactly the way you want.

Example 2: Junction tables using an auto number field

The auto-number field fills itself automatically counts the number of the records you create. This field type provides a way to ensure you always have a unique identifier in your primary field. You can use an auto-number field as your primary field, but that won’t provide you a very informative title. Combining an auto-number field with another, using a formula, solves that problem.
Here’s an example:

{Product Type Code}&{Auto Number}

The formula above would produce a result that looks something like this:

When you don’t have a reason to work with dates and time, a formula that references an auto-number field can ensure unique titles in your primary field. Even better, you can hide the auto-number field after creating it so it doesn’t clutter up your view. It doesn’t require any future editing because it populates itself.

The many uses of IF

You use “if” in your everyday life. For example, “If I eat a snack right now, will it spoil my appetite for dinner?” If you eat the snack and your concern becomes true you know the result: You won’t eat as much for dinner and may enjoy it less. If you don’t eat the snack, however, you’ll have a voracious appetite for dinner.

The general concept of “if” exists broadly throughout life as we try to determine a result based on a set of binary choices. The same idea applies with formulas in Airtable. When using the IF function, you provide a logical argument for the formula to consider and two values to choose from based on the result of that argument. If the argument proves true, Airtable will display the first value. Conversely, Airtable will display the second value if not.

Let’s take a look at an IF function with some placeholders:

IF([logical argument], [value1], [value2])

Here’s how that might look if applied to the previous real life example:

IF({Eat Snack}=TRUE(), "Not hungry for dinner.", "Very hungry for dinner!")

If your Airtable base contained a Checkbox field called “Eat Snack” and a formula field with the above formula, it would display that you’re “Not hungry for dinner” every time you check the box in that record. If the box remained unchecked, it would display the second value instead.

The TRUE()function checks the status of the checkbox for us in the above example, but you can evaluate the contents of different types of fields with IF in several ways. For example, you can check if a text field equals a specific value:

{Pet's Name}='Rufus'

You can also test if a Number field matches a number or has a value that meets certain criteria. For example, you can check for test scores above 75 like this:

{Test Score}>75

You can even compare two fields to one another if you like:

{Field 1}={Field 2}

Remember, when writing formulas you should enclose field names in {} (curly braces) and strings of text in "" (double quotes). Numbers, on the other hand, may roam free.

Nested IF formulas

IF can make a simple binary decision on its own, but what happens if you require a more complex determination? Like a set of Matryoshka dolls, you can nest your IF functions inside of one another. Airtable will resolve the IF function on the outside first and work its way towards the center until a condition matches and it can display the value you provided for that resolution. This allows you to create formulas that output more than two responses.

Let’s take a look at the generic version of one IF nested inside another:

IF([logical argument1], [value1], IF([logical argument2], [value2], [value3]))

If you needed to check a field called “Purchases” to find out if a customer has made less than 5 purchases, more than 5 but less than 10 purchases, or as many 10 or more, your nested IF function would look like this:

IF({Purchases}<5, "New Customer", IF({Purchases}>=10, "Super Customer", "Regular Customer"))

The first IF function checks to see if the purchase total amounts to less than five. If it does, the function resolves immediately and the formula displays “New Customer” in the corresponding record. However, if the purchase total exceeds 5, then the formula moves down the line to the second IF function and checks if the total equals 10 or more. If it does, the formula displays “Super Customer” for their obvious loyalty to your store. If neither IF function meets the conditions you set, the formula displays the fallback value “Regular Customer” for making somewhere between 5 and 9 purchases.

Of course, you can use nested IF functions for more than comparing numbers. You can make all kinds of checks and nest as many as you need to reach your desired conclusion. While this method can save you a ton of time in the future, the proper setup of nested IF functions can take planning and forethought when your formula requires several layers. For more in-depth examples on how to construct a nested IF formula, read through the examples in this support article.

Conditional emoji formula

When you need more than text and color codes to provide visual cues for your tables, you can use emoji to expand your options. While Airtable supports emoji in many other places as well, you’ll find them particularly useful when creating conditional formulas. For example, you can display a more visual project status with emoji than you can with text.

In this example, a completed task gets a 🚀, an overdue task receives a ❗️, and an incomplete (but not overdue) task defaults to a 🕙.

This works using the following formula:

IF({Complete}=TRUE(), '🚀', IF(IS_AFTER({Due date}, TODAY()), '🕙', '❗️'))

Emoji may display as images but they are comprised of specific text codes behind the scenes. Like all text in Airtable formulas, you need to surround your emoji with single or double quotation marks. Because text and emoji function in the same way, you can also add text to go along with your emoji formula to provide additional clarity to the formula’s displayed result.

To use emoji in Airtable, you can with open the emoji selector (Mac: Ctrl+Cmd+Space | Windows: Windows+. or Windows+;). Alternatively, you can copy and paste your favorite emoji from Emojipedia.)

Using IFto combine and/or filters

Airtable’s filtering system can chain rules together with AND. It can also filter records when it matches one of many rules using OR. It cannot, however, filter based on a combination of the two, because this linear flow requires more information to determine which rules to apply first, last, and in between. Nevertheless, you can create complex filters with the all-powerful IFfunction.

You only need to create a formula field and supply it with nested IF functions that result in the match you want. Here’s an example used to check for dietary restrictions:

IF(AND({Attending?} = 1, OR({Dietary Restrictions} = "Vegan", {Dietary Restrictions} = "Vegetarian")), "true")

While the formula field will only display whether a match (“true”) was found or not, you can create a simple filter to sift out the records that the formula didn’t resolve as a match. If you prefer the formula to work secretly in the background, just hide it.

Creating your own AND/OR combination filter can take a lot of different forms depending on its purpose. For a more in-depth look at how you can create these types of formulas, read this support article.

Formula output types

Formulas can produce different results based on the fields you use when you create them. If you use numbers you will receive a numerical result. You can expect the same results from dates and text. Using the same types of fields will yield a predictable result. Based on the many functions you’ll discover in the formula field reference guide, however, different combinations of fields and functions can change the output type in different ways.

While you can often predict the output based on field contents, occasionally versatile field types—like single line and long text—can contain information that looks just like another field type (e.g. numbers). When text fields contain numbers, you’ll notice they align to the left. Number fields, on the other hand, align their content to the right.

You probably recognize this type of alignment in popular spreadsheet apps you’ve used before. This visual cue provides a quick way to identify the type of information in each cell without the need to check. While Airtable provides helpful icons in the column headers for easy reference, text versus numerical alignment helps to identify the output of a formula field its icon references and not the format of its output in each cell.

Sometimes a formula field can’t determine the most appropriate type because the fields and functions you’ve supplied don’t strongly imply the output format. This commonly occurs when combining a text field with a numerical field (e.g. number, date, rating, etc.). Because text fields can contain both numbers and letters, Airtable will resolve any formulas that mix these types as text due to its versatility.

Although you can’t enforce a field type through the formula field settings, and formulas cannot result in special format field types (e.g. select, rating, email, etc.), the formula field reference guide may provide a function that yields the format you would prefer. For example, DATEADD can alter date and time field information by adding a number to it and ensure the formula field keeps the correct display format.

Using formulas in rollup fields

When using linking records to bring information from one table into another, you can also employ a rollup field to perform calculations and other functions, collectively, on a specific field within those records. Naturally, you’ll need a formula to tell Airtable what you want to do with them. The available functions for rollup fields offer similar functionality to the summary bar in each of your tables.

For example, let’s say you have a table that contains “Menu Items” for your restaurant and you’ve linked it to another table that contains “Sections” for each part of the menu. In each record of the “Sections” table, you’d link records in the “Menu Items” table that belong to that specific section. Using a rollup field, you can reference the exact menu items from each section and perform functions on all of them at once. If you wanted to count the total number of items per section you’d only need this short formula:

COUNT(values)

Unlike standard formulas, you can simply use valuesto refer to the chosen values of the linked records in your rollup fields without the need for further specificity. You can copy and paste the formula above and use it in a rollup field without making any changes.

Formula fields can perform several other functions to find minimums, maximums, unique items, sums, and much more.

Using IFfor conditional rollups

While rollup fields can apply formulas to the values in linked field you specify when setting it up, it can’t test those values against others and draw a conclusion from the comparison. If you tried to test the pH balance of several glasses of water with your eyes alone, you’d run into the same dilemma. Instead, you’d find your answer by examining pH test strips for each water sample and draw conclusions from those results instead. Similarly, rollup fields can look at formula fields that perform the test you need automatically using the versatile IF function we’ve come to know and love. This is a “conditional rollup.”

When you might need a conditional rollup depends on specific circumstances. For example, suppose you created a base to manage your team’s sales pipeline in Airtable. You’d have a table for all deals in all stages, from qualification to closure, as well as a table for your sales reps. While you’d note the value of each deal in this table, only knowing the total value of each deal doesn’t tell you much about your team’s total success. To discover the total value of each sales rep’s successful deals, you required a conditional rollup.

Creating a rollup field in the Reps table that summarizes the values of those deals will give you everything and not just the closed deals. Instead, you can create a formula field that only displays a value if the sales rep closed the deal successfully:

IF({Sales Stage}='Closed-Won', {Deal Value})

Back in the Reps table, you can now create a rollup field that links to the Deals table and choose the formula field you just created to supply what the rollup needs.

With the SUM(values) rollup formula, you’ll see only the aggregate total of the values that met your formula’s condition.

To learn more about condition rollups,read this in-depth support article.

DATETIME_FORMAT

The DATETIME_FORMAT function takes the date and time stamp formatted for computers and translates it into a more people-friendly version of your design (e.g. 2:00 PM on March 15th, 2020). Date fields provide formatting options as a built-in feature, and formulas will do the same if they produce a date and time stamp as output. When combining date fields with fields of other types, however, you’ll want to use the DATETIME_FORMAT function to tell Airtable how to display that information.

Let’s take a look at a generic example of how the DATETIME_FORMAT function looks in a formula:

DATETIME_FORMAT({Date Field},'Output Format')

You can specify any date field as a the first parameter, but you’ll need to choose an output format like 'M/D/YYYY h:mm a'or 'DD-MM-YY' to determine how it will display in each cell of the formula field. You can use either of those popular examples or craft your own out of the many options detailed in this support article.

Due to the vast number of possibilities, shortcuts also exist so you don’t have to remember the exact way to represent many popular styles.

Additionally, 'Q' gives you the number of a date’s yearly quarter so you can use DATETIME_FORMAT to generate quarterly reports. After creating your formula field to display this information, you can group by that record to get a quick overview of all records associated with each quarter.

Because each group has its own summary bar, you won’t have to do anything else to calculate quarterly summaries for every relevant field.

DATETIME_PARSE

You use the DATETIME_FORMAT function to turn a computer-friendly date and time stamp into a people friendly version, but if you want to do the opposite you need the DATETIME_PARSE function. It takes date (and time) information, written in virtually any format, and interprets it as a structured value that Airtable can understand like a normal date field.

DATETIME_PARSE will only display the interpreted date in the ‘M/D/YYYY h:mm a’ format (e.g. 3/4/2017 11:00pm). If you want to utilize a different format, you can wrap it in the DATETIME_FORMAT function.

Related video content