Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific field types

Rollup fields

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

A rollup field is a combination of a lookup and a formula field. It requires a linked record field and a formula. The formula can use all the same formula functions and operators available in a formula field.

Ensuring that a rollup value is a single value and not an array

Technically a rollup can result in either an array or a single value. However, because arrays can behave unexpectedly in other formulas, it is best to ensure that your rollup is a single value by using an aggregation formula.

To get a single text string, use ARRAYJOIN(values).

To get a single date, use MAX(values) or MIN(values).

To get a single number, use SUM(values), MIN(values), MAX(values), AVERAGE(values), COUNT(values), COUNTA(values), or COUNTALL(values).

Removing blanks or duplicates

Sometimes you want to remove blank values or duplicate values from the rollup:

To remove blank values from a rollup result, nest ARRAYCOMPACT(values) inside an aggregation formula.

To remove duplicates from a rollup result, nest ARRAYUNIQUE(values) inside an aggregation formula.

You can nest these functions together: ARRAYJOIN(ARRAYUNIQUE(ARRAYCOMPACT(value)))

Data type of a rollup

A rollup can have one of three data types.

number. This is the data type if all possible results of the formula are numbers. The configuration screen will have options for formatting the number. In the grid view, numbers are right aligned.

date. This is the data type if all possible results of the formula are dates. The configuration screen will have options for formatting the date.

text. This is the default data type. If any possible result of the formula is text, including an empty text string "", the end result is text. This data type does not have any formatting options. In the grid view, text is left aligned.

Order of items in a rollup

The order of items in a rollup is determined by the order of the linked records in the linked record field. Normally linked records are added at the end of the list of linked records; however, the order can be changed.

Methods for changing the order of linked records:

Manually dragging linked records around in the linked record field.

Batch update extension.

Sort linked records extension.

Scripting and automation scripts.

Note that sorting linked records in their own table has no impact on the order in which records are linked.

Rolling up dates

When rolling up a date field, you can use MAX(values) and MIN(values) to get the earliest or latest date. These formula functions normally only work with numbers and not dates in a formula field. However, they do work in a rollup field.

Common rollup formulas

Making lists

Comma separated list

ARRAYJOIN(values, ", ")

List with each item on its own line

ARRAYJOIN(values, "\n")

Bullet list

IF(

COUNTALL(values),

"- " & ARRAYJOIN(values, "\n- "

)

Checking for duplicates

IF(

COUNTALL(values) = COUNTALL(ARRAYUNIQUE(values)),

"All values are unique.",

"There are duplicate values."

)

Default value

A default value is the value when there are no linked values.

IF(

COUNTALL(values),

"default value",

ARRAYJOIN(values, ",")

)

Override value

An override value is a value in an editable field that is used instead of the calculated value.

IF(

{override field},

SUM(values)

)

Things rollups cannot do

Rollups cannot perform different operations on individual values. For example, you cannot add 10 to each number before finding the average. You can usually perform that math in the individual records in the other table and then rollup the formula result.

Rollups cannot easily obtain only the “first” value. It is possible to obtain the “first” value using complex formulas, but is rarely worth it.

A single rollup cannot sometimes be text and sometimes be a number. At best, you can have a number that looks like text.

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.