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
Search
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
Search
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
Search
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.