How can I search in a filter for multiple values to exclude? Basically the opposite of (1|2|3|4|5)

[dgreenberg]

I want to filter a field where the values are not in (1,2,3,4,5).

If I wanted the opposite that's simple it's (1|2|3|4|5).

I do not want to do this with triggers it would truly be best to do it as a filter saved in a bookmark for various reasons.

The list of values will change so I can't just select excluded and save it as a bookmark.

If this cannot be done I will set a flag in the data model something like:

Load RestrictionID,if(wildmatch(restrictionID,1,2,3,4,5)<>0,1,0 as RestrictioNFlag

Resident RestrictionTable;

That's not a bad solution but I feel like this might be able to be accomplished as a filter selection in a bookmark without triggers.

Here is where it gets interesting:

(-1|7|8|9|<>11|14|31|35)

That selects -1,7,8,9 excludes 11, includes 14,31,35

Great so you would think I could use the <> anywhere in the list but you can't it ignores it after the first occurrence.

So:

(-1|7|<>8|9|<>11|14|31|35)

Will only exclude 8 and will include 11.  Weird isn't it?

Since it does accept the <> in 1 position I am hopeful there might be some crazy syntax to make this work.

I do know of many other ways to achieve the same results with triggers and flags in the data model but really want to keep it simple with a field selection saved in a bookmark.

Thanks

Dan

[sunny_talwar]

Have you tried it with two actions

1) Select in field

(1|2|3|4|5)

2) Select Excluded

[dgreenberg]

Yes of course that would work but not permanently because the list of values will change over time so if I currently have just a list containing 1-10 and 11 and 12 are added they would not be included if I selected in field and then selected excluded so that is not a good solution for me.

The only viable solutions so far are:

• Set a flag in the data model
• use triggers to select then select excluded.

Again I am trying to avoid that for various reasons.

[sunny_talwar]

Why would it not do that? I am not sure how is that a concern?

[sunny_talwar]

Look at the attached

Comment/Uncomment the second inline load.... Select button will still select everything except 1, 2, 3, 4, 5

[swuehl · Accepted solution]

Maybe like

(* ^ (1|2|3|4|5))

Compound Search - demystified

[dgreenberg]

Thanks Sunny but what you are missing is the list of values will change over time.

If today I select 1,2,3 and then select excluded it will save the selections as 4,5,6,7,8,9,10

Tomorrow if 11 and 12 get added it will still only have a bookmark selecting 4,5,6,7,8,9,10

Now of course if I did this through a trigger it would work but a trigger is not the best solution here.

If it cannot be done as a filter to exclude a list of selections then I will do it as a flag in the data model.

[sunny_talwar]

Or if you need this based on On-Open Trigger or PostReloadTrigger....

[dgreenberg]

Stefan, owe you a pint!

If you are going to Qonnections look for me to collect!

[sunny_talwar]

That's exactly what I am trying to say... that based on what values are added, it will only exclude 1, 2, 3, 4, 5 and will include all new and old values... so 11 and 12 will be added in...