How-to? Selecting row from another table in formula

[Carl_Haugen]

It’s driving me crazy that I can’t figure out this simple formula.

The picture below show what I want to duplicate in a new document. I want to add a value like “Done” from the “Status”. The “Status” column of the All Tasks table reflect the lookup values from the Status table depending on the Due dates as show in the example.

I just need to know how to get that pink part of the formula. Why is this so difficult? I can’t find any row command or similar that get the specific row value from the Status column in the Status table.

[Pch]

Hi @Carl_Haugen ,

Have you tried to reference directly the row Done using @ ? (as I guess this is how it was done here)

Literally typing in your If(): @ + Do…

The @ should open a menu allowing you to select a specific row in a table (and in this case, the specific row Done in the table Status)

Another way (among others) to get the row Done from the Status table in your If() formula would be to use the classical Filter() formula .

E.g.: Status.Filter(Status = "Done").First()

[Carl_Haugen]

Thanks Pch.

Unfortunately I can’t reproduce this. I get no popup except for the standard IF build popup helper if I use “@” in replacement of the old row value as shown here.

If I use the @ symbol in a blank formula, I can just select people in the document. Does this have anything to do with the type of column? It’s set to a lookup column, and I’ve chosen the Status table as the lookup table. As it was originally. Can’t see any different behavior with lookup or text columns.

The second suggestion work, but it wont pick up the formating from the Status table, as it did in the original formula. So it’s not ideal, but it could work.

I could use this suggestion, but I know I have to use this row value trick in other parts of our document

[Pch]

@Carl_Haugen

I get no popup except for the standard IF build popup helper if I use “@” in replacement of the old row value as shown here.

You need to type at least the first letter of the row you’re trying to reference .
So, in this case @d should be enough to open the menu.

@ref a row

In the short screen recording above, I used a simplified setup and If() formula.

There are just 2 tables :

• The table Status (at the bottom) containing the statuses Done and Not Started

• The table Table () with only 2 fields :

  • The single-select Lookup field Status linking the table Status to the table Table and gathering the correct status based on the result of the If() formula.
    (The If() will only return Done if the number in Number is equal to 1. Otherwise, it will return Not Started)
  • The field Number simply there to conditionally select the appropriate Status

You should see that typing @ is not enough to trigger the menu …

But, if you add the 1st letter, Coda does a few things :
The menu where you should be able to select a row appears and a row is directly auto-suggested .

You can navigate between the suggestions either using your mouse or your keyboard and then once you’ve found the row you were looking for you can either click on it with your mouse or use ⇥ Tab

So, that’s for the @Ref of a row

Now, for the Filter() formula, I can tell you that you didn’t write the same formula I suggested in my previous reply
(This is on me and I’m sorry … I should have been more precise )

@Pch

E.g.: Status.Filter(Status = "Done").First()

Here’s a screenshot of the If() formula I used in my sample using the Filter() formula just above for the True part of the If()

As you can see, I’m filtering the table called here [Status 2] to find the appropriate Status and not the field Status.Status as you’ve done in your filter formula .
The 2 formulas are different as they won’t give the same results.

Let me explain … or at least try to :

To get the row Done from the Status table using Filter(), you need to understand that a table is just a list of rows and that each specific value in the various fields of that table for a specific row in that table is “stored” as a CurrentValue.
The Reference for a specific row in a table is the value of the field you choose as the Display column and acts somewhat as a landmark to pin point a row in a table. It’s the value allowing you access all the other values for that specific row (Sorry, this is not easy to explain )

That said, as we’re looking here for (a reference of) a row for the lookup field Status, we need to filter the whole table Status.

In the screenshot below, you’ll see that by just selecting the table [Status 2] in my If(), there’s a small icon at the right of the 2 row references Coda is giving me as a result of my formula…
By hovering that small icon, it tells me that the result will be a List of rows

So now, we just need to correctly filter the table :

  [Status 2] //Table [Status 2]
    .Filter(
      Status = "Done" //CurrentValue.Status
    ).First(),

