TBrowse with dynamic filtering

[gfilatov]

Hi Friends,

I saw an interesting sample by Mr. Rao on the FiveWin forum.

This example inspired me to adapt it to MiniGUI.

I have attached a working application for your testing.

The original form with the TBROWSE control, three checkboxes and a filter status is shown below.

image1.png (15.03 KiB) Viewed 2356 times

Then we set a filter on MARRIED field

image2.png (15.5 KiB) Viewed 2356 times

Add the filter condition on STATE field

image3.png (14.27 KiB) Viewed 2356 times

and finally add the filter on AGE field

image4.png (12.6 KiB) Viewed 2356 times

HMG Power is ready

[serge_girard]

Thanks !

[ASESORMIX]

Gracias por comprtir.
Puedes postear el codigo fuente.? O donde puedo conseguirlo.?

[tryak96]

Good idea

[gfilatov]

Gracias por comprtir.
Puedes postear el codigo fuente.? O donde puedo conseguirlo.?

Hi,

Thanks for your interest

The complete source code for this example is below.

Code: Select all

#include "hmg.ch"
#include "TSBrowse.ch"

REQUEST DBFCDX

*----------------------------------------
FUNCTION Main()
*----------------------------------------
   LOCAL obrw
   LOCAL aFilters := { { .F., "AGE>40" }, { .F., "STATE='NY'" }, { .F., "MARRIED" } }
   LOCAL aCheck[ 3 ], n
   LOCAL nRow := 40
   LOCAL nCol := 40

   USE CUSTOMER NEW SHARED VIA "DBFCDX"

   DEFINE WINDOW win_1 ;
         AT 0, 0 ;
         WIDTH 600 HEIGHT 400 ;
         MAIN ;
         TITLE Alias() + ": Dynamic Filter Test" ;
         ICON "lupa.ico" ;
         FONT "Arial" ;
         SIZE 10 ;
         NOMAXIMIZE NOSIZE

      FOR n := 1 TO Len( aFilters )

         aCheck[ n ] := CreateChk( nRow, nCol, thiswindow.Name, aFilters, n )
         this.( aCheck[ n ] ).Cargo := n
         this.( aCheck[ n ] ).OnChange := {|| n := this.Cargo, aFilters[ n, 1 ] := ! aFilters[ n, 1 ], ;
            SetProperty( 'win_1', "Label_1", "Value", "FILTER : " + ResetFilter( obrw, aFilters ) ) }

         nCol += 150

      NEXT

      @ 80, 40 LABEL Label_1 VALUE "FILTER : " WIDTH 400 HEIGHT 25 CENTERALIGN

      DEFINE TBROWSE obrw AT 115, 40 ;
         CELLED SELECTOR "pointer.bmp" ;
         COLORS CLR_BLACK, CLR_WHITE, CLR_BLACK, { RGB( 231, 242, 255 ), GetSysColor( COLOR_GRADIENTINACTIVECAPTION ) } ;
         ALIAS Alias() ;
         WIDTH win_1.Width - 70 - GetBorderWidth() HEIGHT 220 ;
         ON INIT {| ob | TsbCreate( ob, .T. ) }

      END TBROWSE ON END {| ob | TsbCreate( ob, .F. ) }

   END WINDOW

   ON KEY ESCAPE OF win_1 ACTION win_1.Release()

   CENTER WINDOW win_1
   ACTIVATE WINDOW win_1

RETURN NIL

*----------------------------------------
STATIC PROCEDURE TsbCreate( obrw, lInit )
*----------------------------------------
   LOCAL aFields, oCol

   IF lInit

      // initial columns
      aFields := { "ID", "CITY", "STATE", "MARRIED", "AGE" }

      LoadFields( "oBrw", "win_1", .F., aFields )

      FOR EACH oCol IN oBrw:aColumns
         oCol:cHeading := oCol:cName
      NEXT

      WITH OBJECT oBrw
         :nHeightCell += 4
         :nHeightHead := oBrw:nHeightCell - 2

         :SetColor( { 5 }, { CLR_WHITE } )
         :SetColor( { 6 }, { RGB( 0, 0, 128 ) } )

         :SetAppendMode( .F. )
         :SetDeleteMode( .T., .F. )

         :lNoResetPos := .T.
         :lNoMoveCols := .T.
         :lNoKeyChar := .T.
         :lNoChangeOrd := .T.
         :lNoHScroll := .T.
      END OBJECT

   ELSE

      obrw:SetNoHoles()
      obrw:SetFocus()

   ENDIF

RETURN

*----------------------------------------
STATIC FUNCTION CreateChk( nRow, nCol, oDlg, aFilters, nIndex )
*----------------------------------------
   LOCAL cChk := "Check_" + hb_ntos( nIndex )

   @ nROW, nCol CheckBox ( cChk ) PARENT ( oDlg ) ;
      CAPTION aFilters[ nIndex, 2 ] VALUE aFilters[ nIndex, 1 ] ;
      WIDTH 150 HEIGHT 21

