To Mr Nages xbrowse INCREMENTAL SEARCH

[Silvio.Falconi]

Mr Nages,
on this topic viewtopic.php?f=3&t=26404

Mr Nages
Can you make a small test sample with Incremental Filter but with some fields
sample for customer.dbf Last+First+State

because I Know How make it for only one field. Thanks!!

Simple

Code: Select all  Expand view

oBrw:cFilterFld := "LAST + FIRST + STATE"
 

But I not understood
I have a big dbf on xbrowse and I must search a record making a filter step by step when the user insert a value into some gets




On this picture you can see there are some gets , when the user insert a Key into a get the xbrowse must search the value into dbf and refresh the xbrowse

@ 22, 85 GET aGet[3] VAR cBanca SIZE 130, 10 PIXEL OF oFrmImportaBanche
@ 36, 85 GET aGet[4] VAR cFiliale SIZE 130, 10 PIXEL OF oFrmImportaBanche

aGet[5]:bKeyDown := { | nKey | KeyChar( oBrwImportaBanche, nKey) }

static function KeyChar( oBrw, nKey)

If nKey == VK_BACK .and. ! Empty( cSeek )
( oBrw:cAlias )->( oBrw:cFilterFld :="ABI+CAB+ISTBAN+FILBAN+CAP+LOCALITA+PROV" )
return 0
elseIf nKey > 31
( oBrw:cAlias )->( oBrw:cFilterFld :="ABI+CAB+ISTBAN+FILBAN+CAP+LOCALITA+PROV" )
return 0
Endif

return nil

How I can make it ?

[Silvio.Falconi]

Now I found this solution :
for each get I insert this function

Code: Select all  Expand view

static FUNCTION  BrowseSeach(cAbi,cCab,cBanca,cFiliale,cCap,cLocalita,cProvincia,oBrw)
       FIELD ABI,CAB,ISTBAN,FILBAN,CAP,LOCALITA,PROV
       local cCond:="", bCond

           cCond := "ABI = alltrim('" + cAbi  + "' ) .AND. "
           cCond += "CAB = alltrim('" + cCab  + "' ) .AND. "
           cCond += "ISTBAN = alltrim('" + cBanca  + "' ) .AND. "
           cCond += "FILBAN = alltrim('" + cFiliale  + "' ) .AND. "
           cCond += "CAP = alltrim('" + cCap  + "' ) .AND. "
           cCond += "LOCALITA = alltrim('" + clocalita  + "' ) .AND. "
           cCond += "PROV = alltrim('" + cProvincia + "' ) "

             bCond    := &( "{ || " + cCond + " }" )

        ( oBrw:cAlias )->( DBSETFILTER( bCond, cCond ), DBGOTOP() )
        oBrw:refresh()
       RETURN NIL

But the search is too slow

do you think about it ?

Exist a good solution to have a fast search ?

[Silvio.Falconi]

Any help please...
I think can exist something fast to refresh xbrowse table

[Rick Lipkin]

Silvo

If you are using DbfCdx .. have a look at Ord.Ch and use scopes

Code: Select all  Expand view

 OrdScope( 0, cCondition ) ; OrdScope( 1, cCondition )

 

Rick Lipkin

[lucasdebeltran]

Silvio,

If you don´t provide a full working example is very difficult to help you, and thus you don´t get a reply.

[Silvio.Falconi]

lUCAS,
THE DBF HAVE 61819 RECORD AND IT SIZES IS 16.120KB i CANNOT SEND IT

but I think a sample can be made with customer.dbf and with the search of some fields


First
Last
City
Age

when the user insert a value on these get the xbrowse must be refreshed
Now I tried with dbsetfilter as you can see on post up but it is too slow and when I insert a key on a get it is too slow

[lucasdebeltran]

Silvio,

You are smart enough to reduce the dbf or just upload it to, for instance, uploaded.to, rapidgator, etc.

[James Bott]

Silvio,

Big databases can't be handled the same as small ones.

Here is how to use scopes to get a fast "filter."

First add an index to your app:

index on UPPER(ABI+CAB+ISTBAN+FILBAN+CAP+LOCALITA+PROV) to index1

[Note that this will be a huge index]

Then for the search, set your condition:

cCond:= UPPER(ABI+CAB+ISTBAN+FILBAN+CAP+LOCALITA+PROV)

