How to display foreign key values in dashboard filter

phcollard · February 21, 2020, 8:24pm

Greetings.

My problem is quite simple, and I believe I can do what I am looking for with Metabase, but so far no luck in getting it working so I'm calling for help.

I have a simple DB to collect the time spent by employees on a given projet. It has only two tables:

cdt.project_code is a foreign key to project_desc.code

I believe my data model is configured as it should:

I have a dashboard that I was able to setup, that allows users to filter by project:

The dashboard works perfectly but users don't find it easy to look in to the dropdown for their project, when it displays only the dozens of project codes that we have in the DB.

So is it possible to have the filter dropdown display project names instead? I.e. project_desc.description

Thanks very much for your help!

flamber · February 21, 2020, 8:39pm

Hi @phcollard
You’re almost there!
Make sure you’re setting project_desc.description's “Field Type” as “Entity Name” (I have a guess that it has a Field Type of “Description”, which is why it’s not showing up)
Then you have the option to set cdt.project_code's “Display value” as “Use foreign key > Project code > Description”
Or at least, that’s what it looks like from the images.
Otherwise please show the Field Type of all fields in both tables.

phcollard · February 21, 2020, 8:52pm

Thanks so much @flamber

I changed the fields settings in the data model as you advised, I feel I am even closer now

But filter is still displaying the project code, here is a screenshot of one the questions included in the dashboard:

flamber · February 21, 2020, 9:27pm

@phcollard Okay, so you want to have a list of options and not a search field? I’m actually not sure why it’s not using the “Display value”, when selecting “A list of all values” instead of “Search box”, it seems like a bug, but I cannot find any issues about it. I need to investigate that a bit more.

A workaround: connect the filter to project_desc.description and if you haven’t already, then you would also need to join that table in your query. And then set that field to “A list of all values”.

flamber · February 22, 2020, 5:00pm

@phcollard I was sure that I had seen an issue about this, but took me a little time to find it:
https://github.com/metabase/metabase/issues/9490 - upvote by clicking on the first post

phcollard · February 24, 2020, 1:35pm

Thank you again @flamber. Upvoted.

alejandrovalde · December 17, 2020, 4:01pm

I was able to display foreign key values when using the filter inside a question (left side in image).
However, when I used the same filter in a dashboard I don't see the foreign keys. (right side in image).

Is this supposed to happen? Does it only work in dashboards when using a variable maybe?

flamber · December 17, 2020, 4:15pm

@alejandrovalde Different issue than this topic. It’s very difficult to know anything from your screenshot.
Post “Diagnostic Info” from Admin > Troubleshooting.
Which settings have you defined in Admin > Data Model for the column?
And which field is the dashboard question connected to?
Can you reproduce with Sample Dataset?