[0.29.x: DONE] Field Filter does not show Dropdown list


#1

Hi, I’m using Metabase v0.28.5 and I created questions in two ways: custom option and native query.

When I used the first option and added filters to my dashboard, they appear as a dropdown list. However, when I used the second option to create a similar question, the filter appears as a autocomplete, which is not the expected behavior, considering that the filter has only three possible options. The images below illustrate my point:

  1. Question created using custom option: “Gênero” stands for “Gender” in Pt-BR.

Field Filter Gender - Dropdown

  1. Question created using native query:

Field Filter Gender - Autocomplete

Why is this happening? Both field filters refer to the same column in the database, so both should be presented as a dropdownlist.


#2

Hi Lucas,

is this something that worked for you in previous versions (and is therefore a bug?) Otherwise it might depend on the syntax you wrote for the native query.
Can you share the syntax you used to create the question? For me (with mySQL database and therefore mySQL Syntax) the category filter field appears when I use a field filter like that:
[[and {{date}}]]
that is linked to a field.

When I use a variable like that:
AND sender_identifier = {{sender}}
that is not linked to a field but the variable type is Text, it appears as free text field.

Let me know if that solved your issue, otherwise your syntax might give an indication!
Cheers, Eva


#3

Hi @EvaS , I’ll post my query below:

SELECT 
    cluster,
    sum(monetary) as monetary_sum
FROM [TABLE_NAME]
WHERE
    1=1
    AND {{gender}}
GROUP BY cluster
ORDER BY cluster ASC

So, I think I defined the field filters in a similar way to what you did. Am I missing some point? In previous versions of Metabase my syntax generated a dropdown list similar to what I displayed in my previous message.


#4

Hi @lucasloami,
yes indeed your query looks good though.
Is the “gender” field classified as “category” in the settings? (Data Model --> Table --> Type of the field?)
I am not sure if it is even necessary to classify a field as category but it might be worth a try?

Let me know how it goes!
Eva


#5

Hi, @EvaS ,

I was able to solve the problem. I will describe below the things I discovered during the investigation of this problem.

  1. Metabase is impacted by how I modeled my database: so, in order to have a quick deliverable in my project, I basically created a database with a denormalized schema (basically a big table with all the columns required without FK’s).

When I created questions using this schema and used “Gender” (defined as “Category” in data types) as a filter, I think Metabase scanned the whole table and identified that there were a lot of occurencies of “M”, “F” and “I” without perform a distinct action in the field and it guessed the field would be a search box instead of a multiselect dropdown. Would it be a improvement point for Metabase?

  1. Solution 01: to change column’s filter data type: @maz and other guy mentioned in my Github issue that there is an option in Admin Panel to change filter type of a column. I used it and solved my problem. It’s a manual approach.

  2. Solution 02: to change database modeling: for performance reasons I modeled my database using a fact dimension model and when I created the filters in the dashboard they were automatically guessed as multiselect dropdown.


#6

There’s a new option in admin. If you go into the field properties, you’ll see this option:


I assume there’s a default based upon the number of values.


#7

Yeah, I used it in the second solution I described above. An automatic solution was change the database modeling.


#8

Hey, Lucas! Are you sure you’re on 0.28.5? That search feature is in master and 0.29, and it looks like you have an outdated build of master because the newest version of the search box does not look like that. I’m just double checking because I’m wondering if we’ve fixed the issue you’re describing here or not.


#9

Hey, @maz , I’m using v0.28.6. After I fixed my issue (using both solutions I described above), my filters are looking like this:

Gender dropdown


#10

Guys, I’m having the same issue, even using a denormalized table. One filed will show the drop down list, and other won’t.

Running the 0.29 (updated a few days ago).

I’ve already tried to re scan both the field and the DB, resync, for no good. Also, using field as category, with the explicit option to show all values in a list from Admin Panel.

Any more thoughts on that?


#11

We’ve identified the root problem and are working on a fix. The current workaround is to set it to “search box,” which will provide you with an autocomplete search box that should correctly search through all of the field’s values. (Incidentally, you might find that you prefer that to a long list of values.)

What’s going on is that if your field has more than 300 distinct values, Metabase is incorrectly not storing those field values when you set it to “a list of values” in the Admin Panel, which results in an empty list being displayed. The fix in PR #7556, which I linked to above, will correct the behavior and show you a static list of values if you’ve set the field to “a list of values.”

Sorry for the troubles, and thanks for your patience.


#12

I’m looking forward for that fix, and I ended up using what you said (and in this case, the search box is actually a better option, by the way). To be honest, Metabase is brilliant by itself, but the support I’m getting all over my experience makes it even more of a great tool to have. Thanks for the support.


#13

Hi

I am facing the same issue too.
I am unable to get the dropdown with values of variable in the view that I am referring to.
It just gives me a search filter and when I put the value,
I get the error -
ORA-00933: SQL command not properly ended

Metabase version 0.29.3
Have I configured the variable incorrectly?

Thanks,
Sneha


#14

@Sneha did you try the solutions I described above? Maybe they can help you =)


#15

@lucasloami
Thank you. I have tried solution 1 and I am able to get the drop down now with options.

The issue still persists where I get ORA-00933: SQL command not properly ended
when I select one of the options.

Is my query incorrect?


#16

Hi

I was able to make this work by modifying the query as below

select * from cmdw.TEST_SKYGO_SCHEDULE
where [[{{Status}}]]

without giving 'Activity_Status= ’