Locate command

[agoodrich]

Let say I have a field with peoples names. I want to to search by any part of the name. Is there a wild card or somthing to do this in vfp.

"John M Lastname"

If I only know the first part of his last name "Last" how would I write a command to find him.

locate for name = "*Last*" ??

Thanks

 

[craigsboyd]

Select ("MyTable")
LOCATE FOR LIKE("*Last*", MyField)
if found("MyTable")
*!* Do Something with the matching record
endif

http://www.sweetpotatosoftware.com

 

[keepingbusy]

Hi agoodrich

Sometime back I was helped out with a similar question, take a look at thread184-843495

This appeared to be the fastest way (For my APP anyway!)

I've added your bit at the start

STORE "John M Lastname" TO mlocate
USE YOURTABLE
IF NOT EMPTY(mlocate)
  SET ORDER TO YOURINDEX
  GO TOP
  SET NEAR ON
  SEEK TRIM(mlocate)
  IF FOUND()
*    DO WHATEVER HERE....
  ELSE
    =MESSAGEBOX("[ "+TRIM(mlocate)+" ] Not Found"+ ;
      space(10),0+64+0,"System Message")
  ENDI
ENDIF

The only thing here is if there is more than one "LAST" you may need to a DO WHILE etc...

Good luck
Lee......

VisFox Version 6 User / Windows ME

 

[mspratt]

Hi agoodrich,

LOCATE FOR "Last" $ NameField

This is NOT Rushmore optimizable, so, if the table is big it's going to be slooooow.

Regards,

Mike

 

[craigsboyd]

keepingbusy,

I don't see how your solution would allow looking for the substring "Last" in the field. It appears you are seeking for the entire "John M Lastname" in the field utilizing an index. You may have misunderstood what was being asked for or I am misunderstanding your suggestion given:

If I only know the first part of his last name "Last" how would I write a command to find him.

http://www.sweetpotatosoftware.com

 

[keepingbusy]

Yep

My mistake, too many late nights!
Lee.....

VisFox Version 6 User / Windows ME

 

[bobmpalmer]

Similar to your solution Craig but we use
LOCATE for AT(ALLTRIM(UPPER("yourtext")),UPPER(NAME),1)>0

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[mspratt]

Hi Bob,

I wonder if the ALLTRIM(UPPER("yourtext")) is evaluated for each record or just once.

LOCATE for AT(ALLTRIM(UPPER("yourtext")),UPPER(NAME),1)>0
vs.
SearchText=ALLTRIM(UPPER("yourtext"))
LOCATE for AT(SearchText,UPPER(NAME),1)>0
or
SearchText=ALLTRIM(UPPER("yourtext"))
LOCATE for SearchText $ UPPER(NAME)

Regards,

Mike

 

[bobmpalmer]

Mike
In the actual code we open a single text field modalform

DO myform with "" TO lcreply
IF !EMPTY(lcreply)
LOCATE for AT(ALLTRIM(UPPER(lcreply)),UPPER(ourfield),1)>0
ENDIF
***Display the record IF FOUND() etc.

But we also use it on a global search in the WHERE clause of a SELECT

 SELECT category,created,freetext,start_time,editor,"" as person FROM events ;
WHERE AT(ALLTRIM(UPPER(lcreply)),UPPER(events.freetext),1)>0 .and. LEFT(events.itemref,2)="O-" .and. events.residentid=thisform.residentid ;
ORDER BY category ;
INTO CURSOR cevents

To enable a report to be printed I don't appear to have a problem with it and certainly the CURSOR returns multiple items.

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[mspratt]

Hi Bob,

I was just wondering which would be faster. Guess when I get a chance I'll write something to benchmark them.

Regards,

Mike

 

[bobmpalmer]

Mike

That would be useful though I have to add we're using this AT() search method because "ourfield" is a memo field and of course can be any size. But if it adds to the equation the SELECT is scanning a table with over 800,000 records and does'nt take long.

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[craigsboyd]

You could get rid of the nested calls to the Upper() function by switching from AT() to ATC().

As for an SQL statement or Locate I would still use LIKE, it's faster, simpler, Rushmore optimizable, and it can do a variety of searches via the wildcards that AT() cannot do (not easily anyways). Note: that there is a difference between the LIKE() function and the SQL LIKE operator, not the least of which is the syntax and wildcard characters.

http://www.sweetpotatosoftware.com

 

[mspratt]

Hi all,

Ran this with some interesting results.

USE c:\codebook\sales\data\cust ORDER cname

* Customer name is ZYNRGY cust.dbf has 7562 records
* ZYNRGY  is last record in cname ORDER
SearchText=ALLTRIM(UPPER("ynrg"))

LOCATE
nSECONDS = SECONDS()
LOCATE for SearchText $ UPPER(CNAME)
? SECONDS() - nSECONDS

LOCATE
nSECONDS = SECONDS()
LOCATE for AT(ALLTRIM(UPPER("ynrg")), UPPER(CNAME), 1) > 0
? SECONDS() - nSECONDS

LOCATE
nSECONDS = SECONDS()
LOCATE for AT(SearchText, UPPER(CNAME), 1) > 0
? SECONDS() - nSECONDS 

LOCATE
nSECONDS = SECONDS()
SCAN FOR SearchText $ UPPER(CNAME)
	? SECONDS() - nSECONDS
ENDSCAN

Ran this several times using cut and paste to change the order. All scan/locates had similar results. The first scan/locate took .240 - .270 seconds and the rest took .030 - .050 seconds.

Here is the interesting bit. When I removed the order and put "urlington" as the SearchText (BURLINGTON is the last physical record) the first scan/locate took .040 - .050 seconds and subsequent scan/locates took .010 - .012 seconds.

Any ideas why removing the order speeds this up by a factor of 5?

Regards,

Mike

 

[craigsboyd]

agoodrich,

Have any of these suggstions or solutions worked for you?

http://www.sweetpotatosoftware.com

 

[bobmpalmer]

Mike

I would guess its to do with Rushmore. If the index expression is not Optimized for the search condition then the index gets in the way. Just a thought!



Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[agoodrich]

Yes, I have been able to seach in many different ways now. Sorry about the slow response.. I have been busy working on a new phone system the last few days. But yeah thanks for all the great ways to locate / search.

Aaron

 

[bobmpalmer]

Craig

I tried substituting
LIKE(lcreply,events.freetext) in our SELECT (see earlier post) instead of AT(ALLTRIM(UPPER(lcreply)),UPPER(events.freetext),1)>0 bearing in mind that freetext is a memo field with any case the user happens to use and in one record I have "Cleaning carried out" etc etc when I used our AT() with "clean" it found the record, when I used LIKE() it did'nt. I tried "Clean*" and also "clean???" Though thanks for the ATC() tip.

Bob Palmer

http://www.resisystems.co.uk

The most common solution is H2O!

 

[craigsboyd]

LIKE(lcreply,events.freetext) in our SELECT (see earlier post)

The Like() function and the SQL Like operator are different. The syntax is different as well as the symbols for wildcards. That's why it failed for you.

http://www.sweetpotatosoftware.com