Linked select list filtering: how to do "if [Column A] = blank, show all [B]; else show only [B] where [A] = [Column A]"

1

For my linked select list, I can’t seem to get this to work…

For my business, I have three tiers of connected items in my hierarchy:

  • Customer
  • Project
  • Item

Customers order Projects, which produce Items.

Each of these items is a table, and has linked relations to the other two. In other words: the Item column has a linked Project and Customer column, and so on and so forth.

What I want is for the Customer column to show ALL Customers in the dropdown … UNLESS Project is not blank, then it shows ONLY the Customer linked to that Project in the dropdown. Same in reverse: Project should show ALL Projects, UNLESS Customer is blank, then it shows ONLY the Projects ordered by that Customer.

Basically:
if [Project Column] = blank, show all [Customer]; else show only [Customer] where [Project] = [Project Column]

2

Hi @Swan_Song1 ,

Welcome to the community!

The problem with all tables related to each other is that you can end up with inconsistencies. For example, item A linked to Customer B and Project C, which in turn is linked to Customer D.

You will need to build logic to avoid/be aware of those inconsistencies that is not trivial, so I would avoid it unless it’s absolutely necessary for your use-case.

But anyway, to your question: Here you have the custom filter for the Customer field from the Items table. The green chip in the formula refers to the Project field in the same table and the Projects field in the Customers table.

image
image696×345 18.3 KB

Let me know if this helped!

Pablo