TDatabase: Parameterized filters and ReFilter()

TDatabase: Parameterized filters and ReFilter()

Postby nageswaragunupudi » Fri May 21, 2021 4:08 am

SetFilter( cFilter, aParams )
Code: Select all  Expand view

oDbf:SetFilter( "AGE >= ? .AND. AGE <= ?", { 30, 40 } )
// This sets filter AGE >= 30 .AND. AGE <= 40

// Later
oDbf:ReFilter( { 40, 50 } )
// changes the filter as AGE >= 40 .AND. AGE <= 50
 


SetFilter( cFilter, bParams )
Code: Select all  Expand view

n1 := 30
n2 := 40
oDbf:SetFilter( "AGE >= ? .AND. AGE <= ?", { || { n1, n2 } } )
// This sets filter AGE >= 30 .AND. AGE <= 40

// Later
n1 := 40
n2 := 50
oDbf:ReFilter()
// changes the filter as AGE >= 40 .AND. AGE <= 50
 


The following sample uses the second method of using bParams (codeblock) to implement xbrowse with incremental filter on two fields.
Code: Select all  Expand view
#include "fivewin.ch"

REQUEST DBFCDX

//----------------------------------------------------------------------------//

function Main()

   local oDlg, oFont, oBrw, oGet1, oGet2, oDbf
   local cName := Space( 20 )
   local cCity := Space( 20 )

   SetGetColorFocus()

   oDbf  := TDataBase():Open( nil, "CUSTOMER", "DBFCDX", .t. )

   // Parameterized filter with codeblock
   oDbf:SetFilter( "UPPER(FIRST) = ? .AND. UPPER(CITY) = ?", ;
        { || { UPPER(ALLTRIM(cName)), UPPER(ALLTRIM(cCity)) } } )

   oDbf:GoTop()

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 700,400 PIXEL TRUEPIXEL FONT oFont

   @  22, 20 SAY "FILTER: Name: " SIZE 100,24 PIXEL OF oDlg

   @  20,150 GET oGet1 VAR cName SIZE 150,26 PIXEL OF oDlg ;
      ON CHANGE ( oGet1:Assign(), oDbf:ReFilter(), oDbf:GoTop(), oBrw:Refresh() )

   @  22,310 SAY "City: " SIZE 50,24 PIXEL OF oDlg RIGHT

   @  20,380 GET oGet2 VAR cCity SIZE 150,26 PIXEL OF oDlg ;
      ON CHANGE ( oGet2:Assign(), oDbf:ReFilter(), oDbf:GoTop(),oBrw:Refresh() )

   @  55, 20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oDbf ;
      COLUMNS "FIRST", "LAST", "CITY" ;
      CELL LINES NOBORDER FOOTERS

   WITH OBJECT oBrw
      :RecSelShowRecNo()
      :bClrSel := :bClrSelFocus
      :CreateFromCode()
   END

  ACTIVATE DIALOG oDlg CENTERED
  RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//
 


Image
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10619
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: TDatabase: Parameterized filters and ReFilter()

Postby Silvio.Falconi » Sat May 22, 2021 10:24 am

nice job
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Parameterized filters and ReFilter()

Postby Silvio.Falconi » Tue May 25, 2021 1:26 pm

Nages,
I need to make a filter with different fields ie for example:


Code: Select all  Expand view
         cFilter := "trim(Tipo) == '" + cTipo +;
                  "' .and. alltrim(str(giorni))== '" + alltrim(str(nGiorni)) +;
                  "' .and. idelemento== '"+cidelemento+;
                  "' .and. settore== '" +cSettore+;
                  "' .and. alltrim(idlistino)== '" +clistino+;
                  "' .and. alltrim(str(mese))== '" + alltrim(str(nmese)) +"'"

   oTariffePrezzi:= TDatabase():Open( nil,oApp:cDbfPath+"TARIFFE", "DBFCDX", .t. )
    oTariffePrezzi:setOrder(1)
    oTariffePrezzi:setFilter(cFilter)
    oTariffePrezzi:gotop()
 


is there another method more easy ?
because sometimes I risk making mistakes by omitting "'"
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Parameterized filters and ReFilter()

Postby nageswaragunupudi » Tue May 25, 2021 3:17 pm

cFilter := "TRIM(TIPO) == ? .AND. GIORNI == ? .AND. IDELEMENTO == ? .AND. " + ;
"SETTORE == ? .AND. ALLTRIM(IDLISTINO) == ? .AND. MESE == ?"

oDbf:SetFilter( cFilter, { cTipo, nGiorni, cidelemento, cSettore, clistino, nmese } )
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10619
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: TDatabase: Parameterized filters and ReFilter()

Postby Silvio.Falconi » Tue May 25, 2021 4:10 pm

Gulp, nice !!!
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Parameterized filters and ReFilter()

Postby nageswaragunupudi » Tue May 25, 2021 4:52 pm

When you set filter with
oDbf:SetFilter( cFilter, aParams )
The method applier the aParams to cFilter and prepares the filter string to set the filter.
You can check the exact filter string with
? oDbf:DBFILTER()
and see if this is what you expected.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10619
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: TDatabase: Parameterized filters and ReFilter()

Postby Silvio.Falconi » Tue May 25, 2021 5:47 pm

all this for the next package ?
when is go out ?
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Parameterized filters and ReFilter()

Postby nageswaragunupudi » Tue May 25, 2021 6:20 pm

It is already there in many past versions.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10619
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: TDatabase: Parameterized filters and ReFilter()

Postby Silvio.Falconi » Wed May 26, 2021 8:25 am

nageswaragunupudi wrote:It is already there in many past versions.


Nages,
I can converte also this block ?

Code: Select all  Expand view

oPrenotazioni  := TDatabase():Open( nil, oApp():cDbfPath+"RESERVA",   "DBFCDX", .t. )
              oPrenotazioni:Exec( < ||
              SET FILTER TO AllTrim( FIELD->TYPE ) = AllTrim( cTipoElemento ) .and. VAL(FIELD->ROOMS_ID) = nElemento ;
                 .AND. ( dDataIniziale <= FIELD->CHECK_OUT .AND. dDataFinale >= FIELD->CHECK_IN )
             return nil
             > )
              oPrenotazioni:gotop()

 
Since from 1991/1992 ( fw for clipper Rel. 14.4 - Momos)
I use : FiveWin for Harbour November 2023 - January 2024 - Harbour 3.2.0dev (harbour_bcc770_32_20240309) - Bcc7.70 - xMate ver. 1.15.3 - PellesC - mail: silvio[dot]falconi[at]gmail[dot]com
User avatar
Silvio.Falconi
 
Posts: 7033
Joined: Thu Oct 18, 2012 7:17 pm

Re: TDatabase: Parameterized filters and ReFilter()

Postby nageswaragunupudi » Wed May 26, 2021 10:27 am

yes
try
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10619
Joined: Sun Nov 19, 2006 5:22 am
Location: India


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 57 guests