Reference other columns after using the @reference

#1

This question came up after I saw @nigel’s post in this thread:

How you would reference other columns in the table that are not the display column once you’ve set the @reference? If I want to get the value in the Link column below I was thinking you could do something like @More Text.Link. My workaround has been adding in an ID column and using the FILTER() formula like so:

16%20PM

4 Likes
Frustration with Coda : Not enough User Friendly / Rows References are Tricky
#2

@Al_Chen I came here just now with that exact question!

If I have a Table of Contents table as a header with some metadata, how can I reference that metadata in the canvas without using =[Table of Contents].filter(....).data ?

My workaround is making a control item instead of tagging, then setting it to whatever value of the table I am looking to view.

#3

Have you tried the Lookup() formula?

@Al_Chen, in your example you could write either of these formulas depending on your syntax preference:
=Lookup(myTable,ID,2).Link
=myTable.Lookup(ID,2).Link

#4

You can also write =@Some Text.Link – key is to write the = before the @, then you’re able to dereference to pick a different column.

2 Likes
#5

Great question! There’s actually a (admittedly hidden) way to do this in the canvas WITHOUT a filter :scream:

In the canvas, try =@More Text.Link. The = makes all the difference! It’s similar to projections inside of a table - specifically if you have a column of references to another Table (or a Lookup formatted column), you can project values from that Table using =[Reference Column].[Column from the Table the reference is from]

How does that work for y’all? cc @chris_homburger

2 Likes
#6

@nathan The Lookup() formula works but that still requires you to know the ID of the row. The question I had was in reference to @nigel’s statement that you can use the @reference to pinpoint the row and then retrieve another column from that specific row. Looks like both you and @evan found the solution which is using the equals sign before doing the @reference :ok_hand:

@chris_homburger How does your workaround work with the control item? Could you post a screenshot with formula?

The concept of using the @reference to pinpoint a row and then pulling in the column reference could be an anti-pattern compared to a VLOOKUP() in Excel. When you have a long list of IDs/unique names and you need to pull in data for a summary table, you would type the handful of IDs/names in the summary table and then run VLOOKUP() to find associated data. With the Coda @reference used in the canvas, it feels like the user needs to be trained to disregard the ID and think about the unique value (in my case, thinking about “More Text”) as the lookup value. This means the first column–or whatever the “display” column is–should be truly unique be it a list of your friends’ names, team names in your company, etc. Then the user should be conditioned to reference the unique name in the formula vs. the row ID.

#7

Not sure if I am on the right place to ask :wink: how can I now do a simple lookup for additional fields (like vlookup) on another table?

tried this one, but it’s not working:
filter(Soll=Kontenplan.Bezeichnung).Gliederung
whereas Soll and Bezeichnung are both keys, Gliederung is a text in the linked table

Thanks for your opinion.

Best
Thomas

#8

Hey @Thomas_Schulz, if you try filter(Soll=Bezeichnung).Gliederung, does the formula work? Looks like “Bezeichnung” is the name of the table you are trying to output the values from the linked table. If you remove it from the formula it should work.

#9

I tried like this:
filter(Soll=Kontenplan.Bezeichnung).Kontenplan.Gliederung

Because “Bezeichnung” is the linking colum to the table “Kontenplan”. Just adding Bezeichnung does not work

#10

Gotcha, you don’t need to reference the table in the filter, just the column names. Do you mind taking a screenshot of your tables, or sharing your doc with support@coda.io? It will be easier to debug this issue for you!

#11

you have already access :slight_smile:
it’s in here https://coda.io/d/beeq-GmbH_dWOajJrCXL3/Bilanz-neu_suo-h#_luooz

#12

Take a look at the formula! I moved the “Kontenplan” table to the outside of the formula and applied the filter to it. What’s tricky is that the “Soll” column is a Lookup to another table, so I had to apply the ToText() formula to “Soll” to get just the text for this column. Here’s the final formula:

Kontenplan.filter(Bezeichnung=Soll.ToText()).Gliederung