Finding the next match after SEEK 7

[keepingbusy]

Hi all

How do I find the next match using SEEK? For example, if I enter the name DAVIES and the first record found is NOT the correct one, I need to continue searching. I've tried CONTINUE etc but that doesn't work.

Sometime back (thread184-843495) it was suggested that SEEK is the quickest way to search. I did try LOCATE but this is very slow when you have 5000+ records. The problem here is that the table contains both forename and surname in the same field (Dont shoot the messenger, it was nothing to do with me!)

This is what I have so far:

* mline01 is the variable for the NAME

SET ORDER TO LINE01
GO TOP
SET NEAR ON
SEEK TRIM(mline01)
IF FOUND()
  THISFORM.GRID1.SETFOCUS()
  nmessage=MESSAGEBOX("Found: "+ ;
  TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
  ", Continue Search?"+SPACE(5), ;
  4+32+256,"System Message")
  IF nmessage=7

*  What goes here to continue search?
*  Or any alternative method

ENDI

Look forward to any replies
Lee

VisFox Version 6 User / Windows ME

 

[ramani]

HI

When you make a seek.. after setting the order to the field on which you have indexed and then seeking that field..
Then.. just SKIP should take you to the next record which will either have the value sought or greater than that and so no more records will be there.

So there is no question of 'continue' required to find another occassion of the same occurance.

You cann also set the filter to the value of seek.. as in above example..
SET FILTER TO myField = ATRIM(mline01)
This will be also fast.




____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[Stella740pl]

You could use DO WHILE Name=TRIM(mline01) ... SKIP ... ENDDO

 

[Stella740pl]

Or SET KEY TO

 

[ramani]

Hi

May be you are looking for this..

IF FOUND()
DO WHILE .t.
THISFORM.GRID1.SETFOCUS()
nmessage=MESSAGEBOX("Found: "+ ;
TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
", Continue Search?"+SPACE(5), ;
4+32+256,"System Message")
IF nmessage=7
SKIP
IF TRIM(mline01) = myField
** ... whatever checking to make sure the
** next record has the value sought
LOOP
ENDIF
ENDIF
EXIT
ENDDO
ENDIF



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[keepingbusy]

Thank you to both of you for replying so promptly. With a bit of manipulation I ended up with this:

SET ORDER TO
GO TOP
DO WHILE NOT EOF()
  IF TRIM(mline01) $ LINE01
    THISFORM.GRID1.SETFOCUS()
    nmessage=MESSAGEBOX("Found: "+ ;
    TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
    ", Continue Search?"+SPACE(5), ;
    4+32+256,"System Message")
    IF nmessage=7
      THISFORM.GRID1.SETFOCUS()
      EXIT
    ENDI
  ENDI
  SKIP
ENDDO

Only one problem I did find and perhaps I didn't make it clear is that I was looking for a part name within a field and of course, SEEK TRIM(mline01) $ LINE01 wont work. Anyway, thanks again, I think that's solved it!

Lee....


VisFox Version 6 User / Windows ME

 

[baltman]

Wouldn't SEEK(MyVal) and then SCAN WHILE MyField=MyVal...ENDSCAN be fastest?

Brian

 

[keepingbusy]

Hi Baltman

Can you suggest how to set this up. Since posting my reply I am having some very strange behaviour when searching and not sure why this is happening. For example if I search for a name within the field (LINE01) such as WILLIAMS it works. If I search for BROWN it doesn't find any matches and I know there are 31.

If you could show a way around this I would be grateful.

Lee.....

VisFox Version 6 User / Windows ME

 

[keepingbusy]

I've now come up with the following which works. It may look a bit long winded, but it does the job and searches quickly. Still strange that if you dont store the RECNO() to a variable and issue GO mrec it doesn't work. Maybe I need to upgrade from V6.

SET ORDER TO
GO TOP
SCAN FOR TRIM(mline01) $ LINE01
  THISFORM.REFRESH
  THISFORM.GRID1.SETFOCUS()
  nmessage=MESSAGEBOX("Found: "+ ;
    TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
    ", Continue Search?"+SPACE(5), ;
    4+32+256,"System Message")
  IF nmessage=7
    SET ORDER TO LINE01
    mrec=0
    mrec=RECNO()
    THISFORM.REFRESH
    GO mrec
    THISFORM.GRID1.SETFOCUS()
    EXIT
  ELSE
    LOOP
  ENDI
