Skip to content
Gallery
Tutorials
Searching in lists
Data
More
Share
Explore

icon picker
Searching in lists

Some extended explanation and use cases about In() and Contains*()
FS
Federico Stefanato

⁠
In()
⁠
(OR)

What

Searches for a single value in a list of items.

Use-case

Archive all tasks that are not open.
Create a new button that add a new row in the
Archived Tasks
⁠
table if the task is in a set of predefined statuses and removes them from the
Tasks
⁠
table
Current
4
Task
Status
1
Strategy Plan
To Do
2
Call Customer
In Progress
3
Hire Sales Manager
In Progress
4
Trip to Mars
Won't Do
5
Trip to the Moon
Done
There are no rows in this table
⁠
⁠
Archive Tasks
⁠
Undo
⁠
⁠
Archived
1
Task
Status
There are no rows in this table
⁠

Formula

Formula (explained)

As you can see, it can be a common shortcut for chained OR conditions, i.e.
But it’s also a good way when you don’t know how many elements you have. For instance:

⁠
Contains()
⁠
(OR)

What

Searches for a list of values in a list of items.
It returns true if any of the values is found, therefore can be considered as a OR filtering operator

Use-case

Filtering rows with multiple columns value over a list of possible values.
Find all Tasks that are ragged with urgent or strategy
Urgent OR Strategy
4
Task
Tags
1
Strategy Plan
product
strategy
internal
2
Hire Sales Manager
sales
urgent
hiring
3
Trip to Mars
product
strategy
4
Trip to the Moon
product
strategy
No results from filter
⁠

Formula

⁠
ContainsAll()
⁠
(AND)

What

Searches for a list of values in a list of items.
It returns true if all the values is found, therefore can be considered as a AND filtering operator

Use-case

Filtering rows with multiple columns value over a list of all required values.
Find all Tasks that are tagged with product and strategy (other tags are ignored)
Product AND Strategy
4
Task
Tags
1
Strategy Plan
product
strategy
internal
2
Trip to Mars
product
strategy
3
Trip to the Moon
product
strategy
No results from filter
⁠

Formula

⁠
ContainsOnly()
⁠
(AND and only...)

What

Searches for a list of values in a list of items.
It is a particular case of ContainsAll(): it returns true if all the values is found and are the only values of the list; it is somehow a strengthened AND operator.

Use-case

Filtering rows with multiple columns value over a list of possible values.
Find all Tasks that are only tagged product and strategy (and no other tags)
Only Product AND Strategy
4
Task
Tags
1
Trip to Mars
product
strategy
2
Trip to the Moon
product
strategy
No results from filter
⁠

Formula

Note that this can be misleading for list equality: we should interpret as “the elements of the values are the only one in the list”.
However, lists can have duplicate items; so look at the different scenarios:

In() (OR)
What
Use-case
Current
Archived
Formula
Formula (explained)
Contains() (OR)
What
Use-case
Urgent OR Strategy
Formula
ContainsAll() (AND)
What
Use-case
Product AND Strategy
Formula
ContainsOnly() (AND and only...)
What
Use-case
Only Product AND Strategy
Formula
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.