Use one table to filter another?

I have two simple tables: Project Categories and Projects, as so:

What I’d like is to have the Projects table filter based on which Project Category has been clicked on.

I anticipate this is possible (and perhaps not that difficult). But I’m jussssst getting started with Coda functions, and haven’t yet figured out their full ins/outs.

Anyone can help me figure out how to accomplish this?

Thanks,
Matt

Hi @Matthew_Shane

Here is what I suggest.

I re created a fake document as yours :

Of course the “Choose Categorie” in your project table is a lookup to categorie table.
I suggest you create a select control, with these option. Thats the tool we will use to filter your project table

Then in your category table, create a button that will change the control

And filter your project table like this

CPT2212031255-754x647

Et voila !

Please find the doc and let me know what you think

Cheers

Quentin

1 Like

Awesome!

Thanks so much!

I looked at your doc, but didn’t copy it; instead reconstructed your pipeline within my doc, so that I could learn a bit more about how it all works. Super straightforward, and I should be able to use that skill from now on - so thanks again!

Onnnnly thing: This works for every Category except for “All projects”. For that one, of course, I don’t want a filter at all. I tried changing the button formula for that row - but it seemed to change all rows together. Is there a way to make the “All Project” button an unfiltered view?

Thanks again!
-Matt

Hi @Matthew_Shane ! This is a very good reflex. When I (often) post an embed, this is not to give you the finished document (otherwise I wouldnt explain at all :wink: ) but this is just to help if you’re stuck within the process.

You may have seen that I considered the “all project” case with two configurations :

  • the button “reset control value” that put the select 1 control to “blank”
  • the formula in the project table, I use If function, only if the control wasnt blank, otherwise I just set a dummy “1=1” thats always true, and then there is not filter at all.

Have you seen that ?

Cheers

Q.

Yes, I do see that. And that’ll work if need be. But I’d prefer if I can just click on the “All Projects” button, and have all projects show. Is this possible?

I cannot see the ‘All project’ button you mentionned, so In the embed doc I created a button “all project” in the category table directly, with the same formula

With this project filter

We do reach what you want, dont we ?

CPT2212032254-1001x773

Yup! Thanks!

I was trying to change the formula of the button itself (ie. the Select 1) instead of the filter formula. Will try to remember this moving forward.

Thanks for your help and patience!
-Matt

No problem of course :wink:

I modified it directly in the shared document, so that you’ve got access !
Do not hesitate for any further question you may have :wink:

Well, if you really don’t mind the queries: I don’t actually understand how changing from !=" " to !=All Projects led to this fix. How are the other columns still filtering by their respective row if the filter is set to All Projects?

  • the select 1 control dont mind, it takes “Finance”, “Manufacturing” or “All Project” as category.
  • So, if we go step by step for the filter of your project database

With the if function, we just want to deal with actual categories, that are NOT “All Project”. If they are different, we ask the project to be filtered with category that match the select control. If the selec control = ‘All project’, then we say a dummy equality so that the project table wont filter anything

is that more clear ?

1 Like

Sigh…I’m so bad at this stuff.
Have always wanted to be a good coder, but it just never took.

Lassssst request, so that I can finish off this page:

Note in my first post/image I have the number “40” beside “All Projects”. That’s intended to be a count of all of the projects that fit under each category.

I’ve been grappling with the count formula for 45 minutes, trying to get it to work. Tried just about every combination that I can think of…but I just. can’t. get. it. to. work.

Any chance you’d be willing to offer me this last bit of help?

Thanks in advance…
Matt

The super power of coda lookup :wink:

Just create a column in your category table to make the reverse lookup link with the project database.
And then you’ve got access to full information from project database, for example the count of “project Name”, which is the count of project per category that you wish :wink:

CPT2212040018-1518x521

Hey hey… I already know that you will think “that works for everything except for the row “All Project””. Yes ! because none of your project have the “All Project” Category :wink:

Then you can just create a new column with this formula to handle this , like we did before, and then show for each category the good count, and for “All project” the total count

1 Like