Grid & SET FILTER

[Georg_BA]

Hello everyone
I would need to consult the GRID function over databases when I use SET FILTER

Grid_3.jpg (84.66 KiB) Viewed 2880 times

in the first picture is the whole database

Grid_3.jpg (84.66 KiB) Viewed 2880 times

the second image is the result after I used the filter "TYPE" # "X"

line 3 "Doklad" ..... 505
as well as "Doklad" ..... 452
is a duplicate and one blank line is displayed

the number of GRID report lines displayed is the same

the third picture shows the state after using SET FILTER "TYPE" = "P"

is "Doklad" .... 903 is displayed 4x and the other lines are empty

Grid_3.jpg (84.66 KiB) Viewed 2880 times

source code

select 13
DBSETFILTER({ || left(druh,1) $ patri_win2 })
dbgotop()
mrn := recno()
win_2.Grid_2.recno := mrn
Win_2.Grid_2.ClearBuffer
//win_2.Grid_2.value:=recno()
Win_2.grid_2.refresh(.t.)
Win_2.grid_2.SetFocus


Thanks for help

[Georg_BA]

friends no one really uses "grid" and "set filter"

[serge_girard]

Did you try with old syntax:

SET FILTER TO <field> == cValue
GO TOP

Serge

[edk]

Did you try with old syntax:

SET FILTER TO <field> == cValue
GO TOP

Serge

Serge, this is a good tip.

@Georg_BA https://www.hmgforum.com/viewtopic.php?p=67611#p67611

IMHO Grid uses DbFilter () internally for rendering. Therefore, if you are using dbSetFilter () you should either define both params: <bCondition> and <cCondition>, or use the SET FILTER TO [<lCondition>] command.

Below is a sample that shows the behavior of the Grid depending on the method you define the filtering conditions:

Code: Select all

#include "hmg.ch"

Function Main

Local aValue := { 0 , 0 }, i

	DBCREATE('test.dbf', { {'ID','N',10,0} , {'NAME','C',30,0}, {'FLAG','C',3,0} } )
	USE TEST
	FOR i := 1 TO 20
		APPEND BLANK
		REPLACE id WITH i
		REPLACE name WITH "Name #" + AllTrim(Str(i))
		REPLACE flag WITH IF (i % 3 = 0, "C", IF ( i % 2 = 0, "B", "A" ) )
	NEXT i
	
	GO TOP

	* Grid Column Controls Definitions

	aCtrl_1 := {'TEXTBOX','NUMERIC','9999999999'}
	aCtrl_2 := {'TEXTBOX','CHARACTER'}
	aCtrl_3 := {'TEXTBOX','CHARACTER'}
		
	DEFINE WINDOW Form_1 ;
		AT 0,0 ;
		WIDTH 800 ;
		HEIGHT 510 ;
		TITLE 'SET FILTER Test' ;
		MAIN 

		DEFINE MAIN MENU 
			POPUP 'File'
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "A" }, "Left(flag , 1) $ "A" )' ACTION SetFilter("A")
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "B" }, "Left(flag , 1) $ "B" )' ACTION SetFilter("B")
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "C" }, "Left(flag , 1) $ "C" )' ACTION SetFilter("C")
				ITEM 'dbSetFilter ()' ACTION SetFilter()
				SEPARATOR
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "A" }' ACTION SetFilter2("A")
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "B" }' ACTION SetFilter2("B")
				ITEM 'dbSetFilter ( { || Left(flag , 1) $ "C" }' ACTION SetFilter2("C")
				ITEM 'dbSetFilter ()' ACTION SetFilter2()
				SEPARATOR
				ITEM 'Set Filter TO FLAG = "A"' ACTION SetFilter3("A")
				ITEM 'Set Filter TO FLAG = "B"' ACTION SetFilter3("B")
				ITEM 'Set Filter TO FLAG = "C"' ACTION SetFilter3("C")
				ITEM 'Set Filter TO ' ACTION SetFilter3()
			END POPUP
		END MENU

		@ 10,10 GRID Grid_1 ;
			WIDTH 770 ;
			HEIGHT 440 ;
			HEADERS {'Id','Name','Flag'} ;
			WIDTHS {50,140,50};
			VALUE { 1 , 1 } ;
			ROWSOURCE "Test" ;
			COLUMNFIELDS { 'Id' ,  'Name' , 'Flag' } ;
			COLUMNCONTROLS { aCtrl_1 , aCtrl_2 , aCtrl_3 } ;
			EDIT ;
			ALLOWAPPEND ; 
			ALLOWDELETE 
		
	END WINDOW

	CENTER WINDOW Form_1

	ACTIVATE WINDOW Form_1