ENDSCAN

Thanks again to all who replied

Lee....

VisFox Version 6 User / Windows ME

 

[baltman]

Look to what else might be happening in the THISFORM.REFRESH
method. Is it rebuilding/closing/opening/re-indexing/moving the pointer of the grid recordsource?

Since I don't think speed is your main concern, so you might just want to try something like this.

lcMySearch=[BROWN]

IF nmessage=7
 lnThisRec=recno() &&so we can skip the 1st find
  SCAN for &lcMySearch $ upper(LINE01) and recno()#lnThisRec
   *do something
  ENDSCAN
ENDIF

 

[Olaf Doschke]

Well, enough said,

as you already notice a search for cExpression $ cField
is not possible with seek, but in general next results after a seek are found by this:

Select (cAlias)
Set Order To Tag Sometag
Seek cExpression
Scan Rest While Evaluate(Key())=cExpression
...
Endscan

You may be better off, if you know the expression Key() of the tag. If you have an index "Sometag" on the field cField, this would simply be:

Select (cAlias)
Set Order To Tag Sometag
Seek cExpression
Scan Rest While &cAlias..cField=cExpression
...
Endscan

The Scan Rest loop will begin at the first found record, if Seek goes to EOF() the scan loop will not even run once.

For the experts: You may say, that Scan Rest For would be better, because it's rushmore optimizable, while Scan While isn't. But We have set the correct Index and Scanning with While is appropriate in that situation, because all matching records are together in that order after the first record found by Seek, so there is no need for rushmore optimization.

You may do a "Scan For cSomething" initially without Set Order To ... and Seek ... and rushmore would then use that appropriate index too, but that takes the extra time of finding the appropriate index and should in general be slower. It's easier, because you don't need to know, what Tag is right, but nevertheless a Scan For can only be optimized, if there is a matching index for the search expression.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, used wrong TGML...

 

[Agust304]

Read up on SET KEY in the VFP Help file.
=SEEK(mline01)
IF FOUND()
SET KEY TO mline01
* Now you can SKIP and SKIP-1 all you want
* And only see the records matchin miline01
ENDIF
* as with set filt, always remember to reset when done:
* SET KEY TO

 

[ramani]

Hi Lee,

The reason for strange effects is likely due to improper exit of the SCAN ENDSCAN loop

SCAN FOR TRIM(mline01) $ LINE01
THISFORM.REFRESH
THISFORM.GRID1.SETFOCUS()

** Do you need this line.
nmessage=MESSAGEBOX("Found: "+ ;
TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
", Continue Search?"+SPACE(5), ;
4+32+256,"System Message")
IF nmessage=7
SET ORDER TO LINE01
mrec=0
mrec=RECNO()
THISFORM.REFRESH
GO mrec
THISFORM.GRID1.SETFOCUS()

* Again do you need this line.
* The bolded lines can be shifted after endscan.. probably
EXIT
ELSE
LOOP
ENDI
ENDSCAN

While you are in a SCAN ENDSCAN loop, the evaluation of the TRIM(mline01) $ LINE01 takes place first and so the Grid1s records could create a pointer problem based on user navigating in the grid.

To avoid that DO WHILE .t. is better.

Also SET KEY pointed by Agust304 is worth considering. However, it will not work the way he has shown. That is the
SET KEY TO myExpression
in which myExpression is exact key value. In your case you are using $ and so it is not exact value, i understand.
So instead you can use a range by using..
SET KEY TO RANGE mLine, PADR(mLine,LEN(line01),'z')
This way the entire range will be shown in the grid1. Then you accept the users record choice and simply exit.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[keepingbusy]

Many thanks to all those who have posted additional replies. I've just got back after a few days away so sorry for not posting back before. I will take a look at all your suggestions soon and let you know how I get on.

I am grateful to all of you for your time and effort

Lee....

VisFox Version 6 User / Windows ME

 

[foxdbs]

<code>SEEK( <value> )
SCAN WHILE <keyexpression_for_current_index>=<value>
ENDSCAN</code>

or

<code>SEEK( <value> )
LOCATE WHILE <keyexpression_for_current_index>=<value> FOR...</code>

