filter problem 2

[vasah20]

ok guys -
here's the problem.

I'm trying to use the Recordset filter property multiple times, but instead of filtering it down, it can return more records.

ex:
original recordset contains 300 records

Filter 1: name like '%masking%' Recordcount: 8
Filter 2: Name like '%tape%' Recordcount: 134

the filters are applied in succession, but for some reason things aren't quite working the way that they should be.

any ideas?
leo

 

[Ovatvvon]

I don't quite understand...what is it that isn't working? How to you want it to work, or what do you want it to produce?
-Ovatvvon :-Q

 

[vasah20]

what's going on is that I'll get a recordset containing about 300 records.

I then filter this recordset down to about 8 records.
then, I try to filter the filtered recordset, expecting maybe 2 or 3 records. Instead, the filter on the filtered recordset returns 134 records (obviously not filtering the 8, but instead refiltering the original 300)

make any sense - got any ideas?

 

[Ovatvvon]

sure, but, how are you filitering it? If you are looking for criteria that matches more than 8 records, or 2 or 3 records, then how can you filter it down to that?

Also, I noticed you are useing wild cards in your sql statment for the 'filter' (WHERE clause). If you take out one of the wild cards on one of the sides (if that does not interfere with what you are trying to do), then that will limit the returns because there will be less matches.

This would be a lot easier to talk about logically if you would be a little more specific in your descriptions rather than just that you have a database and you are filtering, but the filter returns down to 134 records. That doesn't tell me hardly anything, so it's hard for me, as I'm sure anyone else, to give you pointers.
-Ovatvvon :-Q

 

[Ovatvvon]

oh wait, I see now. Just a search from within the returned results only, rather than a new search, and you're saying the second search or filter is returning more results than the original...sorry....just woke up a few ago...not totally with it. LOL

I will investigate and let you know!
-Ovatvvon :-Q

 

[Ovatvvon]

Here's a thought,
when they search for tape, and then they want to search within those results, why not just add to the sql statement?
Say currently you have:


