I am trying to answer if there is a simple way to filter the choices in a select list that references another table. I know there are a lot of older “work arounds” to solve this but I feel like I am missing something. When I add a filter to the the place where it says “filters” when I edit it, it offer some suggested filters or allows me to create my own filter. I have tried things like filter for car color based on car color column equal to car color column in the other table. No matter what I do it returns nothing. The only way it seems to work is with the build in formula maker but they only provides a static choice.
If it is is doable in the formula without creating a bunch of work around columns I would love any links to any posts showing this. Otherwise it would be great to get a confirmation answer that no it is not possible in a simple way.
I want to stop looking for an obvious solution that doesn’t work. Here is the same question: Filtered Lookups? - Ask the Community - Coda Maker Community.
It is not a critical enough field to build a work around but I am spending hours just trying to clarify it is not possible. And if it isn’t possible, I am curious why putting in a basic formula or filtering fails to show any results even though the formula is accepted.
Happy weekend everybody.
1 Like
It’s totally doable, and you found the right place to do it: Selecting ‘Custom filter’ within the ‘Option settings’ section. You just have to write the same formula that you would write within filter(). No need for any workaround.
For example if Table.filter(condition=true AND number>0) returns the options you want in the canvas or in a column, if you configure the ‘Custom filter’ to be condition=true AND number>0 it will make those options selectable.
Hope this helps,
Pablo
2 Likes
Ok, well at least I am not losing my mind as I thought I did this before. No matter what I do, it doesn’t seem to filter out any thing. Grateful if you could see if anything jumps out here to you in my formula.
The table on the left is where I am pulling from and then I am saying filter the contact database to show any row where CompanyC=Company M (on the mm2 database). I tried changing everything totext() or trying contains but no luck. You can see it is not working because the output is a field where everyone unique option starts with the name of the company. So all results here should only show TCL.
1 Like
Read again my answer more carefully 
At the moment your formula returns a list of items. In this context, empty means false and not empty means true, so that’s why it’s true for all rows and therefore everything is selectable.
2 Likes
In the filter part of a relation the filter is already applied… you have to pass the condition as @Pablo_DV said. So eliminate the filter part and usually instead of = is better to us e contains.
1 Like
Actually, I started with the companyc=companym and it failed which is why I came to the forum. I think I discovered the problem and will sort it out later. The issue is that it doesn’t just match the text but is reading the linked relations. Not to make it too confusing but I made a mistake early since I was copying the data from somewhere else and have too many company relations. For example, if an objective of a project is to find someone from Company A. Then the sub tasks on another table could be named approach contact 1, approach contact 2, etc. Then on this table I could choose the contacts from a master contact database. But what I inadvertently did was say name the task as the company name so when trying the filter it was filtering by a bad column. I am not sure exactly what caused the problem but can see it is flawed. So will fix the setup first then retry it.
1 Like
It ended up working as soon as I realized that I cannot have the filter by a column referencing a column that is a table column where the user chooses from another table. It has to be a value either manually or automatically calculated for it to work properly. Hope it helps others too. Thanks @anon1959570 and @Pablo_DV for confirming quickly that it should work as this pointed me in a new direction to consider and helped me figure out my design flaw.
1 Like
That’s not correct, anything that works within a normal filter() formula will work here too, including relation columns selected manually.
1 Like
