{{tag>category:"OpenInsight Specific"}} [[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]] ==== Something Like Btree.Extract..... (OpenInsight Specific) ==== === At 09 FEB 1999 11:33:47AM Greg James, ISIS, Inc. wrote: === 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 ---- === At 09 FEB 1999 01:10PM Mike Ruane, WinWin Solutions Inc. wrote: === 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 ---- === At 15 FEB 1999 02:14PM Cameron Revelation wrote: === 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: * 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 Cameron Purdy Revelation Software --- 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 ---- === At 15 FEB 1999 06:03PM Richard Hunt wrote: === 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. ---- === At 15 FEB 1999 06:15PM soundex utility wrote: === 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. ---- === At 16 FEB 1999 09:41AM Oystein Reigem wrote: === 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 - ---- === At 17 FEB 1999 10:43PM Ron Wielage wrote: === 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 ---- === At 24 FEB 1999 08:28PM akaplan@sprezzatura.com - [url=http://www.sprezzatura.com]Sprezzatura, Inc.[/url] wrote: === You're best bet is to use [url=http://www.revelation.com/WEBSITE/knowledge.nsf/461ff2bd5a8ddeed852566f50065057d/922322b08a81d2ff852563e200499a00?OpenDocument]Btree.Read[/url]. 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 [url=http://www.sprezzatura.com]Sprezzatura, Inc.[/url] [img]http://www.sprezzatura.com/zz.jpg[/img] ---- === At 24 FEB 1999 09:59PM akaplan@sprezzatura.com - [url=http://www.sprezzatura.com]Sprezzatura, Inc.[/url] wrote: === There's also a soundex in the on-line help as part of the User Index Facility in Btree.Extract. akaplan@sprezzatura.com [url=http://www.sprezzatura.com]Sprezzatura, Inc.[/url] [img]http://www.sprezzatura.com/zz.jpg[/img] [[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=766F61A6BA51A74685256713005AFC04|View this thread on the forum...]]