Field Filtering

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

Capture1

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
1 Like

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.

@flamber I tried creating a view but it does not appear in the drop down list. Why is that so?

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. :rocket: