When you group a table by a column that is a select list, and you also have "allow multiple selections" enabled on that column, you'll notice that each group is defined by unique combinations of items, rather than each unique item.
For example, let's say you have a doc for organizing your home movie collection. You can find the doc used for the example here (it's view only, but you can create a copy by clicking on the title of the doc and selecting "copy doc").
You've added multiple tags on each movie:
You then want to see all the movies for each tag. You create a view of the Movies table and group the view by the Tags column. Each group is based on a unique combination of tags, not the individual tags themselves:
You'll notice that the drama tag is repeated several times, each with a different combination of tags. This is useful for a lot of scenarios, but in your case you'd like to see each tag as a separate group.
To do this, you'll want to follow 2 steps:
Step 1: Convert your
select list column type into a
lookup from table column type, and create a new Movie Tags table:
Navigate to the column type icon on the Tags column and change it to
Lookup from table . Next, select the table to lookup from, and choose "+ Create new table".
Note: The newly-created table shows up in a brand new section so it's easier to find.
Step 2: Add a
FILTER formula to the Movies Tags table to look up all the movies that contain that table:
Navigate to the newly-created Movies Tags table and add a text column. Input the following formula to look up all the movies associated with each tag:
And there you have it! You can now see each tag on a new line, with all the associated movies.
You can also create a subtable in the Movies Tags table by doing the following:
- Remove the
.BulletedList()from the end of the formula in the second column
- Pop open one of the rows in the Movies Tags table
- Click on the pencil icon in the top right of the screen
- Make sure the "movies with this tag" column is in the visible section
- Hover your mouse over the "movies with this tag" column and select the toggle "display as table"
- Click the pencil icon again to close the edit layout mode. You'll notice that the movies are now listed in a table rather than just a list.