Adding a Btree index to a name column provides an effective way of searching a table, if a name is known, e.g., searching for 'James', Smith, etc.

How could I provide a similar function, but allow searches for fragments of a name, e.g., 'Jam', 'Sm', etc?

What I really want to do is show a 'view' of the table around the search criteria. Perhaps display the 10 records preceding 'Sm' and the 10 records following 'Sm'.

Is this possible? If so, does anyone know of an example that does this?

Also, where can I get information about the records contained in an indexed table? If I put a Btree index on a column 'COLUMN' in a table called 'TABLE', a new table '!TABLE' is created with records such as 'COLUMN*', 'COLUMN*ROOT', etc. Where can I find out more about these structures?

Thanks

Greg-

For a fragment, use the or operators to get ending with or starting with, respectively.

For the range, you can use something called btree.read. It was documented in the technical bulletins.

Hope it helps-

Mike Ruane

WinWin Solutions inc.

WWW.WinWinSol.Com

Hi Greg,

I'm planning to doc a soundex utility for 3.8, but here is some raw code. To search on a field named , create a symbolic called _SOUNDEX with @ans=soundex({}). Use R/List to search, like:

<code>
* lostfocus event on "CUSTOMER_NAME" control

declare function   Get_Property, Send_Event, Get_Status, Soundex
declare subroutine Set_property

$insert Msg_Equates
$insert Popup_Equates
$insert Rlist_Equates

* user has entered "best-guess" customer name
Name=Get_Property(@window: ".CUSTOMER_NAME", "TEXT")
List=Soundex(Name)
if len(List) then
  Def="
  Def=Processing..."
  Def=U"
  MsgUp=Msg(@window, Def)

  List=quote(List)
  swap @vm with '" "' in List
  RList("SELECT CUSTOMERS WITH CUSTOMER_NAME_SOUNDEX EQ ": List: " BY CUSTOMER_NAME", TARGET_ACTIVELIST$, "", "", "")
  if Get_Status() else
    Ans=Popup(@window, "", "CUSTOMER_POPUP")
    if len(Ans) then
     * fill in the selected customer number
      Set_Property(@window: ".CUSTOMER_NO", "DEFPROP", Ans)
    end
  end

  Msg(@window, MsgUp)
end

return 1

</code>

Cameron Purdy

Revelation Software

—

<code>
function Soundex(Key)

declare subroutine Xref

equ ALPHABET$ to "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
equ SOUNDEX$  to "01230120022455012623010202"

* parse the words out of the "key"
List=Key
convert @lower.case to @upper.case in List
Xref(List, " .,;:'/\(){}&*^%$#@!" :'"', "", 1)
transfer @ans to List

* soundex the individual words
Cnt=count(List, @vm) + (List # "")
Ret="
for i=1 to Cnt
  Word=List
  gosub CreateCode
  if len(Code) then
    Ret=Code
  end
next i

return Ret

* Soundex Rules
* All Soundex codes have 4 alphanumeric characters no more, no less 
* 1 Letter 
* 3 Digits 
* The Letter of the name is the first character of the Soundex code. 
* The 3 digits are defined sequentially from the name using the Soundex Key below. 
* Adjacent letters in the name which belong to the same Soundex Key code number
* are assigned a single digit. See examples 2 and 3 below 
* If the end of the name is reached prior to filling 3 digits, use zeroes to
* complete the code. 
* All codes have only 4 characters, even if the name is long enough to yield more. 
* The Soundex Key
* 1        b p f v 
* 2        c s k g j q x z 
* 3        d t 
* 4        l 
* 5        m n 
* 6        r 
* no code  a e h i o u y w 
CreateCode:
  Pos =1
  Code="
  Prev="
  loop
    ch=WordPos,1
  while len(ch)
   * translate
   
        return
    end case

    begin case
      case Pos=1
        Code := ch
      case id and id # Prev
        Code := id
    end case

    Pos += 1
    Prev=id
  until len(Code)=4
  repeat

  if len(Code) < 4 then
    Code=(Code: "0000") 1,4
  end
return

</code>

well i use "CROSSREFERENCE" instead of "BTREE" and i use a subroutine to control the index.

see you could set up a subroutine to break down the string into what ever you wish. for example….

STRING=AMERICAN EXPRESS"

my subroutine set up indexing for the following…

AMERICAN

AMERICAN*EXPRESS

EXPRESS

you could set up indexing for like…

STRING=AMERICAN EXPRESS"

so that the indexed values are…

A

AM

AME

AMER

AMERI

and so on just collect the values and delimit them with like @VM

then set the index up to delimit using the @VM

be very careful what you ask for cus if you index with too few characters the index will swell up way to big for practical use. Maybe

start with the first 3 characters and 4, 5, 6 ans so on.

i find this to be very user friendly and user helpful.

just wanted to add so to your thoughts…

1) you might consider to remove duplicate characters like

 PASSAGE turns into PASAGE

 MESSAGE turns into MESAGE

2)i remove the trailing S's and preceeding zero's

it seems to simplify the misspelling of words on lookups.

Greg

Do you need more help on this?

I have wanted to make my own index lookup function that can list sets of index values, e.g 50 values starting with a certain values (or with the closest value if the value is not in the index), forwards or backwards. That's not so different from what you want. It's been difficult to get information about the structure of index tables, but I think I know enough now. I have written my function and it seems to work.

- Oystein -

Cam,

Looks just like a soundex I put together a couple years ago, except we did a length of 8 character instead of four. And when we returned the results we put those spelled the same as the query before those that just "sounded" like it. Works well.

Ron Wielage

Standard Life Insurance Company of Indiana

You're best bet is to use Btree.Read. It will let you narrow down to a range of values in an index. We use it for an autofill type feature in many of our dropdowns. It's all encapsulated. Drop us a line an we'll mail it off to you.

akaplan@sprezzatura.com

Sprezzatura, Inc.

There's also a soundex in the on-line help as part of the User Index Facility in Btree.Extract.

akaplan@sprezzatura.com

Sprezzatura, Inc.

View this thread on the forum...