Difference between to thisRow and column name?

#1

This issue is somewhat related to what @nigel posted about in this thread. I am confused sometimes as to why thisRow works vs. the column name when referencing column names in formulas. In this table below, I have a summary table at the top that sums up all the costs by person, and the table below shows all the individual costs:


The formula makes sense to me since you are comparing Name from the Detailed Costs table with the People column in the Summary table. Both People and Name are of the People column format.

If I change the formula to use thisRow instead of the column name, the formula doesn’t work anymore. I feel like it should, any ideas why?

Frustration with Coda : Not enough User Friendly / Rows References are Tricky
Matches formula cannot match to a lookup field
#2

Al,

thisRow returns you a reference to the current row value. It’s mainly uses when you want to compare whether another column contains a reference to the current row. You can also use it to pick another column from the current row - ex: thisRow.column. From your example, are the columns People and Name of type People? i.e. a reference to the document’s people table? If that’s the case then your Summary table formula for Costs should be:

[Detailed Costs].Filter(Name = thisRow.People).Cost.Sum()

i.e. using thisRow.People vs. thisRow. The reason is that the values stored in [Detailed Costs].Name are @refs to the People table, not references to the Summary table.

Nigel.

3 Likes
#3

@nigel Yes both People and Name are the People type. In this example, since the Display Column on the Summary table is set to the People column, should thisRow return the People column automatically (i.e. you wouldn’t have to reference the column as thisRow.People)?

#4

@Al_Chen, the display column sets the display value of the row but does not affect the underlying type of the row reference value. Think of it a bit like an envelope tuple with values (tableId, rowId, displayValue).

In your example, since the displayValue is a people reference, a typical Summary row’s thisRow value would be something like:

(tableId = summaryTable, rowId = xyz, displayValue = @Al_Chen)

So if you take a formula such as [Detailed Costs].Filter(Name = thisRow).Cost.Sum() it’s going to be comparing Name = thisRow which translates to:

@Al_Chen === (tableId = summaryTable, rowId = xyz, displayValue = @Al_Chen)
→ #fail

which will never match. If on the other hand you use thisRow.People you’ll be comparing:

@Al_Chen === (tableId = summaryTable, rowId = xyz, displayValue = @Al_Chen)-->People
→ @Al_Chen === @Al_Chen
→ success!

Nigel.

1 Like
#ri-num and #r1 showing up as result to IfBlank(thisRow) evaluation. Why?
#5

Thanks for the detailed explanation about the strict comparison @nigel. Pushing this to the edge case, if I set the column type for the Name column in the Detailed Costs table to be “Lookup from table” where the referenced table is the Summary table, this also breaks the formula in the Summary table.

The reason one would do this is to limit the user to a unique list of values to select from in the Name column of the Detailed Costs table. This was the route I originally took when I created this template only to find that it didn’t work. The formula in the What They Paid column is the new formula as you described: [Detailed Costs].Filter(Name = thisRow.People).Cost.Sum()

Screenshot of the Name column type:

#6

Hi Al,

Linking [Detailed Costs].Name to lookup from Summary should work fine. You’ll need to reenter the values in the column to ensure they are references to the Summary table rows. Once you do this your Cost formula should be able to use thisRow = Name vs. thisRow.People = Name for the filter.

If that doesn’t work for some reason, I’m happy to look at your sample doc if you can share it with me (nigel@coda.io).

Nigel.

#7

Yup this works :ok_hand: So the reason why you can make the comparison thisRow = Name vs. thisRow.People = Name in this instance is because [Detailed Costs].Name is also a tuple (since it is a lookup of a People reference in the Summary.People column). Therefore we are comparing two tuples and when the displayValue of each tuple equal each other then the filter returns a match. Not sure if this logic is exactly right but makes sense in my mind :grin:

#8

Yep, you’ve nailed it! :slight_smile:

1 Like