Skip to content
Tutorials
Share
Explore

Searching in lists

Some extended explanation and use cases about In() and Contains*()
(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
table if the task is in a set of predefined statuses and removes them from the
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
Tasks.Filter(Status.
In(
[Done],[Won't Do]
)
)
.FormulaMap(CurrentValue.WithName(task,
RunActions(
[Archived Tasks].AddRow(
Task, task.Task,
Status, task.Status,
Tags, task.Tags),
task.DeleteRows())))
Formula (explained)
Tasks.Filter(Status.
In(
[Done],[Won't Do]
)
)
// Take all tasks that have the status "Done" or "Won't Do"
.FormulaMap(CurrentValue.WithName(task,
// for each one, iterate and give the value the name "
task
"
RunActions(
// for every
task
we have, do the following actions:
[Archived Tasks].AddRow(
// 1. add a new row in the Archived Task table...
Task, task.Task,
// ... by setting its values from
task.Task
Status, task.Status,
// ...
task.Status
Tags, task.Tags),
// ...
task.Tags
task.DeleteRows())))
// 2. remove the
task
record on the Task table

As you can see, it can be a common shortcut for chained OR conditions, i.e.
Status=”Done” OR Status=”Won’t Do”

But it’s also a good way when you don’t know how many elements you have. For instance:
Status.
In(
Statuses.Filter([Open].Not())
)
(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
Tasks.Filter(Tags.
Contains(
urgent,strategy
)
)

(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
Tasks.Filter(Tags.
ContainsAll(
product,strategy
)
)

(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
Tasks.Filter(Tags.
ContainsOnly(
product,strategy
)
)

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:

Tasks.Filter(Tags.
ContainsOnly(
List(product,strategy
)
))
// true
Tasks.Filter(Tags
=
List(product,strategy)))
// true

Tasks.Filter(Tags.
ContainsOnly(
List(product,strategy, strategy
)
))
// true
Tasks.Filter(Tags
=
List(product,strategy, strategy)))
//
false

but...

Tasks.Filter(Tags.
Unique()
=
List(product,strategy, strategy).
Unique()
))
// true

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.