Submitted by Tom on 2010/08/27 06:36

Thread for filter queries - I suspect I will have more!

Is it possible to filter for a field NOT having a certain text content

e.g. MyField is NOT "text here"

I cant seem to get it to work here...

EDIT/ it was on the filtering page (5.2.4 Grid Filter Editbox) but I had overlooked it...

Comments

Tom, I'm no expert (as you must know by now) but I think this would usually be expressed by is not equal to. I don't know if IQ has this facility. Also how about more than or less than "text here". I think that would mean about the same as not equal to.

Armando has pointed out to me that an easy (simple) filtering mechanism is really necessary for easy usage. I agree.

You are presenting a very simple problem which should be easy to implement & currently it really isn't for 'everyman".

I look at an amazingly capable email program like The Bat! and only until relatively lately has it included an easy filtering method.

Thunderbird has it now, too. I think IQ should look at these simple filter GUI examples for IQ & I'm sure others will have even better ideas.

Point is it has to be SIMPLIFIED.

I, for example, wanted to filter on a background color so I thought ok, there's a field for that so I found "ItemColor" so

I created a simple filter "ItemColor = &H0079F2"

and for some reason I started getting all kinds of msgs, i.e. check source, etc & the filter didn't work.

I found a workaround but that should have been pretty simple.

When I have more time I will try it again.

Thanks for starting this filtering thread

>I created a simple filter "ItemColor = &H0079F2"

ItemColor is a text field, so the correct syntax is:

ItemColor = "&H0079F2"

ItemColor = "orange"

etc.

HTH

Thanks Pierre

[quote=Pierre_Admin]

ItemColor is a text field, so the correct syntax is:

ItemColor = "&H0079F2"

ItemColor = "orange"

[/quote]

how do I filter for itemcolor is NOT red ?

- I used be able to do this but none of the ones in the filter dropdown work.

Tried a bunch of variations with = and NOT without success

EDIT/ from Pierre's post below:

Case: exact text: item <> "text here"

I tried:

ItemColor <> "Red"

now nothing is showing - Filter applies to sub-items is turned on

ItemColor not like "red"

But this should work too :

itemcolor <> "red"

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 2

Dell Vostro 1500, Ram:3gb, CPU:Core2Duo T7500 2.2ghz

Yep, this should work. If you want to include items without colors, you need to use this:

ItemColor is null or ItemColor <> "Red"

HTH !

[quote=Pierre_Admin]

Yep, this should work. If you want to include items without colors, you need to use this:

ItemColor is null or ItemColor <> "Red"[/quote]

this works - thanks Pierre

but

ItemColor <> "Red"

does not work...

There's only one TLI in the grid, all direct sub-items are not coloured

The items I really want to filter are 4 levels down (counting TLI as level #1) and they are all coloured

I'm curious why:

ItemColor <> "Red"

does not work, if Filter for sub-items is turned on ??

Next query:

how to exclude more than one colour.

I tried:

ItemColor is null or ItemColor <> "Red" or ItemColor <> "Orange"

but that didnt work at all

then tried:

ItemColor is null or ItemColor <> "Red", ItemColor <> "Orange"

(with a comma instead of the last "or") but that has caused a seize-up and blank-out - in fact it's not starting up again properly - obviously cause it's still trying to implement the filter

HELP !!! ***

is there a way of removing remembered filter info without opening the IQbase file ??

... meanwhile I'll go see when my last backup was made

*** EDIT - luckily I had a backup from just before I added the dodgy filter

Hi Tom,

I remember having this problem as IQ was set to open last DB on start up and load the last grid... I don't remember what I did to solve it -- maybe manually edit the \AppData\settings.ini

That's why I ended up not loading the last DB on start up... You can change that in the Options.

[EDIT : forgot that part : and, from there (options), you can also set the DB to not open the last grid, then open the DB and manually edit the filters in the grid management dialog]

I'll get to your other questions later.

PIerre : is there a way to get out of the problem (apart from editing the ini) when IQ endlessly tries to load a faulty DB, everytime it's run ?

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 2

Dell Vostro 1500, Ram:3gb, CPU:Core2Duo T7500 2.2ghz

[quote]but

ItemColor <> "Red"

does not work...

There's only one TLI in the grid, all direct sub-items are not coloured

The items I really want to filter are 4 levels down (counting TLI as level #1) and they are all coloured

I'm curious why:

ItemColor <> "Red"

does not work, if Filter for sub-items is turned on ??

[/quote]

There are 2 reasons :

- IQ doesn't go down the hierarchy until it finds an item meeting the filter condition. It stops looking after the second level.

It's in Mantis (the title isn't very explicit... I was young and inexperienced at the time) :

http://mantis.sqlnotes.net/view.php?id=312

- if the TLI doesn't meet the Filter, it doesn't look further (which is logical since the filter is applied to TLI AND subs). So what you need to do is either find a way so that all items (even the subs) share some elements of the source, or use the column filter.

I wonder if column filter and text box filter should behave approximately the same. It isn't the case at the moment and I don't use the column filter much as there are some restrictions which disturb my workflow (can't show all subs -- alt-click -- when it's on, won't work if the whole hierarchy isn't first expanded, etc.).

[quote]how to exclude more than one colour.

I tried:

ItemColor is null or ItemColor <> "Red" or ItemColor <> "Orange"

but that didnt work at all

[/quote]

You'd have to use AND instead of OR (you don't want that Color OR that Color excluded, but that Color AND that Color...). And dont forget to add "or itemcolor is null" somewhere... Would be something like :