Then set the scope (lower and upper bounds are the same):

set index to index1
ordScope(0,cCond) // lower bound
ordScope(1,cCond) // upper bound
go top

This will be much faster than using a filter.

If you want to use the search-on-keystroke that you are using now, it is still going to be kind of slow since you may be paging the index in and out of memory. The PC's are going to need a lot of RAM to hold that index. I would suggest that instead of processing each keystroke, you let them type it all in, then press a search button.

Search the forum for "filters" and/or "scopes" and you will find lots of discussions about this.

James

[Silvio.Falconi]

James,

this is a sample test

Code: Select all  Expand view

#Include "Fivewin.ch"
#Include "dtpicker.ch"
#include 'xbrowse.ch'
#include "constant.ch"
#define LIGHTCYAN        nRGB( 203, 225, 252 )
  REQUEST DBFCDX
  REQUEST DBFFPT
  EXTERNAL ordkeyno, ordkeycount,ordcreate,ordkeygoto
Function Test()
Local oFrm
   Local oBrw
   Local  cAbi      := space(5)
   Local  cCab      := space(5)
   Local  cBanca    := space(106)
   Local  cFiliale  := space(40)
   Local  cCap      := space(5)
   Local  cLocalita := space(42)
   Local  cProvincia:= space(2)
   Local ogrp1
   Local oBut1
   Local oCerca
   Local aGet[7]
          //arrays per fare il xbrowse
       Local aCampi  := {"ABI","CAB","ISTBAN","FILBAN","INDIR","CAP","LOCALITA","PROV","SWIFT"}
       Local aNomi   := {"Abi","Cab","Istituto Bancario","Filiale","Indirizzo","C.A.P.","Località","Provincia","Codice Swift"}
       Local aSizes  := {30,30,120,120,120,30,60,30,40}
         // coordinates della dialog
       Local nRight  := 75
       Local nBottom := 22
       Local nWidth :=  Max( nRight * DLG_CHARPIX_W, 180 )
       Local nHeight := nBottom * DLG_CHARPIX_H
       RddSetDefault( "DBFCDX" )
       SetGetColorFocus()
       USE BANCHE  ALIAS  BA
         Define DIALOG oFrm TITLE "Importa Banche - " ;
         SIZE nWidth, nHeight PIXEL COLOR 0, 14215660  ;
         STYLE nOr( WS_OVERLAPPEDWINDOW )
        // //XBROWSE
                   @ 80,1 XBROWSE oBrw OF oFrm SIZE -2,-10 PIXEL ;
                   DATASOURCE "BA" ;
                   COLUMNS aCampi  HEADERS aNomi  COLSIZES aSizes
                   oBrw:CreateFromCode()
      @    1,    4 GROUP oGrp1 TO  65, 255 LABEL "Cerca " PIXEL;
              OF oFrm COLOR 0, 14215660
      @   12,   6  SAY "&Abi:"                  SIZE  25,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   12,  45  SAY "&Cab:"                  SIZE  25,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   24,  11  SAY "&Descrizione  Banca:"   SIZE  55,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   38,  11  SAY "&Descrizione Filiale :" SIZE  55,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   54,  11  SAY "Ca&p:"                  SIZE  25,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   54,  64  SAY "Lo&c:"                  SIZE  25,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   54,  217 SAY "Pro&v:"                 SIZE  25,   7 PIXEL  OF oFrm COLOR 0, 14215660
      @   10,  33  BUTTON oBut1 Prompt ".."     SIZE  10,   9 PIXEL  OF oFrm ACTION NIL
      @   10,  73  BUTTON oBut1 Prompt ".."     SIZE  10,   9 PIXEL  OF oFrm ACTION NIL
       @  10,  18  GET aGet[1] VAR cAbi       SIZE  15,   10  PIXEL OF oFrm
       @  10,  58  GET aGet[2] VAR cCab       SIZE  15,   10  PIXEL OF oFrm
       @  22,  85  GET aGet[3] VAR cBanca     SIZE  130,  10  PIXEL OF oFrm
       @  36,  85  GET aGet[4] VAR cFiliale   SIZE  130,  10  PIXEL OF oFrm
       @  52,  28  GET aGet[5] VAR cCap       SIZE   30,  10  PIXEL OF oFrm
       @  52,  85  GET aGet[6] VAR cLocalita  SIZE  130,  10  PIXEL OF oFrm
       @  52,  235 GET aGet[7] VAR cProvincia SIZE   14,  10  PIXEL OF oFrm
         ACTIVATE DIALOG oFrm CENTERED
       dbCloseAll()
       RETURN NIL
     static FUNCTION  BrowseSeach(cAbi,cCab,cBanca,cFiliale,cCap,cLocalita,cProvincia,oBrw)
           FIELD ABI,CAB,ISTBAN,FILBAN,CAP,LOCALITA,PROV
           local cCond:="", bCond

               cCond := "ABI = alltrim('" + cAbi  + "' ) .AND. "
               cCond += "CAB = alltrim('" + cCab  + "' ) .AND. "
               cCond += "ISTBAN = alltrim('" + cBanca  + "' ) .AND. "
               cCond += "FILBAN = alltrim('" + cFiliale  + "' ) .AND. "
               cCond += "CAP = alltrim('" + cCap  + "' ) .AND. "
               cCond += "LOCALITA = alltrim('" + clocalita  + "' ) .AND. "
               cCond += "PROV = alltrim('" + cProvincia + "' ) "

                 bCond    := &( "{ || " + cCond + " }" )

            ( oBrw:cAlias )->( DBSETFILTER( bCond, cCond ), DBGOTOP() )
            oBrw:refresh()
           RETURN NIL
 

