Dropdown does not appear for filter on SQL question (v0.32.5)

I am using 0.32.5. A few versions ago, I was able to get a dropdown to appear for filters on questions. Then I upgraded and lost that ability. I don’t remember all of the version numbers but I know it wasn’t working in 0.31.2. I left it for a while and now I’m trying to get this working again. I upgraded to 0.32.5 today and still see the same problem.

I have a very simple query on one table that has 11 rows. I have a WHERE clause for 2 optional fields. If I set the variable type to Filter Field, I get search fields that expect multiple values to be manually typed in. No matter what I do, I can’t get a dropdown to appear. I checked my data model for those 2 fields - I have Field Type = Category and “Filtering on this field” = “A list of all values”. I cleared the cache and re-scanned it but I still only get a search field.

Then, of course, I have the same problem on the dashboards.

I’ve read other comments here and in the bug list but the open issues seem related to multiple tables, too many values, or older Metabase versions.

I know I was out of the loop for a while and haven’t kept up on all the changes but based on what I’ve read, it should work. Anyone have ideas?

SELECT
UserName
,TransmissionUniqueID
,FileType
,CONVERT(VARCHAR(23), LoadDateTime, 121) AS [Load DateTime]
,FileName

FROM [dbo].[AnalysisFileInformation]

WHERE 1=1
[[AND {{UserName}}]]
[[AND {{FileType}}]]

ORDER BY LoadDateTime DESC

Hi @sldorman
Do you see any warnings/errors in the Metabase log or browser console?
Does it work if you only have a single Field Filter?
Is the Field Filter doing lookup on the table AnalysisFileInformation or another table?

Hi @flamber,

The only error I see is when I run a query, it says “Invalid response from database driver” but it seems to be a non-issue based on bug #9818.

No, it doesn’t work if I have just one Field Filter.

The query is only on one table, all columns and Field Filters are on that table. I’ve tried to simplify it as much as possible but still can’t get it to work. I’ve removed the Order By, the CONVERT, but still doesn’t work.

I’m using Firefox and SQL Server by the way.

So you never see dropdowns, when using Field Filters, even when doing other queries on other tables?

Is it a regular table or view or scalar?
What is the actual type in the database of this field? I’m guessing char/varchar.

Do you get any errors in the log, if you try to force sync via Admin Settings > Databases > (MSSQL) ?

Have you tried to use the Sample Dataset H2 database and generate a simple query with a Field Filter?

Ah, you were reading my mind about trying the Sample Dataset. I copied a query in the Variables Help and it does come up with a dropdown but when I run the query, I get an error.

SELECT count(*)
FROM products
[[WHERE category = {{category}}]]

org.h2.jdbc.JdbcSQLException: Data conversion error converting “Doohickey”; SQL statement: – Metabase:: userID: 1 queryType: native queryHash: b993adb06e5a9591d2c78650f1e110595672540c50d13cec23a393ca7899073c SELECT count(*) FROM products WHERE category = “PUBLIC”.“PRODUCTS”.“CATEGORY” IN (?) [22018-197]

I force synced the Sample Dataset database and my database. I get the same results - dropdowns for Sample Dataset but query error, no dropdowns for my database. No errors in the log from the forced sync.

My database has regular tables only and the fields I am trying to filter are varchar. I can’t get the dropdown on any of my queries on any of my database tables. This applies to newly created questions as well as existing questions.

If you’re using Field Filters, then you should not include the column, since that’s added as part of the magic.

SELECT count(*)
FROM products
[[WHERE {{category}}]]

And as for why it’s not working on your database, I’m not sure.
Which version of MSSQL?
Are you using H2, Postgres or MariaDB/MySQL as your backend metadata?

I took that query directly from the help page when setting the variables:

image

I tried your query and it runs successfully, no error and dropdown works. I know I've tried the "magic" in the past and it hasn't worked consistently. I will try it with my queries to see if that helps.

As for the databases - I'm using SQL Server 2012 and H2 for the metadata. I'm running from the jar.

Look up a little higher, and you’ll see the “Field Filters” section. “Optional Clauses” is something different.
I would highly recommend migration away from H2:
https://www.metabase.com/docs/latest/operations-guide/start.html#migrating-from-using-the-h2-database-to-mysql-or-postgres
It would make it easier to manage the data and having a look at what the fields are containing.

Changing to “magic” didn’t change anything for my regular questions. I added a new SQL Server connection in Metabase that points to the same SQL Server database and created a new question against it - the dropdown works fine. So Metabase’s knowledge of my data model was wrong and forcing a re-sync didn’t correct it.

Glad it’s working but it’s going to be a lot of work to change all of my questions.

Thanks for the help @flamber

You might want to consider making a backup of the H2 database and then try to Admin Settings > Databases > “Discard saved field values”.

Well, I was using optional clauses and used the query under the Optional Clauses section but it errors. Removing "category = " from that query works. Either the help is wrong or Metabase is not working as expected.

Thanks for the link, I’ll look into changing the metadata database. I try to stick with whatever comes with the app but now that my set up is growing, I can see why it would be wise to use something more manageable.

I did run “Discard saved field values” but it didn’t have any effect.

Thanks again!

There’s two types of ways to use variables.

  1. Regular text/number or simple date, then you write WHERE column = {{filter}}
  2. Field Filters does a lot of automatic stuff, so you would just write WHERE {{filter}}

And then you can use “Optional Clauses” either of the variable types.

That also what both the help panel and the documentation states.

But it sounds like everything is working, so that’s good.

So the problem was none of the above. As I tried to re-create my questions with the other database connection, I ran into some very buggy issues - some variable fields disappeared, pages weren’t displaying correctly, Outlook windows seemed to interfere (or vice versa). The memory usage was very high. So I restarted the computer and cleared Firefox’s cache. Now all of my original questions and dashboards properly show dropdown fields.

I did try restarting Firefox yesterday but I didn’t clear the cache. Perhaps clearing it would have been enough, maybe the computer restart was needed. Either way, everything is working now and I don’t need to re-create anything.

Hope this helps someone else.

Thanks for your help @flamber. I learned a few things along the way and that’s always good.

1 Like