Having difficulties understanding how filter works

Showcase
1

Hello,

I’m trying to do a simple filter in a table and I’m having difficulties in understanding why it doesn’t work.

Here are 2 tables. One is a list of tickets, and for each we can assign an problem using a lookup to the second table.

The second table, the list of problems, compute how many tickets are assign per problem, and propose a solution.

I want the solution to be displayed in the first table with each ticket as well, so I thought I could just use a formula with a filter to find the row in list of problem that matches current rows problem, but i doesn’t work. It outputs every row instead.

image
image1465×798 69.5 KB

The answer is probably very simple, but I’m stuck on this.

Thank you in advance for your help.

Mathieu

2

@Mathieu_BABEL

Here’s one possible approach:

List of problems.Filter([thisRow.Problem].Contains(CurrentValue))

1 Like
3

Thanks @Ander for the answer.

I finally found a solution very simple:

[thisRow.Problem].Solution

But I still don’t understand why the first formula outputs all the row of the list of problem table

Mathieu

2 Likes
4

I’m not really good at explaining in English but I’ll take a try.
Whenever you use the filter function you have to use currentValue.
The filter will then compare each value (currentValue) from a list (List of problem in you case) to something else (In your case, each row value from the column Problem- thisRow.Problem).
The way it is, your formula is not filtering any data, so it returns [List of problem].solution

5

Ok thanks, it’s very clear.
Indeed, using:

[List of problems].Filter(CurrentValue=thisRow.Problem)

works.

Mathieu

6

@Breno_Nunes, just as a clarification: while in this case it’s true, it is not the rule.

@CurrentValue is a convenient variable holding a reference to the context in several situations.
For instance

  • [List of problems].Filter(CurrentValue=thisRow.Problem): @CurrentValue here holds the reference of any of the problem of the list.
  • Sequence(1,10).FormulaMap(CurrentValue): here it holds the index of the iteration (1,2,3…)

Filter() formula needs a condition.
Tasks.Filter(Status != "done" && Priority="High"): you access to properties (columns) of Tasks object (table) to select the condition.
You could write it as: Tasks.Filter(CurrentValue.Status != "done" && CurrentValue.Priority="High"): as you can see, the CurrentValue is unnecessary.

But when you have to check the whole object, then you have to use it, like in the case of this post, as you correctly pointed out.

I hope this clarifies better.

2 Likes
7

Hi, @Federico_Stefanato .
You definitely don’it need to write down ‘CurrentValue’ every time, but you have to understand that it’s there concealed.

coda current value
coda current value764×563 75.5 KB

Coda even shows you so when you are writing a formula. It’s the same thing for ‘thisRow.column’ that some times can be written as ‘column’ only.
Filter formula is practically useless without ‘CurrentValue’
Please, correct me if I’m wrong but that is the way I understand it.

1 Like
8

Hi @Breno_Nunes ,
You are right.

I think it’s just a matter of (mis)understanding.

CurrentValue is intrinsically part of the current context, even though you don’t explicitly refer to it.
And not only in filtering functions, btw.

I was just clarifying your statement

“Whenever you use the filter function you have to use currentValue”

because it might lead to misinterpretation: as a matter of fact you don’t have to every time.

Thanks for your insight.
Cheers!

1 Like