Return

***************************************
Function SetFilter( cFlag )
 
IF !Empty( cFlag )
	dbSetFilter ( { || Left(flag , 1) $ cFlag }, "Left(flag , 1) $ '" + cFlag + "'")
ELSE
	dbSetFilter ()
ENDIF
dbGoTop()
Form_1.Grid_1.RecNo := RecNo()
Form_1.Grid_1.Refresh

RETURN

***************************************
Function SetFilter2( cFlag )
 
IF !Empty( cFlag )
	dbSetFilter ( { || Left(flag , 1) $ cFlag } )
ELSE
	dbSetFilter ()
ENDIF
dbGoTop()
Form_1.Grid_1.RecNo := RecNo()
Form_1.Grid_1.Refresh

RETURN
***************************************
Function SetFilter3( cFlag )
 
IF !Empty( cFlag )
	Set Filter TO Left(flag , 1) $ cFlag
ELSE
	Set Filter TO
ENDIF
GO TOP

Form_1.Grid_1.RecNo := RecNo()
Form_1.Grid_1.Refresh

RETURN

PS.
My good advice. Test filtering in the Grid on a large number of records and in a network environment if the application will run on them. You may find that you will be disappointed with the speed of this solution.

[serge_girard]

For this purposes I DO miss the dBASE dot command !

[serge_girard]

Georg_BA

Also be sure what you want/ask:

Code: Select all

SET FILTER TO ALLTRIM(some_field) = '0' 
SET FILTER TO ALLTRIM(some_field) == '0'
SET FILTER TO some_field = '0'          
SET FILTER TO some_field == '0'         

This can give confusing results when field_len > 1 !
I always use TRIMMED values..

Serge

[Georg_BA]

Txh serge_girard and edk

it never occurred to me that this could be in the command syntax used

i used "set filter to" and everything works as it should

thanks again

[dragancesu]

The problem is solved, but here are some of my ideas

When there is little data according to me it is better to use BROVSE, if I use GRID then I rewrite the data from dbf to grid and if necessary I use some condition or filter

The grid "knows" how much data it had to display and if it needs less then it needs to be deleted before the new display otherwise "ghosts" remain

MD_Form_1.MD_Grid_1.DeleteAllItems ()

Code: Select all

   aGrid := {}
   SELECT table2
   // set filter to ...        <- one way   
   dbgotop()
   DO WHILE .NOT. EOF()
      IF _key1 != some_filter  // second way
         DBSKIP()
         LOOP
      ENDIF
      Aadd(aGrid, {str(recno()),str(field_1),dtoc(field_2), ...,str(field_n)})
      DBSKIP()
   ENDDO

   MD_Form_1.MD_Grid_1.DeleteAllItems()
   FOR i = 1 to LEN(aGrid)
      MD_Form_1.MD_Grid_1.AddItem(aGrid[i])
   NEXT
   SELECT table1 

just one way to solve your problem

[franco]

My thoughts.
I use browse for viewing or looking for table contents and grid for working with data.
I try not to use set filter on files larger then ? 50 items. Can be to slow in network as Edk suggested.
I use local temporary indexes instead.
At start of MAIN program I create a local temporary folder so table lookups (which is in server.) are local lookups and much faster.

Code: Select all

CREATEFOLDER("C:\Hmtemp")      \\ if it exists it continues.

Then in your Grid program

Code: Select all

local hm:= " ", prname := 'your Grid program name'
select table ?
if reccount() > 50
   index on fieldname to c:\hmtemp\prname.ntx for $ patri_win2
else 
   set filter
endif
go top
----- your code ----
----- end of program ----
hm := "c:\hmtemp\prname.ntx"
erase(hm)
return

[Georg_BA]

thank you for the contribution, I basically also avoid GRID
I only see a lot of problems here, I don't know why we deviated from BROWSE
I see the biggest problem is that TBROSE in HMG has stopped being supported

Georg