How to use "this row" formula to fill a column with referring/linking it to another tableanother

[VINAY_B_C]

I want to know to which team a given task belongs to in the “Raw data June - 2022” table belongs to, this has to happen based on the mail id of the assignee.
the details of the assignee and their teams is shown in the table ‘Assignee details’.

Please share the formula to do the same.

The link to the CODA DOC Copy of METRIC TRACKER - ASANA TASKS

[Paul_Danyliuk]

Hey @VINAY_B_C, here’s the formula:

You have to find a row in the Assignee Details table that corresponds to the current email:

[Assignee Details].Filter(
  [Assignee Mail] = thisRow.[Assignee Email]
).First()

(use .First() to limit to one result and avoid getting a list-of-one)

and then read the .Team column out of that record.

[VINAY_B_C]

Hi @Paul_Danyliuk thanks for helping out. The formula you shared works for me.
I was looking at whether its possible to get the same result by making changes to the formula and found that the formula works even without the “this Row” function. Can you please explain how not having “the row” function in the formula might affect the results.

[Paul_Danyliuk]

thisRow is implicit in that case — the reference is still thisRow.[Assignee Email]:

Just like [Assignee Mail] is implicitly CurrentValue.[Assignee Mail]:

It’s okay to do it like this but be careful. It’s a very common mistake with beginners to use e.g. Assignee Email without thisRow thinking it would always check on this row. However if you had a column with the same name in your Assignee Details table, both would implicitly resolve to CurentValue.[Assignee Email] and your filter would always be true because it would resolve to the same values every time:

or actually, you’d have that choice — whether to use thisRow or CurrentValue — but it’s so easy to miss:

Color coding to the rescue, too. See how items from the same table have the same color and from different tables different colors.

Bottom line: I suggest you actually use thisRow and CurrentValue explicitly until you get an absolute confident grip on the formula language.