Skip to content
Searching in lists
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
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
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 tableAs 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
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
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
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))) // trueTasks.Filter(Tags = List(product,strategy))) // true
Tasks.Filter(Tags.ContainsOnly(List(product,strategy, strategy))) // trueTasks.Filter(Tags = List(product,strategy, strategy))) // false
but...
Tasks.Filter(Tags.Unique() = List(product,strategy, strategy).Unique())) // trueWant to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.