Table Prefilter begins with range

[jsnider]

I am trying to set up a pre-filter on a table, that uses the begins with criteria, that has a range. So basically, in my particular instance, records where the project field begins with A through D. I can do it for a particular letter, but can't figure out how to get that range in there.

Thank you in advance for any thoughts!

 

[cheesegrits]

Don't think you can do that with our built in filters, we don't have a "BETWEEN" feature. I thought in MySQL you could cheat and do straight up compares on strings like "WHERE foo > 'A' AND foo < 'E", in which case you could have just used to filters joined with AND, but I tested and that doesn't seem to work.

So you'd have to do a subquery, like ...

WHERE
Field: id
Condition: IN
Value: SELECT id FROM your_table WHERE LEFT(project, 1) BETWEEN 'A' and 'D'
Type: query

If it can be lower or upper case, do

Value: SELECT id FROM your_table WHERE LOWER(LEFT(project, 1)) BETWEEN 'a' and 'a'

This is kind of a kludge, as subqueries can be an expensive way of filtering, and I don't think indexes will get used once you do a LEFT(...), although I'm not sure about that. But as long as you don't have a gazillion rows you should be OK.

-- hugh

 

[cheesegrits]

Actually, I just tested again, as I was sure it should work, and two filters joined with AND should work.

So one with company > A, and one with company < E, using AND to join. Note that it's < E, not D. Type "text", not query.

Haven't tested this actually in Fabrik, just by hand with a query on a test table.

-- hugh

 

[jsnider]

Nice! The second one did it! Here is screen shot for future reference for everyone! That is super simple. I figured there must be a way, but I just didn't know the right syntax. Thanks again so much!