Grouping records in Airtable
  • 02 Jun 2023
  • 4 Minutes to read
  • Dark
    Light

Grouping records in Airtable

  • Dark
    Light

Article Summary

Airtable's grouped records feature allows you to show your records grouped together based on one (or more) fields of your choosing. You can also use grouped records to generate reports—see this support article for more information.

Introduction

Plan availabilityAll plan types
Permissions

Owners/Creators/Editors

Platform(s)Web/Browser, Mac app, Windows app, and mobile apps

Understanding grouped records in Airtable

Grouping records in Airtable allows creators to build a set of one or more conditions that will help to categorize the dataset that is being presented within a particular view. Common examples include grouping by:

  • Single select field - Status, category, project, etc.
  • User field - Individual or group (Enterprise only)
  • Linked record field
  • Multiple select field - For grouping records where multiple selections overlap to create a matching group
  • Date/Date range

Multiple conditions create deeper categorization by adding collapsible subgroups within each group of records. When combined with other options such as record filtering or sorting, this allows you to create hyper-specific organization and insight into the records contained within a set of data.

Getting started with grouped records

Creating a group

To group your records, open up a view and click on the Group button located between the filter and sort buttons. Select from the suggested fields or click See all fields to choose from all available fields.

Adding one or more subgroups

Once you've selected the field you want to group by, groups will automatically be created based on the unique values in that field. New groups will similarly be added automatically whenever a new, unique value is entered in the designated field. You can also group your records by multiple fields by clicking the Add subgroup option.

Additional group configuration options

Now that you have one or more groups set up there are some additional options to consider:

  1. Collapse or expand all of the groups in the view
  2. Change the field referenced in the group or subgroup by selecting from the dropdown options
  3. Change the sorting order of a group or subgroupby selecting from the dropdown options
  4. Choose whether to show or hide groups with no records
  5. Delete a particular group or subgroup
  6. Drag a subgroup to move its order of grouping

Dragging between groups to reassign values

You can change the values on a record in a group by clicking and dragging the record to a different group using the drag handle on the left side of the record. For example, you could drag a task assigned to one person to another person's group, and that task would automatically be re-assigned to the new person.

drag-to-reassing

Note that you cannot add new records to a grouping that is based on a computed field type (like a lookup, rollup, or formula field).

Grouped records and the summary bar

There is a summary bar at the top of every group which can summarize the cell values in numerical fields by each group. These summary bars let you see the subtotals of each grouping. Every grouping will also have a count of the number of records in each grouping.

image.png

Much like the summary bar for the whole table, the summary bar for each group can provide different summary functions.

image.png


Grouping on a date range

If you want to group your records on a date range—for example, to see every transaction record that was made by week, or every deal by the quarter in which it occurred—you can create a formula field with DATETIME_FORMAT() and group on that formula field.

By using different format specifiers in your DATETIME_FORMAT() formula, you can group by day of the week, week of the year, month, quarter, and more.

Alternatively, instead of using the DATETIME_FORMAT(), you can also use the preset YEAR(), MONTH(), WEEKDAY(), or WEEKNUM() functions. You can read more about these functions in the formula field reference article.

Note

If you choose to group on a date field that includes a time field, you will group by both the date and time. If you aren't looking to include the time in your grouping, we recommend creating a separate formula field to parse just the date and grouping by that field instead.

Example 1: Grouping records by week

Let's say that you have a table of sales conversations, and you want to be able to group the outreach records by week in order to get a sense of how much outreach you've done each week for the past few weeks.

image.png

If you try to group on the Date field, only conversations that occurred on the same day will get grouped together. To group on the week instead, you'll need to make a formula field using DATETIME_FORMAT(). DATETIME_FORMAT() requires you to specify a date field (which we already have) and a format specifier, which determines the output of the formula. (You can find a full list of our supported format specifiers on this page.)

Since you want to group on the week of the year, and the format specifier for the week of the year is 'w', you write this formula:

DATETIME_FORMAT(Date,'w')

image.png

Now, you can group on the newly created Week No. field to see all the outreach events grouped by the week in which they happened:

image.png

Example 2: Grouping records by year and quarter

Let's suppose that you have a table of closed deals spanning multiple years, and you want to have some way to see the total size of the deals made by quarter AND year.

image.png

To create a DATETIME_FORMAT() formula field that groups on quarter, use this formula:

DATETIME_FORMAT(Date,'Q')

However, if you just group on this new field, records from different years (but the same quarters) will get grouped together. The solution is to create a second formula field for the year:

DATETIME_FORMAT(Date,'YYYY')

image.png

If you create two layers of grouping (first grouping on Year, then grouping on Quarter), you can take advantage of the grouped record summary bar to get valuable data insights.

image.png

image.png


Was this article helpful?