ItemColor is null OR (ItemColor <> "Red" AND ItemColor <> "Orange")

I havent tried but it should work... depending on what you're trying to achieve.

Note that you can use () to group conditions together. e.g. :

Task AND (ItemColor is null OR (ItemColor <> "Red" AND ItemColor <> "Orange"))

which means that the item needs to be 1- a TASK 22b- which has a Color that is either null 2b- or something else that's not red and also not orange.

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 2

Dell Vostro 1500, Ram:3gb, CPU:Core2Duo T7500 2.2ghz

I've updated the Mantis description etc. :

Summary	0312: Sub-items filter issue - when filter applies to sub-items, it should go down the hierarchy and not stop at 2nd level
Description	sub-items filter switch in filter textbox : "\|" should allow searching all sub-items and sub-sub items indefinitely (instead of only searching the first level of sub-items and... stopping there) ...The Ecco way, as you suggested?
Steps To Reproduce
Additional Information	I wonder if the concept could be extended to the concept of "filter applies to sub items " in general. Not just the "\|" . See tom's more recent comment about this "problem" : http://www.sqlnotes.net/drupal5/index.php?q=node/1893#comment-10549 [^]

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 2

Dell Vostro 1500, Ram:3gb, CPU:Core2Duo T7500 2.2ghz

[quote]

then tried:

ItemColor is null or ItemColor <> "Red", ItemColor <> "Orange"

(with a comma instead of the last "or") but that has caused a seize-up and blank-out - in fact it's not starting up again properly

[/quote]

I was not able to reproduce the issue. If you can reproduce it with the sample IQBase, please provide the steps.

>is there a way of removing remembered filter info without opening the IQbase file ??

In v0.9.25T, I've added the following: Press Shift will prevent opening of the last view (grid, calendar)

You can then use Manage Grids to remove the offending filter.

HTH !

Pierre

[quote=Pierre_Admin]In v0.9.25T, I've added the following: Press Shift will prevent opening of the last view (grid, calendar)

You can then use Manage Grids to remove the offending filter.

[/quote]

Good idea ! :)

--------------------------------------------------------------------------------

Windows XP Home Edition, Service pack 2

Dell Vostro 1500, Ram:3gb, CPU:Core2Duo T7500 2.2ghz

Hi Tom (and Jan)

Lots of activity on the forum... a good thing for sure, but not easy to keep up and keep dev. going

You can use:

Case: text is anywhere in the item: item not like "*text here*"
Case: exact text: item <> "text here"

HTH

[quote=Pierre_Admin]

Hi Tom (and Jan)

Lots of activity on the forum... a good thing for sure, but not easy to keep up and keep dev. going

You can use:

Case: text is anywhere in the item: item not like "*text here*"
Case: exact text: item <> "text here"

HTH

[/quote]

thanks Pierre,

I'm embarrassed to say I had looked at the relevant page (Source Filter Editbox) but I missed it