<%
Dim info, sql
info = Request.Form(&quot;selectionInformation&quot
sql = &quot;SELECT * FROM table WHERE ((table.name) Like '%&quot; & info & &quot;%');&quot;
etc etc etc...
%>



now that 8 records came up from the user searching tape, you want to pick masking, so when they search again, you add it to the where clause...pass the sql:


<%
Dim sqlStr, info, sql
info = Request.Form(&quot;selectionInformation&quot
sql = &quot;SELECT * FROM table WHERE ((table.name) Like '%&quot; & info & &quot;%');&quot;
sqlStr = sql
'connection
'Information
'Close connection
%>

<form method='post' action='page3.asp'>
Further search info:<BR>
<input type='text' name='moreinfo'><BR>
<input type='hidden' name='info' value='<%= info%>'>
<input type='hidden' name='sqlStr' value='<%= sqlStr%>'>
<input type='submit' value='submit'>
</form>




---page3.asp---------------------------

<%
Dim moreinfo, info, sqlStr, sql
info = Request.Form(&quot;info&quot
moreinfo = Request.Form(&quot;moreinfo&quot
sqlStr = Request.Form(&quot;sqlStr&quot
sql = &quot;SELECT * FROM table WHERE (((table.name) like '%&quot; & info & &quot;%') AND ((table.subname) like '%&quot; & moreinfo & &quot;%'));&quot;
'Connection
'display more detailed info
'close connection

%>


Hope that is what you are looking for and helps!
-Ovatvvon :-Q

 

[link9]

leo --

You can only apply one filter at a time... sort of stinks.

I ran into this problem several months back, and had to do as Ovatvvon has suggested by just doing the multiple filter thing with a SQL statement, rather than the .filter method on a recordset. When you apply that second filter, it is applying it on the 300 records, first removing the filter that is already on it.

:-(

 

[link9]

After thinking on this some more, I think this would be a possible workaround, but it's not going to be very efficient. Probably less efficient than the SQL solution, but here goes.

You could apply that first filter, and then create a custom recordset to be the same dimensions as your filtered recordset, and then copy each value from the original into the custom... then you could filter the custom.

Like I said, though, it's obviously not very efficient....

Before, I even tried to do a .clone of the filtered recordset to see if it would only copy the filtered records, but alas, it copied the whole thing, and I was right back where I started...

**still thinking**

 

[vasah20]

I'm thinking that I'm going to have to implement the SQL solution in order to get a subsearch working.

I tried doing the clone method too, but that didn't seem to work either.

That custom recordset idea is a good idea, but it's way too inefficient.

Here's a thought.
I know that there is a VBScript Filter function, that filters an array. Has anyone used this function?

According to the Microsoft books:

Returns a zero-based array containing a subset of a string array based on a specified filter criteria.

Filter(InputStrings, Value[, Include[, Compare]])

Ex:
Dim MyIndex
Dim MyArray (3)
MyArray(0) = &quot;Sunday&quot;
MyArray(1) = &quot;Monday&quot;
MyArray(2) = &quot;Tuesday&quot;
MyIndex = Filter(MyArray, &quot;Mon&quot ' MyIndex(0) contains &quot;Monday&quot;.

Couple questions:
1) After I apply a filter, if I do .GetRows, will I get an array back containing just the filtered recordset?

2) Does anyone know what the runtime is like on Filter, and whether or not it supports wildcards?

Any thoughts -
leo

 

[jonscott8]

ISO replacing the existing Filter, appending the new cryteria will yield the results you desire:

oRS.Open &quot;MyTable&quot;, oConn, adOpenKeyset
' Do Stuff
oRS.Filter = &quot;Name like '%masking%'&quot;
' Do More Stuff
oRS.Filter = oRS.Filter & &quot; AND Name like '%tape%'&quot;
' Do Everything else Jon Hawkins

 

[vasah20]

I can't do it in that way because there is the possiblity that I have to do a filter in a

&quot;(Name like '%xxx%' or Name like '%yyy%') AND (Name like '%zzz%' OR Name like '%abc%')&quot;

Unfortunately, the recordset filter method will not support this syntax. :-(

If it did, I wouldn't be having this problem.

 

[jonscott8]

You are correct that you cant do it like this:

&quot;(Name like '%xxx%' or Name like '%yyy%') AND (Name like '%zzz%' OR Name like '%abc%')&quot;

But if you restructure it like so, you can do it:

&quot;(Name like '%xxx%' AND Name like '%zzz%') OR
(Name like '%xxx%' AND Name like '%abc%') OR
(Name like '%yyy%' AND Name like '%zzz%') OR
(Name like '%yyy%' AND Name like '%abc%')&quot;

If you refer to the ADO documentation for the Filter property, there is an example similar to this. Jon Hawkins

 

[Ovatvvon]

Man,
You all are teaching me new stuff. I didn't know about the .filter! When Vasah was talking about filter, I thought he meant filtering through the rs useing the sql statment. Not sure who to give the tipmaster too thought since you all have been talking about it! (This is cool though, I like the conversations like this rather than the basic questions. Now I can learn this way too. )
-Ovatvvon :-Q

 

[vasah20]

Jon -
thanks for that idea. it never even occurred to me to restructure the filter like that. Admittedly, it has the potential to be an extremely long filter (since all the possible variations need to be matched up), but I think that it might be the most effective method.

Ovatvvon if you're interested in seeing what my filtration code is going to look like, I'll be more than happy to post it here.

thanks
leo

 

[Ovatvvon]

That would be cool, I'm interested in seeing this!
Thanks!
-Ovatvvon :-Q

 

[vasah20]

ovatvvon -
sorry it took so long to post this code, it took me forever to get the combinatorial algorithm right (thanks to shaddow who helped me big time on this)

I'm just going to post the code, as is. Something to remember is that sp_querylookup returns a recordset containing one column, and N rows. the NULL acts as a delimiter.
(sp_querylookup grabs all the possible abbreviations for the words in our DB)

'add 1 to the elements in the counting array
'to iterate through the values
SUB Add1(mainArr, ubMA)
	dim carry, sum, i
	carry = 0
	
	for i = 0 to ubMA
		'step through the array
		if carry = 0 then
			sum = int(ElArr(i)) + 1			'just started the iterations
		else
			sum = int(ElArr(i)) + carry	'continuing through the iterations
		end if
		
		if sum > ubound(mainArr(i)) then	'past ub of array
			carry = 0				'reset the values of the arr
			ElArr(i) = 0			'set this to 0, and move on.
		else
			ElArr(i) = sum			'assign next value for ElArr
			exit for					
		end if
	next
END SUB

'get the total number of combinations possible
FUNCTION GetTotalComb(arr)
	dim total, i
	total = 1
	for i = 0 to ubound(arr)
		total = total * (ubound(arr(i)) + 1)	'since ub is ubound + 0
	next
	getTotalComb = total
END FUNCTION

'initiate the subsearch
FUNCTION subSearch(byref rs)
	dim trimStr
	filters = filters	& " "	& Request.Form("subSearchField")	
	trimStr = trim(filters)
		
	'get all the possible abbreviations from the DB
	dim rsLookup, sqlLookup, arrLook
	sqlLookup =	"sp_querylookup '" &filters& "'"
	set rsLookup =	server.CreateObject("adodb.recordset")
	rsLookup.Open sqlLookup, conn
	if	not rsLookup.EOF then arrLook	= rsLookup.GetString (,,," ","|*|") 'simplify splitting
	rsLookup.Close	
	set rsLookup =	nothing
	'end DB stuff
	
	dim mainArr, ubMA
	mainArr = Split(arrLook,"|*|")
	ubMA = ubound(mainArr)	'number of sets
		
	'generate counting array
	redim elArr(ubMA)
	for i = 0 to ubMA
		mainArr(i) = Split(trim(mainArr(i))," ")	'populate arr of arrays
		elArr(i) = 0
	next
		
	Dim tStr,ubI, tArr, cnt, fStr
	for i = 1 to GetTotalComb(mainArr)	'grab all possible permutatoins
		tStr = ""
		cnt = ""
		'step through the array, using the elArr as the counting array
		'to show where in the array the index should be
		for j = 0 to ubMA
			tStr = tStr & " AND Name Like '%" & mainArr(j)(elArr(j)) & "%'"
		next
		
		Call add1(mainArr,ubMA)	'increment the index in the elArr
		tStr = Trim(tStr)							'just in case
		tStr = Right(tStr,Len(tStr) - 4)		'hack off the excess AND
		fStr = fStr & "(" & tStr & ") OR "	'contain within it's own (), and add the OR
	next
	fStr = Left(fStr,Len(fStr) - 3)	'hack off the excess OR
	rs.Filter = fStr
END FUNCTION


'main procedure
if cbool(Request.Form("subsearch")) then	'vendor search
	Dim elArr()
	Call subSearch(rs1)
end if

the output is similar to:
&quot;(Name like '%xxx%' AND Name like '%zzz%') OR
(Name like '%xxx%' AND Name like '%abc%') OR
(Name like '%yyy%' AND Name like '%zzz%') OR
(Name like '%yyy%' AND Name like '%abc%')&quot;

I hope you (or anyone else) finds this helpful
leo