Case Sensativity in search

[ictoo]

Hello everyone, this is another aspect of FoxPro I’m not sure on so I hoped to be enlightened again by you all

I want to write a simple program that will search a table for all occurrences of a letter and will keep going down tell you type in the full name of what’s in the table, lets say in the table there are 100's of "How To" books so you start by typing H in to a textbox and it shows everything with H then Ho and so on tell it shows just "How To" entry’s in the grid your displaying the table on. Now I found a FAQ on here that’s helped me but I’m stuck on one thing case sensitivity, I cant for the life of me work out a way to make it if someone types in h occurrences of H and h show up right now its case sensitive.

So I’d just like to ask if this can be done make the search none case sensitive?


Link :

http://www.tek-tips.com/faqs.cfm?fid=1214

This is the code:

SET EXACT OFF

LOCAL cFilterOld, cFilterIn
cFilterOld = ThisForm.cFilterOld
cFilterIn = KEY()


SET FILTER TO


IF LEN(ALLTRIM(This.Value)) > 0
ThisForm.cFilter = (ALLTRIM(This.Value))
IF EMPTY(cFilterOld)
SET FILTER TO ThisForm.cFilter $ &cFilterIn
ELSE
SET FILTER TO &cFilterOld .AND. ThisForm.cFilter $ &cFilterIn
ENDIF

LOCATE

ELSE
IF !EMPTY(cFilterOld)
SET FILTER TO &cFilterOld
ENDIF
ENDIF



ThisForm.Refresh()
ThisForm.Grid1.SetFocus
This.SetFocus

 

[JRB-Bldr]

As to how to do "Case Sensitivity in search"...

The best way is to 'normalize' the search parameters by doing something like searching for the UPPER() of the input characters as found in the UPPER() of the field(s).

Something like

   LOCATE FOR UPPER(cInput) $ UPPER(MyField)

Good Luck,
JRB-Bldr

 

[ictoo]

I tried using upper but it then only showed the uppercase words.

code below works but as i said only shows Uppercase words, i really cant get my head around this.. i guess the easy way out is to make another field in the table with the words all in upper case and then another field showing the normal formatted words and search on the uppercase field.

SET EXACT OFF

LOCAL cFilterOld, cFilterIn
cFilterOld = ThisForm.cFilterOld
cFilterIn = KEY()


SET FILTER TO


IF LEN(ALLTRIM(This.Value)) > 0
ThisForm.cFilter = UPPER(RIGHT(ALLTRIM(This.Value),LEN(ALLTRIM(This.Value))))
IF EMPTY(cFilterOld)
SET FILTER TO ThisForm.cFilter $ manual.propname
ELSE
SET FILTER TO &cFilterOld .AND. ThisForm.cFilter $ &cFilterIn
ENDIF

LOCATE FOR UPPER(ThisForm.cFilter) $ UPPER(propname)
GO TOP

ELSE
IF !EMPTY(cFilterOld)
SET FILTER TO &cFilterOld
ENDIF
ENDIF

 

[DSummZZZ]

Have you tried using the ATCC( ) function? ATCC() ignores case.

Something like:

SET FILTER TO ATCC(ALLTRIM(This.Value), SomeField) > 0

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Ictoo,

You've correctly wrapped the search terms in your LOCATE with UPPER(), but you also need to do it with the filter:

 SET FILTER TO ;
   UPPER(ThisForm.cFilter) $ UPPER(manual.propname)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

search a table for all occurrences of a letter

Using SET FILTER or LOCATE on the original table might not be the most speedy way to get what you are wanting.

Instead you might do a SQL Query to acquire a sub-set of the original table. That sub-set can be modified if the user continues to enter more search characters.

Something like:

*  Assumption - You have a Grid on a Form 
*    The Grid has its RecordSource as   RevuData
*
* --- Eliminate Visibility Of Previous Search Results ---
SELECT RevuData
DELETE ALL
SET FILTER TO !DELETED()

* --- Get New Data To Meet New Search Criteria ---
SELECT *;
   FROM MyTable;
   WHERE UPPER(ThisForm.cFilter) $ UPPER(manual.propname);
   NOCONSOLE;
   INTO CURSOR ThisData

SELECT RevuData 
APPEND FROM DBF('ThisData')

SELECT ThisData
USE

SELECT RevuData 
GO TOP
ThisForm.Grid1.Refresh

Obviously there are a lot of variations as to how to use the Search results data. This was only one possible way.

Good Luck,
JRB-Bldr

 

[bborissov]

jrbbldr,
Instead of:

SELECT RevuData
DELETE ALL
SET FILTER TO !DELETED()

You could use

SELECT RevuData
ZAP
..

Just because SET FILTER will slow down your performance. And if you use DELETE ALL you could use ZAP


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[JRB-Bldr]

Boris - I have found that a Form's Grid will often go into an error condition if its ControlSource is Zapped.

Maybe others have not had that problem, but I have so I typically avoid it.

However, now that you mention it, instead of using SET FILTER I might suggest using an INDEX instead.
Something like:

* --- Within the Form's LOAD Method.... ---
SELECT MyTable
COPY STRUCTURE TO "c:\temp\RevuData.dbf"

USE "c:\temp\RevuData.dbf" in 0 EXCLUSIVE
SELECT RevuData
INDEX RECNO() TAG GridDsply FOR !DELETED()

<then go on to do anything else>

Ictoo - Good Luck,
JRB-Bldr

 

[cbasoz]

About ZAP:

All you need to understand the grids a bit.

lcRecordsource = thisform.myGrid.RecordSource
thisform.myGrid.RecordSource = ''
select (m.lcRecordSource)
zap
thisform.myGrid.RecordSource = m.lcRecordSource

and grid would work wonderfully well. However it is questionable if you are in need of delete all or zap a cursor often and then add new records to it. Instead you might simply use a recordsourcetype of 4-SQL.

Back to the original poster's question, best way to handle the situation would be by using an Upper() or lower() index combined with a:

set key to ...

and not 'set filter'.

With or w/o a grid it would be fast. AND instead of "locate for" one should use seek to quickly 'locate' the first item:

=seek( upper(m.lcSearch), 'myAlias', 'UpperTag' )

If you have 'set exact off' (default) then this would work perfectly well for the original problem.



Cetin Basoz
MS Foxpro MVP, MCP

 

[Mike Lewis]

Jrbbldr,

I have found that a Form's Grid will often go into an error condition if its ControlSource is Zapped.

If you zap the table (or cursor) to which the grid's ControlSource is pointing, you won't get an error; you'll just get an empty grid. A ZAP is no different from any other table update in that respect.

You might be thinking of what happens if you zap the cursor to which the RecordSource is pointing. (In fact, a grid doesn't have a ControlSource; that's a property of its columns.)

But even then, you shouldn't get an error. Where the problem arises is if you recreate the cursor using SELECT ... INTO CURSOR (or INTO TABLE). In that case, the cursor will be momentarily destroyed before it is recreated; when the cursor is destroyed, the grid will lose its columns (and all the columns' properties and methods) and you'll just see a empty space in the grid.

The solution in that situation is to remove the RecordSource just before you destroy the cursor, and re-instate just after.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[markros]

Check

http://weblogs.foxite.com/andykramek/archive/2005/03/19/174.aspx