[Solved] Pre Filter Query

[esskay]

List contains a column of numbers e.g. 9, 92, 192, 292, 203 etc. I need to prefilter list which only contains a particular number. For example in above I need to filter all records with only 92. When I give contains = 92 then it shows records with 92, 192, 292 also. I need records which have only 92 and not 192 and 292.

When I select 92 it gives me 92, 192, 292 & 203 and so on. What I want is 92, 203

Please advise...

 

[cheesegrits]

Have you tried "EQUALS" rather than "CONTAINS"?

-- hugh

 

[esskay]

Yes...I did but it does not help. Equals only show records which has 92 and nothing else. whereas i want 92 alongwith other values too. Just want to exclude 192, 292 etc...

 

[cheesegrits]

OK, I'm a little confused because you original post says:

 I need to prefilter list which only contains a particular number. For example in above I need to filter all records with only 92

So, you want to show all numbers, except x92 (where x is any digit)?

As we don't have a "DOES NOT CONTAIN", you'll need to use a subquery ...

WHERE
Field: your PK field (usually 'id')
Condition: IN
Value: SELECT id FROM yourtable WHERE yourfield NOT LIKE '_92'
Type: query

Replace 'yourtable' with the name of the table this list is on, id with the P field, and yourfield with the one with the numbers in it.

-- hugh

 

[esskay]

sorry...not working...I think I am not able to communicate what I need. This goes like this -

1. I have a column in the list called 'Section' which contains numbers like (without [ ] brackets) -
a. [90, 91, 92,93,94]
b. [98, 101, 203, 94, 192]
c. [100, 292, 92, 96]
d. [87, 85, 93, 98]
e. [292, 5, 72, 86, 87]

2. PK Field is called 'SerialNo'
3. In the pre filter I want only records which contains 92. In the above example I want pre filtered records which will show only a & c.

The query which I have is as under -

SELECT SerialNo FROM my_table WHERE Section NOT LIKE '_92'

This does not give expected results and list all records...

 

[cheesegrits]

Are there spaces after the commas?

Are there always multiple numbers, or can there just be one?

-- hugh

 

[esskay]

Hi Hugh...Thanks for your response...

Yes...there is always a space after comma.

Most of the time there are multiple numbers but in some cases there is a single number too...

 

[cheesegrits]

OK, you can do it the long way, have multiple pre-filters ...

WHERE
Field: yourfield
Condition: EQUALS
Value: 92
Type: text

OR
Field: yourfield
Condition: LIKE
Value: 92,%
Type: text

OR
Field: yourfield
Condition: LIKE
Value: %, 92
Type: text

OR
Field: yourfield
Condition: LIKE
Value: %, 92,%
Type: text

 

[esskay]

I do not see anything called LIKE in Condition...

 

[cheesegrits]

Sorry, CONTAINS

 

[esskay]

Perfect...Thanks a ton Hugh...