or

use SET KEY TO <value>

 

[KoenPiller]

Hi KeepinBusy,

Maybe you could consider to use:

[i]
SELECT * FROM myTable WHERE myField="myCondition" INTO CURSOR myCursor[/i]

You can than parse through the myCursor as if you would through the table etc. etc.

Regards,

Koen

 

[keepingbusy]

Baltman

Tried the below by changing lcMySearch to mline01 but nothing happens when I try a search. Am I missing something?

IF nmessage=7
 lnThisRec=recno() &&so we can skip the 1st find
  SCAN for &lcMySearch $ upper(LINE01) and recno()#lnThisRec
   *do something
  ENDSCAN
ENDIF

Olaf

For the experts: You may say, that Scan Rest For would be better

Select (cAlias)
Set Order To Tag Sometag
Seek cExpression
Scan Rest While &cAlias..cField=cExpression
   ...
Endscan

If my variable is mline01, how would you incorporate this into your suggestion?

agust304
Read up on SET KEY in the VFP Help file

Looking at the example given, my understanding of this is that a seperate "Browse" window would be created with only the records fitting the criteria matching my variable. Am I right?

CLOSE DATABASES
USE customer
SET ORDER TO postalcode
SET KEY TO RANGE '40000', '43999'
BROWSE
IN cTableAlias | nWorkArea

Ramani

You asked if I needed some of the lines in the following code?

SCAN FOR TRIM(mline01) $ LINE01
  THISFORM.REFRESH
  THISFORM.GRID1.SETFOCUS()

** Do you need this line.
  nmessage=MESSAGEBOX("Found: "+ ;
    TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
    ", Continue Search?"+SPACE(5), ;
    4+32+256,"System Message")
  IF nmessage=7
    SET ORDER TO LINE01
    mrec=0
    mrec=RECNO()
    THISFORM.REFRESH
    GO mrec
    THISFORM.GRID1.SETFOCUS()

* Again do you need this line.
* The bolded lines can be shifted after endscan.. probably
    EXIT
  ELSE
    LOOP
  ENDI
ENDSCAN

The reason for including the first THISFORM.REFRESH and THISFORM.GRID1.SETFOCUS() is because when the first record is found matching the criteria it gives the user an opportunity to view the hightlighted record in the forms Grid. If its not the one the user clicks no and the search continues etc....

The second part you mentioned, I have left out and this works fine. I'm not sure why it wouldn't work without it in the first place, but it does now. They say if it 'aint broke, dont fix it!!

foxdbs
Looking at your suggestion, how does this work? If the user doesn't find the correct record, how do they "Look" for the next one?

Koen
Thanks for your post. I will consider this suggestion as it may be a good idea to allow the user to look at all the names found. My only thought here is the fact that both surname and forename are in the same field, it could be more or less similar to my existing Grid if that makes sense.

How I've resolved it: For the time being I have made some changes as suggested by Ramani and that seems to be working fine for the moment. The code looks like this

SCAN FOR TRIM(mline01) $ LINE01
  THISFORM.REFRESH
  THISFORM.GRID1.SETFOCUS()
  nmessage=MESSAGEBOX("Found: "+ ;
    TRIM(LINE01)+" - "+TRIM(TRANSACT)+ ;
    ", Continue Search?"+SPACE(5), ;
    4+32+256,"System Message")
  IF nmessage=7
    EXIT
  ELSE
    LOOP
  ENDI
ENDSCAN

A million thanks to all of you for showing an interest in this thread

Lee....

VisFox Version 6 User / Windows ME

 

[foxdbs]

-> keepingbusy

You can write IF SEEK.
But if you use it without IF.. and SEEK will find nothing, then you stay behind the last record (on EOF), so SCAN WHILE or LOCATE WHILE will stay on same place (doesn't make anything). So both is the same.

<code>
IF SEEK( value )
SCAN WHILE keyexpression_for_current_index = value
ENDSCAN
ENDIF
</code>

or

<code>
IF SEEK( value )
LOCATE WHILE keyexpression_for_current_index = value
FOR...
ENDIF
RETURN FOUND() && if previous is a function
</code>

 

[foxdbs]

of course LOCATE WHILE.. FOR.. shell be on one line.

(+ I don't know why <code> doesn't work for me)