Filter a list based on matching the corresponding info in another column

1

I’m sure I am missing something basic here, but this is what I’m trying to do.

1st scenerio:
I have 1 table with a list of clients and another table with a list of projects.
When I enter a new project, I use the table lookup to select which client that project is for. On the client table, I would like to have a column that pulls in which projects are attached to that clients name. As it is, I can pull in the list and manually select which one is for that client, but it seems like I should be able to filter that list of projects based on the client name. Is there a formula for that?

2nd scenerio, which I assume would utilize a similar filter:
I have a table of purchase orders and a table of vendors. The vendor is selected from a table lookup list. On the vendor list I would like to pull in a bulleted list of just the purchase orders for that vendor.

2

1st scenario:
Make another column in Client Table called “Client’s Projects” and give it this formula:

 [Project Table].Filter(Client=thisRow)

This will give you a comma separated list of all of a client’s projects.

To take it a step further, you can wrap it in…

BulletedList(...)

…for your 2nd scenario.

And because Coda is great, this will be a list of links to the actual projects/purchase orders; you can hover to see info or click to edit in a popup.

1 Like
3

Thank you so much. So to make this work I had to change a couple of things.

1st - I had to make sure the column I was pulling the list into was set as Text and not a Lookup column. (because that was causing a circular reference)

2nd - I had to change the column that it was being pulled from to be a Lookup column. (I originally had 1 of the tables set up as a Select List with the formula doing the lookup.)

Follow up Question…
In these circumstances, the data being pulled in was the display column. If it was a different column, would I just replace [Project Table] with [Project Table.Status] or something like that?

Thanks!!

4

1st - I had to make sure the column I was pulling the list into was set as Text and not a Lookup column. (because that was causing a circular reference)

Yep that makse sense. AFAIK Lookup Columns are actually just a bit of a shortcut - you can get the same result (plus added customization like deciding you want a bulleted list) if you just start with a text column and write a formula that pulls in entire rows looked up (filtered) from another table.

2nd - I had to change the column that it was being pulled from to be a Lookup column. (I originally had 1 of the tables set up as a Select List with the formula doing the lookup.)

One thing to keep in mind with lookup columns:

  • What’s displayed in the lookup column is just the other table’s Display Column (as you said)
  • What’s truly in the lookup column as the underlying data, is the whole row from the other table (that’s why you can hover on it to see more details)

Filtering needs apples-to-apples comparisons. So if you’re trying to use Filtering, and you’re saying “hey does this column match this whole row from Client table”, the column you’re filtering has to contain a whole client record (which lookup columns do), not just the text of the name of the client.

Alternatively, you could say you want to do an apples-to-apples comparison between just the text of the client names. That’s ok too. You would set your column back to text, and instead of comparing that to a whole row from the client table, you’d say TheRowFromTheClientTable.ClientName. This pulls just the text of the client name, which you can legally compare to a text column when filtering.