the problem is
If I create the get with ON CHANGE command
as

@ 52, 85 GET aGet[6] VAR cLocalita SIZE 130, 10 PIXEL OF oFrm ;
ON CHANGE BrowseSeach(cAbi,cCab,cBanca,cFiliale,cCap,cLocalita,cProvincia,oBrw)

when the use insert key by key on a get for a sample aGet[6] and insert the word "Roma" the insert is too slow and then the search is too slow

the dbf is allready index on

Code: Select all  Expand view

   INDEX ON UPPER(ABI) TAG ABI EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000    FOR ! deleted()
   INDEX ON UPPER(CAB) TAG CAB EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
   INDEX ON UPPER(ISTBAN) TAG ISTBAN EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
   INDEX ON UPPER(FILBAN) TAG FILBAN EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
   INDEX ON UPPER(CAP) TAG CAP EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
   INDEX ON UPPER(LOCALITA) TAG LOCALITA EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
   INDEX ON UPPER(PROV) TAG PROV EVAL (oProgress:SetPos(nProgress++), Sysrefresh()) EVERY 1000     FOR ! deleted()
 

[Silvio.Falconi]

I wish explain you:

The user must insert a city sample "ROMA" into aGet[6] then if the user go to another get the xbrowse must be refreshed and in this case the xbrowse refresh on city inserted

then the use change get and go to another get sample aGet[7] and inser the province sample "RM" and the xbrowse must be refresh for that field PROV = "RM"

I think only in this mode it can be fast

[Silvio.Falconi]

Perhaps now I found the solution , and it seem run good and fast

simply :

@ 52, 85 GET aGet[6] VAR cLocalita SIZE 130, 10 PIXEL OF oFrmImportaBanche Valid SearchBanche(cLocalita,oBrwImportaBanche,7)
@ 52, 235 GET aGet[7] VAR cProvincia SIZE 14, 10 PIXEL OF oFrmImportaBanche Valid SearchBanche(cProvincia,oBrwImportaBanche,8)

Function SearchBanche(ctext,oBrw,n)
FIELD ABI,CAB,ISTBAN,FILBAN,CAP,LOCALITA,PROV
local cCond:="", bCond
DO CASE
CASE n= 6
INDEX ON CAP TO TMP FOR ! deleted()
cCond := "CAP = Upper(alltrim('" + ctext + "' )) "
CASE n=7
INDEX ON LOCALITA TO TMP FOR ! deleted()
cCond := "LOCALITA = Upper(alltrim('" + ctext + "' )) "
CASE n =8
INDEX ON PROV TO TMP FOR ! deleted()
cCond := "PROV = Upper(alltrim('" + ctext + "' )) "
ENDCASE
bCond := &( "{ || " + cCond + " }" )
( oBrw:cAlias )->( DBSETFILTER( bCond, cCond ), DBGOTOP() )
oBrw:refresh()
Return .t.