Cannot add Field filter variable in the dashboard filder [SOLVED]


#1

Hi everyone,

I create a new question in metabase which use the Field filter variable
SELECT
count(id_user)
FROM tbl_user
WHERE
{{register_date}}

above question is to count number of user base on registerDate and I add this question in to dashboard.
In my dashboard, I create a filter Date range but when I apply field, my above question saying “Not valid fields” then the filter cannot use the range date in the filter and apply into my question above.

Anyone already done something link this ?
p/s: I’m in version v0.29.3
Built on 2018-05-12


#2

Does any of this help?


#3

Unfortunately no, I believe his case would be because he did not register the field so that the metabase understands


#4

Can you help to show me how I can register the field ? I using the register_date as field variable which is the same name of my column in the table tbl_user following metabase guide:

Field Filters

Giving a variable the “Field Filter” type allows you to link SQL cards to dashboard filter widgets or use more types of filter widgets on your SQL question. A Field Filter variable inserts SQL similar to that generated by the GUI query builder when adding filters on existing columns.

When adding a Field Filter variable, you’ll need to map it to a specific field. You can then choose to display a filter widget on your question, but even if you don’t, you can now map your Field Filter variable to a dashboard filter when adding this question to a dashboard. Field Filters should be used inside of a “WHERE” clause.

Example:
SELECT count(*)
FROM products
WHERE {{created_at}}


#5

Thanks for your guide, I found the Field Filters setup. It works for me on the above sample.

However, when I setup the same way but for query with inner join

Select
count(tu.id_user)
from tbl_user tu
inner join tbl_payment tp on tu.id_user = tp.id_user
where trim(coalesce(tp.paid_date, ‘’)) <> ‘0000-00-00 00:00:00’
AND DATEDIFF(tp.paid_date, tu.register_date) <= 2
[[ AND {{register_date}} ]]

where register_date is in tbl_user, then when filter it, it shows “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘tbl_user.register_date’ in ‘where clause’”. What should I update here ?

Thanks,


#6

Hi guys,

I found myself the solution, must using the alias table name = table name so metabase can understand
Good SQL is:
Select
count(tbl_user.id_user)
from tbl_user tbl_user
inner join tbl_payment tp on tbl_user.id_user = tp.id_user
where trim(coalesce(tp.paid_date, ‘’)) <> ‘0000-00-00 00:00:00’
AND DATEDIFF(tp.paid_date, tbl_user.register_date) <= 2
[[ AND {{register_date}} ]]

Cool.