Locator, multi field key tweaking

[JPMacDonald]

BACKGROUND:
I have a typical MSSQL table with LastName and FirstName columns and a key on that table made up of both fields. I want to be able to use the locator filed to allow the user to enter Smith, John and have it locate to the first John Smith in the table.

APPROACH:
Digging around a bit I dropped the following code in the "2 Before Browse Loop/ 11 Set Filter" embed and it does exactly what I want in terms of locating to the proper name in the table.

IF Loc:LocateField = 'PROSP:LAST_NAME'
   X# = INSTRING(',',Clip(loc:LocatorValue),1,1)
   IF X# > 0
      ThisView{prop:Filter} = 'UPPER(PROSP:FIRST_NAME) >= <39>' & LEFT(UPPER(CLIP(SUB(loc:LocatorValue,X#+1,30)))) & '<39>'
      loc:LocatorValue = Clip(SUB(loc:LocatorValue,1,X#-1))
   END
END

PROBLEM:
After the browse is refreshed and I am looking at the correct data if I press the Previous button it starts the browse at the first name in the table and not the names just prior to Smith. If however I press the Next button, which works as expected, and then the Previous button it all works as expected.

I am guessing it has to do with the FirstValue pointer but I cannot get a handle on it. Can anyone help with what other setting I have to play with in order to get this last piece of the puzzle working?

SOLUTION:
Your terrific and simple answer goes here 


Thanks

Parker

[Bruce]

I think maybe you're making something really easy into something really hard. I'd probably approach it like this (with no hand-code required.)

a) for firstname field, in the browse, Sort tab, set Locator Field(s) to
PROSP:FIRST_NAME,PROSP:LAST_NAME

b) do the same as (a) for the lastname field in the browse.

c) Set the locator type to "Search" (browse options, Locator tab).

Now the user could type in
John Smith
Smith John
John
Smith
or whatever other combination they like, and they're probably get the right record.

If they entered just John Smith that's an OR locator, so they'd get all the Johns and all the Smiths.
However if they entered +John +Smith those become AND terms, so _only_ John Smiths would be displayed.

One could probably change the default terms from OR to AND if one wanted to as well.

Cheers
Bruce

[JPMacDonald]

"I think maybe you're making something really easy into something really hard"

Bruce,

That is my usual MO, but I think with good reason this time.

I tried your suggestion searching for Lisa MacDonald, problem is the results are not intuitive as the first returned row in the browse is Lisa Anderson. I may have to page through dozens of pages to get to Lisa MacDonald.

Tried the +Lisa +MacDonald which does return a filtered result set of just the Lisa MacDonalds but it took 35 seconds to do it with a table of only 20,000 entries. My production table will be over 100,000 rows.

Using my simple embed code positions me to the first Lisa MacDonald with sub second response time, just what the doctor ordered.

If I could solve the problem with the immediate pressing of the previous button I think I would have a great solution for this type of common locator. Any hints???

Regards

Parker

[Bruce]

what kind of locator type are you using?
to "go back" a age it would need to be Positional methinks.

When you press Previous, does it go through your code - ie does x# have a value on that pass through?

cheers
Bruce

[JPMacDonald]

Yes, the locator type is Position.

On Previous it does hit my code but only the first IF and INSTRING test is executed (confirmed with debugview), since the locator value is blank it does not try to split the name in 2.

Regards

Parker

[JPMacDonald]

Hmmm,

Adding this line after the p_web._SetView statement seems to solve the problem with the immediate pressing of the Previous button:

IF X# > 0 THEN p_web.SetSessionValue('BrwProspects_Filter_' & loc:RandomBrowseId,'') .


Regards

Parker