Unable to get field filters to return data in a MySQL query

Hi Folks –
I’m relatively new to Metabase and also a SQL novice, so it’s entirely likely this one’s on me…

I’m trying to use field filters to drill down using variables as field filters. The filter dropdown is correctly showing values for the field, but the returned result set is empty – I know this is incorrect, since there are values I can get to in other ways, and this has happened regardless of which field I’ve tried to use in a query.

I checked in the Admin>>Data Model for the specific field, and it is set to “category” by default, and the only available widget choices in the filter for that field filter are “Category” and “None” – neither returns a satisfactory value.

We’re using version 0.2.7.1, with a MySQL back end.

Any help figuring out what the problem is would be greatly appreciated.

Hi,
weird that the result is emtpy rather than showing an error message (as I would expect if something is wrong with your query …).
But when you show the dates rather than filter them there are results per date in the query?
Would you mind sharing the sql you wrote?
Hope I can help, cheers, Eva

Hi Eva – thanks for the response.

It’s not dates, but the names of Test Suites – if I recall, there are about 30 suite names, and over time, the list will grow (so that some results I’m trying to show may be null). If I hard-code a suite name where the variable would be set, I get a result set with values.

Hi,
so the suite names appear in your filter as a dropdown menu or did you classify the filter type as text and have to enter the name of the suites?
If you enter the name manually in your filter you might want to try to add LIKE LIKE CONCAT (’%’,{{var}},’%’), that just searches for the suite name with a LIKE or contains statement rather then the exact value.

Besides that I run out of ideas …
Cheers, Eva

It appears to be user error – my syntax was completely wrong (a co-worker seems to have solved it for me overnight).

I was doing something like this:

`WHERE test_suite.test_suite_name = {{test_suite_name}}`

I should have just done this:
'WHERE {{test_suite_name}}'

As I said, it was likely all on me. :slight_smile:
Thanks for your assistance Eva.

1 Like

Glad you worked it out.
By the way, our syntax “WHERE test_suite.test_suite_name = {{test_suite_name}}” is not wrong, when you use it with the variable type text this is how it has to look like …
Filters are a bit tricky in metabase!
Cheers :slight_smile:

That’s where I went off the rails – when I used it as a text variable type, it worked fine, but obviously that wasn’t going to be a great way to do things.

It’s all good – I’ve been joking with co-workers that I’ve written more SQL in the last couple of weeks than in my whole 20 year career. :smile:

1 Like

Thanks @ dhocking!

I needed to do the same with my SQL - it’s very interesting you wouldn’t need to use “table.name”.“table.column” = {{my_var}} for a field widget drop down list and rather…

Where {{my_var}}

Thank you again for your example and post! This needs to go on the https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

@VIVBI It’s already noted multiple times in the UI and documentation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
And there’s a longer article on Field Filters here, which also notes it:
https://www.metabase.com/blog/field-filters/index.html

By the way, this topic is three years old, so perhaps you should have created a new topic instead of reviving this.