Página 1 de 9
How to SCAN substrings in a dbf - Summer 87
Enviado: 03 Jan 2013 17:20
por marge0512
Hello, I need a command that works like the "Like" command in SQL. Our system was programmed in Clipper87 and we have to type in the exact company name using hyphens and everything else. If you don't type the name in the exact way, you would get Record Not Found.
For example,searching for "The Burling - Huntor Store" would have to be typed in that exact way in order to find that record. Is there a way that i could just type in "The Bur" and it would find it?? I would also like to have all records to sort from "The Bur" so the user may easily select the row.
I hope I'm explaining this in a way that is understandable.
This is what I have now:
Código: Selecionar todos
procedure REC_FND
private REC2FIND, SAV_REC
* Save current position in database if search fails
*--------------------------------------------------
SAV_REC = recno()
set color to &GET_CLR
read
* Continue if escape wasn't pressed
*----------------------------------
if LASTKEY()#ESC_KEY
*--------------------------------
REC2FIND = space(40)
do DISP_MSG with 'Client (include punctuation): '
@row(),col() get REC2FIND
read
* Continue if the escape key was not pressed
*-------------------------------------------
if lastkey() # ESC_KEY
* Remove trailing spaces
*-----------------------
REC2FIND = upper(trim(REC2FIND))
* Get client index
*--------------------
set order to 7
* Search using the key
*---------------------
set exact off
seek REC2FIND
set exact on
* Return to the first index
*--------------------------
set order to 1
* If found, put the highlight bar on the record
*----------------------------------------------
if !eof()
do SCRL_TOS
* If not found, tell the user
*----------------------------
else
do DISP_MSG with [Client, ]+trim(REC2FIND)+[, not found. Press a key.]
inkey(0)
go SAV_REC
endif
endif
endif
return
*
Thanks in advance!
Nota de Moderação:por
Pablo César: Title was modified to keep same matter in different situations.
[][]
Command Needed
Enviado: 03 Jan 2013 22:53
por fladimir
(Using sometimes Google Translator)
Welcome friend...
If I understand all a way to do this is create a temporary index with the key that you want, where you receive only the records that contain your key...
For you have a return how the "Like" command in SQL you use the operator '$'
For example:
Código: Selecionar todos
Procedure Main
//-- Declare your variables how PRIVATE because in FOR expressions if you use 'Macro' (&) you will need
PRIVATE cYourInput := ''
PRIVATE bFilter := ''
cYourInput := "The Bur"
bFilter := "cYourInput $ cMyField" //-- Here you put something how this.
Use Table Alias Test
Index on FieldName to IndexName FOR &bFilter
//-- Here you will only show what was filtered in work area indexed
While Test->(!EOF())
? 'Your expression' + Test->cMyField, Test->YourFields, Test->OtherField, Test->EtcField
Test->(dbskip())
END
Test->(dbclosearea())
Return NIL
Best Regards...
Command Needed
Enviado: 03 Jan 2013 23:13
por alxsts
Hi!
Just to clarify:
in the above example, the "dollar operator" ($) was used. It works like the SQL LIKE command: starts with ('%like'), ends with ('like%') or contains ('%like%').
See details and example below:
$
Substring comparison--binary (Relational)
------------------------------------------------------------------------------
Syntax
<cString1> $ <cString2>
Type
Character, memo
Operands
<cString1> is a character or memo value that is searched for in
<cString2>.
<cString2> is a character or memo value within which <cString1> is
sought.
Description
The $ operator is a binary relational operator that performs a case-
sensitive substring search and returns true (.T.) if <cString1> is found
within <cString2>.
Examples
. This example illustrates the case-sensitivity of the substring
operator ($):
? "A" $ "ABC" // Result: .T.
? "a" $ "ABC" // Result: .F.
Command Needed
Enviado: 05 Jan 2013 16:40
por Maligno
The tips of our friends are valid, but you need to pay attention to an important point: you always have to enter the names correctly, otherwise you will not find anything. It's an easy problem to predict.
So I want to leave a tip: in the future, consider using something like SOUNDEX algorithm (not in Summer'87, of course), where you will have possibility to enter any name, even with small errors. The names, especially names of people, often are difficult to write.
Note: the SOUNDEX algorithm, as it was originally conceived (early 20th century), is not ideal. You need to use something more modern to get a really good result.
Command Needed
Enviado: 06 Jan 2013 00:07
por rochinha
Hi little friend,
With this post you can create selects like SQL in pure Clipper
See here
Command Needed
Enviado: 06 Jan 2013 10:44
por Euclides
Amiguinho Rochinha...
O .CH no ´post´ citado, é da classe TSelector que é um Controle do Fivewin (por sinal, criado pelo nosso amiguinho Ramón Avedaño).
V. teria o .CH pertinente?
[]´s e Feliz 3*11*61
Euclides
Command Needed
Enviado: 06 Jan 2013 12:56
por rochinha
Amiguinho,
Peguei o conteúdo do arquivo errado. Tem o mesmo nome mas o conteúdo era diferente. Hehehe! demorei 6 anos para perceber o erro. Graças a voce, e conhecedor das ferramentas que usa, fui alertado.
Já está retificado.
Command Needed
Enviado: 06 Jan 2013 13:22
por alxsts
Hi!
Fladimir's code will not work because the FOR clause is not suported in Clipper Summer'87 INDEX command. Neither the use of inline assignment operators (:=).
Summer'87 Syntax: INDEX ON <key exp> TO <file>/(<expC>)
Command Needed
Enviado: 07 Jan 2013 12:07
por rochinha
Friend,
Change
":=" for
"=".
Try:
Or:
Command Needed
Enviado: 07 Jan 2013 13:04
por alxsts
Olá!
Segundo a sintaxe no manual do Clipper Summer'87, a cláusula FOR não existe no comando INDEX...
Command Needed
Enviado: 08 Jan 2013 11:47
por marge0512
Thanks for all of your responses. I'm sorry I'm just getting back this now. I was out for a few days.
I am slightly confused though.......I only work with Clipper when the user needs a change which is about once a year so I consider myself still a newbie.........is there anyway i can incorporate the dollar operator into my existing code?
The user needs the search to work as if it was the %like% command. So, for example, if he wanted to search for "The Burlington" and doesn't remember if he entered the information with the word "The", he could enter "Bur" and still find "The Burlington"?? Would I still need to use the function that was given?
Command Needed
Enviado: 08 Jan 2013 14:10
por alxsts
Hi!
I've made some changes to your original code. Please, check to see if it meets what your user needs.
Código: Selecionar todos
procedure REC_FND
private REC2FIND, SAV_REC
* Save current position in database if search fails
*--------------------------------------------------
SAV_REC = recno()
set color to &GET_CLR
read
* Continue if escape wasn't pressed
*----------------------------------
if LASTKEY()#ESC_KEY
*--------------------------------
REC2FIND = space(40)
do DISP_MSG with 'Client (include punctuation): '
@row(),col() get REC2FIND
read
* Continue if the escape key was not pressed
*-------------------------------------------
if lastkey() # ESC_KEY
* Remove trailing spaces
*-----------------------
REC2FIND = upper(trim(REC2FIND))
* cannot use an index to search partially. Go top and perform full table scan...
*-----------------------------------------------------------------
GO TOP
DO WHILE LASTKEY() # ESC_KEY .And. ( .Not. Eof() )
IF REC2FIND $ UPPER( <customer-name-Field> ) * =============> suplly the correct field name here
* If found, put the highlight bar on the record
do SCRL_TOS
* await for user input
do DISP_MSG with [Client, ]+trim(REC2FIND)+[, found. Press <ESC> to EXIT, any other key to RESUME searching...]
INKEY(0) * =============> if DISP_MSG generates a wait state, remove this line
ENDIF
SKIP
ENDDO
IF EOF() .And. LASTKEY() # ESC_KEY
do DISP_MSG with [Client, ]+trim(REC2FIND)+[, not found. Press a key.]
inkey(0)
go SAV_REC
ENDIF
*----------------------------------------------
endif
endif
return
*
Command Needed
Enviado: 08 Jan 2013 16:18
por rochinha
Friend,
Uses for dollar operator:
This cause .T. response.
Maybe here, causes .F. response.
Equalize the search
Código: Selecionar todos
...
set filter to upper( "Bur" ) $ upper( "The Burlington" )
...
In all these cases the search had been too slow
For all uses:
Código: Selecionar todos
...
set filter to upper( M->MyVar ) $ upper( Field->MyFieldName )
...
Command Needed
Enviado: 08 Jan 2013 18:08
por marge0512
Thanks for the code! I tried this and played around with it a little but it seems to select what i want it to search for plus more. I'm not sure why. For example, I chose the word "Inc" (which will select many) and it will bring back records that have the word "Inc" but will also select records that do not have the word "Inc" and these records will show up in between the records that do have "Inc". I will keep trying.
Command Needed
Enviado: 08 Jan 2013 18:14
por alxsts
Hi!
Seems so strange... if possible, attach a small DBF with sample data.
I placed the search ($ command) inside a loop. Is it what you need?
Check details. Sometimes the problem is very well hidden...