Hello
I have a question set up with a field filter on a column that is a foreign key to a different table and is set up to display the entity name from that referenced table. In the question itself this filter works perfectly (looks like Foreign key's title - [small]local ID[/small] but when I add a dashboard filter it only shows the local ID.
I'll try and replicate this with the sample database and post images here.
n.b. we're running 0.42.0, I'm planning to upgrade to 0.42.3 but github claims it failed it's CI tests so I'm holding off on it. I checked the patch notes though and I didn't see anything that could fix this.
I've replicated this with the sample database and noticed another issue.
-
set orders' product ID column as a Foreign key to products' ID column
-
set the same column to display as foreign key -> title and filter = list of all values
-
Set the columns in the product table to these types
-
Set the title column in the product table to filter = a list of all values
-
Set the ID column to the same (otherwise the filter gives us a search box)
-
Make a query on the orders table, using the products column as the field filter
-
Filter looks as expected
-
Add to dashboard, add an ID filter to the dashboard and wire it up to the question: now the filter only shows the orders table ID and not the foreign key
- Now if you go back to the original question (not in the dashboard) its filter is also broken to show the non-foreign key version
- But if you force refresh (ctrl + f5) then the question filter goes back to working properly (the dashboard filter still is broken, and breaks the question's own filter any time you try to use it).
Also this same thing happens regardless of setting Title in step 3. to either type Title or type Entity Name.
I don't think it's the first issue, since that was one I also ran into, but it looks like the second issue is what I'm experiencing. I'll try the workaround.
By the way this issue does not happen when set to search mode, only list of all values.
The workaround posted sort of works, but if you refresh the browser it unfixes itself until you go back to the question. I'm not certain how fragile this is but I'll see if it's workable.
Thanks for the help.
@ametauser Since you're doing SQL, then you can just use FK reference from that for your Field Filter.
Hadn't considered that, will try.
I'm not certain I understand what you mean by use the FK reference for the field filter: it just complains about not having a from from that table?
@ametauser In your screenshot example, instead of selecting "Orders.Product_ID" as the Field Filter, then select "Products.Title".
You'll have to make a join, so the table is available:
https://www.metabase.com/learn/sql-questions/field-filters#creating-a-filter-widget-with-a-dropdown-menu
Ah right, that will work. Same logic as how you'd join to select the human-readable foreign key title if you wanted that in the output.
This suggestion worked perfectly, I should have just tried of this first: metabase's field filters are so powerful out of the box I forgot to use basic SQL functionality! Probably also means a subquery I turned into a materialised view is now unnecessary.
I think there's still either a bug or at least a weirdness to the fact that the question itself displays the dropdown items in one way and the dashboard doesn't but this workaround is perfect.
Thanks, as always, for your help.
By the way this also functions effectively as a workaround for https://github.com/metabase/metabase/issues/21382 as it lets you search for the remote value: you might want to add it into the github issue for anyone else who runs into it.
@ametauser I've created an issue for this:
https://github.com/metabase/metabase/issues/21528 - upvote by clicking 
on the first post
