I am looking for the general principle of enabling a Dropdown List filter in a Native SQL question.
My Context:
I have a location_id column configured as a Foreign Key in the Admin Data Model.
The display value is correctly set to show the Location Name (Title).
When I build a question using the GUI (Query Builder), the filter works perfectly: it shows a dropdown list of names.
The Problem: When I switch to Native SQL and use a Field Filter variable mapped to the exact same column (location_id), the dropdown does not appear (it shows a text input or search box).
My Question: Is there a specific requirement or limitation for Native SQL questions to inherit the Data Model's "Dropdown" behavior? How can I force the SQL filter to show the list of values defined in the Foreign Key settings?
Must be less than 1000 unique values–Metabase won’t offer a dropdown (“list of all values”) if there are too many values or they take up too much space (100KB max). You can still use a Search Box if the table doesn’t meet this requirement.
Foreign keys must be defined in Table Metadata–Metabase will scan foreign key relationships for most databases, but make sure the FK relationships are correctly expressed. This is not necessary if the ID and name are in the same table.
Set semantic types in Table Metadata–The symbolic name to associate to the numeric ID must be set as semantic type “Entity Name” in the table the foreign key points to. For it to work in the same table, the ID must also be set as semantic type “Entity Key.”
Set filtering behavior–The Entity Key AND Entity Name columns must have Filtering set to “A list of all values” and meet the requirements of #1. If the requirements are not met, set the Filtering to “Search box” for both fields, and the name or ID can be searched.
Add a field filter attached to the ID field–If the requirements are met, when the filter is pulled down, the symbolic name will be displayed along with the ID number. If not, but the fields are set for search as in the second part of #4, a search box will be offered that can search by Entity Name or Key.
Attach an ID filter in a dashboard–If #1-#5 are followed then a dashboard filter will have the same capabilities.
Foreign key lookup is not supported for all databases, in particular the Sample Database.
There’s also an implicit requirement that the key is a numeric type and the name is a string type. People sometimes run into problems if they are using domains in their database–Metabase doesn’t understand domains and assumes the values are opaque, which disables any special handling. You may have to use Table Metadata’s cast feature or a view to cast the domains to a base type.