RETURN cChk

*----------------------------------------
STATIC FUNCTION ResetFilter( obrw, aFilters )
*----------------------------------------
   LOCAL cFilter
   LOCAL af := {}

   AEval( aFilters, {| a | If( a[ 1 ], AAdd( af, a[ 2 ] ), nil ) } )

   cFilter := LB_Array2String( af, " .AND. " )

   IF Empty( cFilter )
      oBrw:FilterData()
   ELSE
      oBrw:FilterData( cFilter, , .T. )
   ENDIF

RETURN cFilter

Hope it is helpful.

[ASESORMIX]

Muchas Gracias Gregory.

[dragancesu]

This is nice and useful, but limited, not used for all fields

Is it possible to somehow select a field and a criterion on it and insert it into the filter

numeric fields can be =, >, <, !=, between
date =, !=, between
character =, !=, like

between in sql is range, from - to
like with wildcards is very powerful

practically we need a function that dynamically create a set filter criterion (like in access on picture)

access.png (15.5 KiB) Viewed 2075 times

[gfilatov]

This is nice and useful, but limited, not used for all fields

Is it possible to somehow select a field and a criterion on it and insert it into the filter

numeric fields can be =, >, <, !=, between
date =, !=, between
character =, !=, like

between in sql is range, from - to
like with wildcards is very powerful

Hi Dragan,

Thanks for your suggestion.

There is an example of a more advanced query dialog for any grid, as shown in the image below.

image01.png (81.54 KiB) Viewed 2069 times

And the result of the above query is:

image02.png (9.59 KiB) Viewed 2069 times

Here is a main form definition for testing:

Code: Select all

/* Test Grid Query */
Procedure Main
   local aGrid, nGrid
   LOCAL aMinMaxInfo := {}, i, nWidth
   local aHeaders := {"Sno","Words","Numeric","Factor","Value","Y","Date"}
   local aWidths := {50,70,70,50,70,50,80}
   local aJustify := {2,0,1,0,0,2,2}
   local cWin := "FrmMain"
   Set( _SET_DEBUG, .f. )
   Set Century ON
   Set Date British

   DEFINE FONT FontBold FONTNAME _HMG_DefaultFontName SIZE _HMG_DefaultFontSize + 1 BOLD

   aGrid := { { '001', 'One',   100, 4, 400,.t.,Date() },;
              { '002', 'Two',   100, 6, 600,.t.,Date()-10 },;
              { '003', 'Three', 100, 7, 700,.t.,Date()-365 },;
              { '004', 'Four',  100, 5, 500,.f.,Date()-100 },;
              { '005', 'Five',  100, 3, 300,.t.,Date()-50 },;
              { '006', 'Six',   100, 5, 500,.t.,Date()-90 },;
              { '007', 'Seven', 100, 7, 700,.f.,Date()-200 },;
              { '008', 'Eight', 100, 9, 900,.t.,Date()-150 },;
              { '009', 'Nine',  100, 5, 500,.t.,Date()-60 },;
              { '010', 'Ten',   100, 4, 400,.f.,Date()-80 },;
              { '011', 'Eleven',100, 6, 600,.t.,Date()-70 },;
              { '012', 'Twelve',100, 7, 700,.f.,Date()-30 } }
   nGrid := 0
   AEval(aWidths, {|e| nGrid += e})

   define window &cWin at 0,0 width 640 height 480 title "F2 - Query" main

      ON KEY ESCAPE OF &cWin Action thisWindow.Release
      ON KEY F2 of &cWin Action GridQuery(cWin,"Grid1")

      @ 10,10 Grid Grid1 of &cWin autosizewidth nGrid height 400 Headers aHeaders Widths aWidths ;
            Items aGrid Value 1 justify aJustify backcolor BROWN fontcolor YELLOW nosortheaders

   for i := 1 to getproperty(cWin,"Grid1","ColumnCount")
      // Dynamic Header
      FrmMain.Grid1.HeaderDYNAMICFONT(i) := {|| 'FontBold' }
      FrmMain.Grid1.HeaderDYNAMICBACKCOLOR(i) := {|| BROWN }
      FrmMain.Grid1.HeaderDYNAMICFORECOLOR(i) := {|| YELLOW }
      nWidth := FrmMain.Grid1.COLUMNWIDTH(i)
      AAdd( aMinMaxInfo, { nWidth, nWidth } )
   next

   FrmMain.Grid1.COLUMNWIDTHLIMITS := aMinMaxInfo

   end window
   DoMethod(cWin,"Activate")
return

Thank you for your attention.

[serge_girard]

Thanks !