Xbrowse Filter with a big dbf

[Silvio.Falconi]

I wish create a filter on a xbrowse fill with a Dbf ( I have a dbf with 61.819 records)


I wish search these fields ABI CAB ISTBAN CAP LOCALITA PROV



I saw the Nages samples use array data but not dbf directly

I insert

oBrowse:uBarGetVals := Space( 10 )
oBrowse:bClrEdits := { || { CLR_BLACK, CLR_YELLOW } }

How I can create the filters ?

[Silvio.Falconi]

I tried to use an array

USE BANCHE
aData := FW_DbfToArray( "ABI,CAB,ISTBAN,LOCALITA" )
CLOSE BANCHE


the exe of the test is arrived after some seconds (40)





then I tried to search a bank write "Roma" on the seek bar

the search is too too too slow and I see the result after many seconds




Perhaps I think

1) the function FW_DbfToArray if there a Big dbf should create a progress or meter control on the screen to tell the user that he is uploading the file, perhaps by placing it on the dialog bar
2) the same thing should be done in the search in order not to bother the client who says "but he got stuck?, he went haywire" because for the search I had to wait some time, maybe about 30/40 seconds

how could it be improved?
I also did a test using the dbf directly but the test.exe never opened at least in the first 5 minutes, I am using a big dbf of 61.819 records

[Marc Venken]

Silvio,

I also browse a very large dbf directly, and the speed is just 2/3 secs ?

Here is a function/code that I use. Maybe you can see more inside the code.

It takes max. 8 secs on a dbf with 291.000 recs

Code: Select all

