How to Use a Formula to Provide Reference for a Lookup Column / Joining Multiple Tables with Lookup Columns

1

I have two source tables. The second table uses the first to identify its rows. (Basically this is the “toy” subset of my intent of using one identity table and as many smaller details tables as I need to relate more data to it later.)

Now, I want to create a table that brings these two source tables together. It’ll be a Natural Join as one of the source table’s rows are identities on the other. Lookups are the Coda way of doing this. I setup a Lookup column for the first source table and could pull referenced columns just fine. Now I set up the second table’s Lookup column, and thought I could just use a formula to reference to the first table’s relevant row with thisRow. It doesn’t throw me any errors, but simply doesn’t work.

Surely, this is the most intuitive way to Join tables in Coda? I can’t possibly manually pick the correct reference for every single source table I’m eventually going to create. What am I doing wrong?

Here’s the example table with more detailed writeup:

2

welcome to community @Sayantan_Chaudhuri - we are excited to have you here.

Not entirely sure you are trying to achieve here, but might and agility on composite table is looking up identity table that doesnt have might and agility columns

I would think your vitals column on Composite table should be vitals.Filter(vitals=thisRow.identity) to pick up correct row from vitals (assuming there is a single row for each identity row) and then might on composite with formulathisRow.vitals.might would work.(like the way you have already)

e.g

1 Like
3

That is exactly what I want to do! I can even add other referenced columns through the + menu on the table and they work too!

So that’s how we can use formulas to provide references to rows; can’t just link to an already Looked up column. Lesson learned.

However, to clear out one small doubt, on my table might and agility are still looking up the vitals take are they not? I added them through the + menu as well, didn’t manually type in the formula through identity. It even shows thisRow.vitals.might etc. Why do you say they’re looking up the identity table instead?