So, we take the table called here [Status 2] which returns, behind the scene, a “first” list of rows.
Then, among all those rows (where, as I said above, all specific values are stored as CurrentValue) we ask the Filter() to only keep the row where CurrentValue.Status is equal to Done.
This outputs a “new” list of rows… and to completely isolate the correct row in this “new” list of rows, we add .First() (so the Filter() actually returns a row and not a list of rows)

By using Filter() on Status.Status you’re actually not looking for a specific row but accessing directly the current text values in the field Status which later won’t return a row but a simple text value (as shown by the small icon at the right of the results in the screenshot below )… Hence, the not working formula … (There are other stuff going wrong too, but that’s not the point )

And just in case, here’s the quick simple I used for the screenshots (and Co.) :

In the table [Table 2], you’ll find 2 lookup fields :

1. Status 2 returning the row Done
2. Status 3 returning the text value Done

So you can compare both formulas

I hope this helps !

Don’t hesitate if something is not clear, it’s been a while since I’ve tried to help and explain stuff here so I’m a bit rusty (as well as my Coda, generally speaking) …

[Agile_Dynamics]

Pch,

what a clear and well illustrated reply!

exemplary, well done. it inspires me to improve my own explanations on this forum.

respect
max

[Carl_Haugen]

Thanks for the great explanation! You did very well Thanks! This will help me a long way to make more complex formulas.

I finally figured out my problem. There is a way to directly reference values in another table, as was shown in my original screenshot. I really thought I did this in my testing, but I suspect that I failed in selecting the right lookup table or something.

Your solution would work perfectly well, @Pch. However, this is a much simpler solution and an answer to my own original question

Solution:

• Make sure you have set the column to a lookup column
• Make sure you point to the table you want to pick the values from. Set under “Lookup Options” under the column setting.
• Use @ and name of the value you want in a formula of that lookup column.

You don’t really need to specify which column to use it seems. Probably because there’s only one column. It seems that this @ method for picking values does not work when you add a second column.

[Pch]

@Agile_Dynamics : Oh my… … Thank you so very much !
I’m really glad you can’t see me blushing right now … and more than happy to know this inspired you ! As always, I’m just trying my best here !

---

Ah ! I’m more than happy to know this could help you debug the issue you had @Carl_Haugen

@Carl_Haugen

Make sure you point to the table you want to pick the values from. Set under “Lookup Options” under the column setting.

This could have been a problem too, yes… But it’s something I couldn’t guess from the screenshots you shared so I assumed (wrongly… sorry ) that you check that already

---

I feel like I need to add some precisions though … So here it goes …

@Carl_Haugen

You don’t really need to specify which column to use it seems. Probably because there’s only one column.

This is not exactly the reason : It’s because lookup fields will only return either a Row or Rows (depending on how you’ve set up your look up field which can be either a Single select or a Multi select) from the table you wish to pick up value(s) from … And those row(s) will always be the value(s) from the field you’ve set as Display column (still in the table you’re picking up values from) … This is the only valid result(s) a lookup field will return .

So if there’s only one field in the table you’re picking up values from, by default that field is set as the Display Column and the lookup field will let you access the values from this field in the table where it is located.
But if you add more fields in that same table, the lookup field will still only return the values from your Display column (and nothing else).

That said, if you wish to also gather values from other fields through your lookup field into the table where that lookup is located, can do so by adding a related column (which is located somewhere down in the menu of the field).

Here, I chose the field Emoji in the options Coda gave me

Or you can create such a field yourself too …

@Carl_Haugen

It seems that this @ method for picking values does not work when you add a second column.

It works … but because of what I tried to explain earlier in this reply : Not directly in a Lookup Field .

In the screenshot just below, you’ll see that in my If() I @Ref the value of the Emoji field I’ve added for each of the statuses in my table [Status 3] .

Note that this formula here is completely independent from any lookup field(s) I could add though. It’s just a text field with a formula… As opposed to a lookup field which would create a bridge between 2 tables and link value(s) to each other(s) …

[Carl_Haugen]

Wow. I did not know about the “related column” option. That opens up some nice possibilities. Thanks for correcting me and bringing more value to the community. I appreciate that

[Pch]

My pleasure @Carl_Haugen !