function BrowseSample(lNewfile)

   local oDlg2, oFont, oBold, oMono, oGet, oBrw,oImgmaster, aVeld
   local nWild := 2, aDir:={}
   local Top_Left:=screenwidth()-10, Bottom_Right:=screenheight()-20

   field code,lev_naam,naam,lev_ref,fab_ref,fab_naam,selection,pagina,cat_main,cat_sub1,filename,dezegroep  // master
   if lNewfile
      close master
      cCurFile := cGetFile( "Database file| *.dbf| ", "Please select a file" )
      ferase("master.cdx")
      use (cCurFile) alias master NEW
      select master
      INDEX ON upper(code) TAG code
      INDEX ON upper(lev_naam) TAG levnaam
      INDEX ON upper(naam) TAG naam
      INDEX ON upper(lev_ref) TAG levref
      INDEX ON upper(fab_ref) TAG fabref
      INDEX ON upper(fab_naam) TAG fabnaam
      INDEX ON selection TAG selected
      INDEX ON str(val(pagina)+1000) TAG pagina
      INDEX ON cat_main+Cat_sub1 TAG cat
      INDEX ON Cat_sub1+fab_naam TAG catsub
      INDEX ON upper(filename) TAG file
      INDEX ON dezegroep TAG dezegroep

   endif
   select master
   master->(dbgotop())

   replace dezegroep with .f. all
   master->(ordsetfocus("code"))

   master->(dbgotop())
   nofoto->(dbgotop())
   do while !nofoto->(eof())
     if master->(dbseek(nofoto->id))
        master->dezegroep = .t.
     endif
     nofoto->(dbskip())
   enddo

   DEFINE FONT oFont NAME "Segoe UI" SIZE 0,-12
   DEFINE FONT oBold NAME "TAHOMA" SIZE 0,-12 BOLD
   DEFINE FONT oMono NAME "Lucida Console" SIZE 0,-12

   DEFINE DIALOG oDlg2 SIZE Top_left,Bottom_Right PIXEL TRUEPIXEL FONT oFont ;
      TITLE "Master database : Groep -> "+fotoinde->Titel+" Subgroep -> "+fotoinde->Index



   aVeld :=  { ;
   { "selection", "Sel"      ,nil,  30 }, ;
   { "dezegroep", "Deze"    ,nil,  30 }, ;
   { "code"     , "Code"     ,nil,  60 }, ;
   { "naam"     , "Naam"     ,nil, 300 }, ;
   { "lev_naam" , "Lever"    ,nil,  60 }, ;
   { "lev_ref"  , "LevCode"  ,nil,  60 }, ;
   { "fab_naam" , "Fabrikant",nil,  60 }, ;
   { "fab_ref"  , "FabCode"  ,nil,  60 }, ;
   { "Pagina"   , "Pag"      ,nil,  40 }, ;
   { "stock"    , "Sto"      ,nil,  60 }, ;
   { "cat_main" , "Cat_Main" ,nil,  60 }, ;
   { "cat_sub1" , "Cat_sub"  ,nil,  60 }, ;
   { "bruto"    , "Bruto"    ,nil,  40 }, ;
   { "brutokor" , "Kor"      ,nil,  40 }, ;
   { "geldigvan", "Van"      ,nil,  20 }, ;
   { "geldigtot", "Tot"      ,nil,  20 }, ;
   { "kleuren"  , "kleuren"  ,nil,  80 }, ;
   { "maten"    , "Maten"    ,nil,  80 }, ;
   { "picture"  , "Picture"  ,nil,  80 }, ;
   { "memotxt"  , "Memotxt"  ,nil,  50 }, ;
   { "online"   , "Groepen"  ,nil, 100 }, ;
   { "filename" , "Database" ,nil,  60 } }


   @ 80,10 XBROWSE oBrw4 SIZE 1630,-10 ;
      PIXEL OF oDlg2 font oFont;
      DATASOURCE "MASTER" ;
      COLUMNS aVeld;
      autosort CELL LINES FOOTERS NOBORDER fastedit

   oBrw4:SetChecks()

   //oBrw4:nStretchCol         := STRETCHCOL_WIDEST

   oBrw4:sel:nFooterType := AGGR_COUNT
   oBrw4:sel:bSumCondition := { || master->selection }

   oBrw4:deze:nFooterType := AGGR_COUNT
   oBrw4:deze:bSumCondition := { || master->dezegroep }

   oBrw4:bRClickHeaders := { || XbrColSelector( oBrw4 ) }  // Kies welke velden tezien zijn



   for n := 1 to Len( oBrw4:aCols )
      WITH OBJECT oBrw4:aCols[ n ]
         if :cDataType != 'L'
            :uBarGetVal    := uValBlank( :Value )
            :cBarGetPic    := :cEditPicture
         endif
      END
   next

   // index
   oBrw4:aCols[2]:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:aCols[3]:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:aCols[4]:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:aCols[5]:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:aCols[7]:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:Fabrikant:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:Cat_main:bClrHeader := { || { CLR_RED, 155 } }
   oBrw4:Cat_Sub:bClrHeader := { || { CLR_RED, 155 } }

   WITH OBJECT oBrw4


      :bChange       := { || oImgmaster:Refresh() }

      :lFooter          := .t.
      :bRecSelHeader    := { || "Row" }
      :bRecSelData      := { |o| o:KeyNo }
      :bRecSelFooter    := { |o| o:nLen }
      :oRecSelFont      := oFont  // optional
      :nRecSelWidth     := "99999" // required size

      :lIncrFilter   := .t.
      :lSeekWild     := ( nWild == 2 )
      :cFilterFld    := "naam"


      :lColChangeNotify := .t.
      :lSortDescend     := .f.
      //:bFooters         := { |oCol| If( Empty( oCol:cOrder ), "", oBrw4:cSeek("") ) }

      :oSeek            := TSeek():New( oBrw4 )

      :nHeaderHeight    := 40 // optional
      :oHeaderFonts     := oBold
      :bClrEdits        := { || { CLR_HRED, CLR_YELLOW } }
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :bRClicked        := {|nRow, nCol| MenuPop( nRow,nCol, oBrw4 ),oBrw4:refresh() }

      :lMultiSelect     := .T.

      :bKeyDown   := { |nKey| If( nKey == VK_DELETE, (oBrw4:cAlias)->( BrwDelselect( oBrw4 ) ), nil ) }

      WITH OBJECT : sel
         :nEditType     := EDIT_GET
         :SetCheck()
         :nFooterType  := AGGR_COUNT
         :bSumCondition := { || master->selection }
      END

      WITH OBJECT : deze
         :nEditType     := EDIT_GET
         :SetCheck()
         :nFooterType  := AGGR_COUNT
         :bSumCondition := { || master->dezegroep }
      END


      WITH OBJECT : Naam
         :nEditType     := EDIT_GET
         :bClrEdit      := { || { CLR_BLACK, CLR_YELLOW }}
         :bOnChange     := { || oBrw4:RefreshCurrent()}
      END

      WITH OBJECT :fabrikant
        :uBarGetVal    := uValBlank( :Value )
        :nEditType     := EDIT_LISTBOX
        :aEditListTxt  := aLeveranciers
        :aBarGetList:= aLeveranciers

        //:cSortOrder = "groep"
      ENDWITH


      :CreateFromCode()
   END

   oBrw4:lGetBar   := .T.
   oBrw4:bToolTips   := ;
         { | oBrw4,r,c,f,oMouseCol,nMouseRow| MyColToolTip( oBrw4,r,c,f,oMouseCol,nMouseRow ) }

   //oBrw4:naam:bClrStd := { || IF( !empty(master->online) , { CLR_BLACK,CLR_HGREEN } , { CLR_BLACK,CLR_WHITE } ) }
   oBrw4:naam:bClrStd := { || IF( !empty(master->dezegroep) , { CLR_WHITE,CLR_HBLUE } , ;
                              if ( !empty(master->online), { CLR_BLACK,CLR_HGREEN } , ;
                              if ( master->stock > 0 , { CLR_BLACK,CLR_YELLOW } ,{ CLR_BLACK,CLR_WHITE } ))) }
   oBrw4:MakeTotals()



   @  20, 20 BTNBMP oBtn OF oDlg2 SIZE 100, 35 NOBORDER PROMPT { || If( oBrw4:lGetBar, "Hide GetBar", "ShowGetBar" ) } 2007 ACTION ( oBrw4:lGetBar := ! oBrw4:lGetBar, oBrw4:Refresh() ) FONT oBold CENTER
   @  20,150 BTNBMP oBtn OF oDlg2 SIZE 100, 35 NOBORDER PROMPT "Set Filter" 2007 ACTION ( oBrw4:cAlias )->( SetFilter( oBrw4 ) ) FONT oBold CENTER
   @  20,280 BTNBMP oBtn OF oDlg2 SIZE 100, 35 NOBORDER PROMPT "Clear Filter" 2007 ACTION ( oBrw4:cAlias )->( DBCLEARFILTER(),("master")->( ORDSCOPE(0, "" ) ),("master")->( ORDSCOPE(1, "" ) ),oBrw4:gotop(),oBrw4:Refresh(), oBrw4:SetFocus() ) FONT oBold CENTER
   @  20,410 BTNBMP oBtn OF oDlg2 SIZE 100, 35 NOBORDER PROMPT "Wis Selectie" 2007 ACTION ( Clearselection(),oBrw4:MakeTotals(),oBrw4:gotop(),oBrw4:refresh() ) FONT oBold CENTER

   @ 05,1000 XIMAGE oImgmaster SIZE 70, 70 OF oDlg2 SOURCE "d:\pictures\origineel\"+alltrim(master->picture)
   oImgmaster:bLClicked:={|| imagetestnew("d:\pictures\origineel\"+alltrim(master->picture))}

   /*
   @ 20, 20 SAY TRIM( SAMPLES->TOPIC ) SIZE 860,30 PIXEL OF oDlg CENTER ;
         FONT oBold UPDATE

   @  20,950 SAY "CODE" SIZE 850,30 PIXEL OF oDlg CENTER ;
      COLOR CLR_BLACK, nRGB( 231, 242, 255 )

   @  60,950 GET oGet VAR SAMPLES->CODE SIZE 850,750 PIXEL OF oDlg ;
      MEMO READONLY FONT oMono UPDATE

   oDlg:bPainted := { || oDlg:Box( 59,339,81,881 ) }
   */

   ACTIVATE DIALOG oDlg2 CENTERED

return nil

static function SetFilter( oBrw )

   local cFilter := ""
   local n, oCol, uVal, cType

   for n := 1 to Len( oBrw:aCols )
      oCol  := oBrw:aCols[ n ]
      if ! Empty( uVal := oCol:uBarGetVal )
         if !Empty( cFilter )
            cFilter  += " .AND. "
         endif
         cType    := ValType( uVal )

         do case
         case cType == 'C'
            uVal     := Upper( AllTrim( uVal ) )
//            cFilter += '"' + uVal + '" $ UPPER( ' + FieldName( n ) + " )"
            cFilter += '"' + uVal + '" $ UPPER( ' + oCol:CExpr + " )"
         otherwise
//            cFilter  += FieldName( n ) + " == " + cValToChar( uVal )
            cFilter  += oCol:cExpr + " == " + cValToChar( uVal )
         endcase
      endif

   next
   *******************************
   */
   //msginfo(cFilter)

   if Empty( cFilter )
      if ! Empty( DBFILTER() )
         DBCLEARFILTER()
         oBrw:Refresh()
      endif
   else
      if !( DBFILTER() == cFilter )
         SET FILTER TO &cFilter
         GO TOP
         oBrw:Refresh()
      endif
   endif

   oBrw:SetFocus()

return nil

[Marc Venken]

Just to inform...

There is a seekbar (lookup system in Xbrowse)

There is a Getbar (can lookup in Xbrowse) This is what I use (sample above)