Filtering Selectable Options in a "Lookup from table" Column

So I found the solution to this while writing up the question. Figured I’d leave it here in case it helps others.


Problem:

I have two tables: projects and people.

People table looks like:

Projects lets me assign a writer and a designer to projects via two columns that are set up as “Lookup from table”:

I don’t want to see Clair in the options in the designer column though (or Alice in the writer column). I was trying to find a way to filter the lookup results so that I only see options where [Designer?]=true.

Unsuccessful solution:

First I created a reference table that filters to just designers, and tried to lookup to that instead. No dice, you can only lookup to master tables, not reference tables it seems.

Successful solution:

Change the column format to be Select List, and set the selectable options to be “People.Filter([Writer?]=true)”.

I initially discounted this solution, assuming that while I’d successfully filter to the right people’s names, I wouldn’t get that sweet sweet hover action (where you hover on the assigned person’s name to see the linked data from the people table in a popup). I was wrong, and you do.

I think it’s because when you filter the people table to a certain set of rows, and then choose one of those rows, you’re still truly selecting a row (with all its associated data) not just text. Cool.

7 Likes

Thanks! I was looking for exactly this, and kept beating my head against the wall.

However, the hover action isn’t there.

It’s weird because the hovering action works just fine in the formula editing box, and the hover action also works great when you’re selecting from the drop-down list itself, but it doesn’t work when you just hover over the field.