so im using field filtering as a variable in my query.
i specified the field to display all values when used as a field filter as in the image shown but for some reason it doesnt list values at the query side ....still shows none instead of category
i have tried using different query and different tables but still no changes
@anonymous The Field Type has to be one of the listed types, as noted in the documentation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#creating-sql-question-filters-using-field-filter-variables
thanks it worked
so it works as in the values displays
but when i run the report, i get this error
ERROR: invalid reference to FROM-clause entry for table "table_name"
Hint: Perhaps you meant to reference the table alias "table_alias". Position: 2785
@anonymous Without seeing your query, it’s difficult to know, but something is wrong with you FROM-clause
Query throwing the Error:
Select *
from orders ord
left join subcategory sc on sc.id = ord.category
left join customers cus on cus.id = ord.requester
left join customers cu on cu.id = ord.assignee
where ord.create_date::TIMESTAMP::DATE >= {{start}} and
ord.create_date::TIMESTAMP::DATE <= {{end}}
and ord.department = {{dept}}
order by ord.id desc
if i should take out the dept variable and run the below query, it runs perfect
Select *
from orders ord
left join subcategory sc on sc.id = ord.category
left join customers cus on cus.id = ord.requester
left join customers cu on cu.id = ord.assignee
where ord.create_date::TIMESTAMP::DATE >= {{start}} and
ord.create_date::TIMESTAMP::DATE <= {{end}}
order by ord.id desc
or if i should replace the dept variable with an actual value like below , and supply the date varaibles, the query also runs perfect.
Select *
from orders ord
left join subcategory sc on sc.id = ord.category
left join customers cus on cus.id = ord.requester
left join customers cu on cu.id = ord.assignee
where ord.create_date::TIMESTAMP::DATE >= {{start}} and
ord.create_date::TIMESTAMP::DATE <= {{end}}
and ord.department = 4
order by ord.id desc
the department field is a foreign key from Department table
so i have mapped the values of department field in orders table to the main table (department)
below is the data model for the orders table
so it displays the name of the department but it should use the value(which is the ID , an integer) in the orders table
@anonymous As noted in the documentation, you cannot use table aliases for Field Filters:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#the-field-filter-variable-type
And Field Filters should not include the column in the query, since that is automatically inserted by references from the sidebar:
where {{field_filter}}
also applying field filtering on a dashboard is been very difficult for me to do
so i have added a question with columns [product_name, count and created_at] and i want to add a field filter to be used on the date.
but the after i select time range filter from the dashboard , it tells me no “valid columns”
please help asap
Before i add the filter, the image shows the columns
After i add the kind of filter which is time, this is what i get
@anonymous
Post a screenshot of the query with the variable sidebar open.
Or are you trying to add a question, which uses a Saved Question that is SQL?
ok so away from the above problem, for now its solved
i chose ‘single date’ as the kind of time filter and it worked.
Anyways there is another issue im facing
this is my query, when i run this query i get the error complaining about explicit stating the data types
select * from details
join product on product.key = details.key
where product.name = {{product}}
so i changed the query to the one shown below and i get know results when the query runs (how interesting)
select * from details
join product on product.key = details.key
where product.name = {{product}}::character varying
but the expected behavior is , i should get results because if i run the below query i do get results
select * from details
join product on product.key = details.key
where product.name = 'brown packed'
@anonymous
Is this a question about Field Filters?
If yes, then your query should be where {{product}}
as I already pointed out.
If no, then open a new topic, since it’s clearly a different problem than originally - and make sure to describe the problem, include error logs.
yeahh exactly
where {{product}} worked thanks
so i have this query which returns some results and i want to use this custom results in my field filtering…is it possible
SELECT description,
to_date(char_date, 'DD Mon YYYY') + interval '1 month 23 hours 59 minutes 59 seconds' expected_date
FROM (
SELECT description,('14 ' || description) char_date
FROM (
SELECT distinct substring(unnest(string_to_array(description, ',')) from '\[(.+)\]') description
FROM orders
) des
) dtd
ORDER BY expected_date ASC
@anonymous
Not unless you create a View
in your database, so Metabase just sees a regular table, but I don’t know which column you’re going to filter on.
Anyways, I think it’s time that you started a new forum topic, since this is now a different question. And please do a search in the forum first.
I was working with CTEs, and what worked for me was removing all aliases and replacing them with the original table names. I hope you find this helpful.