Hardcoded list of filter options

Get Help
1

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:

red,green,blue
red,green
blue,yellow
yellow
yellow,blue,purple

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!

2

You could create a view that does what you need:
select ‘red, green, blue’ as choice
union all
select ‘red, green’
union all
etc, etc

3

Yeah I tinkered with that but for Field Filter to work, doesn’t it need to be able to join on the main table?

4

Ugh, yes, you’re right. Sorry

5

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

6

After 20+ years of being a BI consultant, I long ago decided that the best option is always to blame the original database designer :grin::grin:

1 Like
7

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, ‘%’))
[where {{colour_filter}}]