Hi, is there a way to hardcode filter options? That is, instead of deriving the filter options from a table, can i just manually input what the selections are? I have a table that we inherited that’s highly denormalized and I want to be able to do a like (%%) search against a column. To put into context, my table has a column where the values are comma separated. Something like:
Find all rows with “blue”.
This is easy enough SQL to write but I can’t figure out how to configure a filter that would populate with all the distinct color options and then perform the like (%%) query.
Any help would be appreciated!
You could create a view that does what you need:
select ‘red, green, blue’ as choice
select ‘red, green’
Yeah I tinkered with that but for Field Filter to work, doesn’t it need to be able to join on the main table?
Ugh, yes, you’re right. Sorry
No worries! it’s such a simple thing. If I were building from scratch i’d know exactly what to do. I have a feeling what I’m gonna have to do is write a really gnarly view (or script or whatever) to normalize this data into lookup tables just so I can build a dang drop down filter
After 20+ years of being a BI consultant, I long ago decided that the best option is always to blame the original database designer
Set the filter on the options table/view, then join it to the main table in whichever way that makes sense :).
select colour, m.*
from main_table m inner join colour_options on (m.colour_list like concat(’%’, colour, ‘%’))