Monday, June 18, 2012

Grid Tricks#4- Clearer Search Results


Where is numbers 1, 2 & 3 Tricks?  We can simply treat #1 as the More Objects inside a Cell blog, #2 as the Various Images II blog and #3 as the Transparent Grid blog.

I decided to start showing some more tricks I do on my side using grids which I deem will be useful for others as well.  This is about making the search result clearer or more visible by highlighting the cell contents where a near match can be found.  If not, it is very tiring for the user's eyes to trace where exactly those near matches are.

Actually, this entry is partly because of recent exchanges of ideas about the tricks and method in searching.  What I wanted to show here is when you have several columns to look at and if the value in the search box can come from any of those columns, how can we easily distinguish where the near matches are?  This trick aids visually the user just about that.  Here is a sample code for this:





Local oForm
oForm=Newobject("Form1")
oForm.Show
Read Events
Return

Define Class Form1 As Form
      Height = 390
      Width = 770
      AutoCenter = .T.
      Caption = 'Clearer Search Results'
      ShowTips = .T.
      _cSearch = ''

      Add Object grid1 As Grid With ;
            GridLines = 0, ;
            Height = 328, ;
            Left = 10, ;
            Top = 50, ;
            Width = 750,;
            GridLines = 3,;
            DeleteMark = .F.,;
            GridLineColor = Rgb(192,192,192),;
            FontName = 'Tahoma',;
            FontSize = 8

      Add Object label1 As Label With ;
            top = 15,;
            left = 10,;
            caption = 'Search',;
            Backstyle = 0

      Add Object Text1 As TextBox With ;
            top = 10,;
            left = 55,;
            Height = 23,;
            Width = 300,;
            value = '',;
            ToolTipText = 'Start typing to search matching records,'+;
            ' Double-click to clear search'

      Procedure Load
            Set Talk Off
            Set Safety Off
            Close Databases All
            Select  company,contact,Title From (Home(2)+;
                  "data\customer") Where .F. Into Cursor junk1 Readwrite
      Endproc

      Procedure grid1.Init
            With This
                  .RecordSourceType = 6
                  .RecordSource = 'junk1'
                  .Column1.DynamicForeColor = ;
                        'IIF(thisform._cSearch $ UPPER(company),RGB(255,128,0),RGB(0,0,0))'
                  .Column2.DynamicForeColor = ;
                        'IIF(thisform._cSearch $ UPPER(contact),RGB(255,128,0),RGB(0,0,0))'
                  .Column3.DynamicForeColor = ;
                        'IIF(thisform._cSearch $ UPPER(title),RGB(255,128,0),RGB(0,0,0))'
                  .Column1.DynamicFontBold = ;
                        'IIF(thisform._cSearch $ UPPER(company),.T.,.F.)'
                  .Column2.DynamicFontBold = ;
                        'IIF(thisform._cSearch $ UPPER(contact),.T.,.F.)'
                  .Column3.DynamicFontBold = ;
                        'IIF(thisform._cSearch $ UPPER(title),.T.,.F.)'
            Endwith
      Endproc

      Procedure Text1.DblClick
            This.Value = ''
            Zap In junk1
            Thisform.grid1.Refresh
      Endproc

      Procedure Text1.InteractiveChange
            Local lcSearch
            lcSearch = Upper(Alltrim(This.Value))
            Thisform._cSearch = m.lcSearch

            Select company, contact, Title From Home(2)+"data\customer" ;
                  Where Upper(cust_id+company+contact+Title);
                  LIKE '%'+m.lcSearch+'%' Into Cursor junk2 NOFILTER

            Select junk1
            Zap In junk1
            Append From Dbf('Junk2')
            Go Top
            Thisform.grid1.Refresh
      Endproc

      Procedure Destroy
            Clear Events
      Endproc

Enddefine



The tricks employed here is via using DynamicForeColor and DynamicFontBold properties of a column.  Another thing you have to notice here is my use of a form property because local variables go out of scope.

Well, those are just 3 simple things to look for.  Now go employ this trick on your end.

Cheers!

7 comments:

  1. Hey, i'm your fan, i did make some changes to your (brillant) code, please see and tell me if is ok. see ya. Guille (Py)


    LOCAL OFORM
    OFORM = NEWOBJECT("Form1")
    OFORM.SHOW
    READ EVENTS
    RETURN

    DEFINE CLASS FORM1 AS FORM
    HEIGHT = 390
    WIDTH = 770
    AUTOCENTER = .T.
    CAPTION = "Resultados de búsquedas claros"
    SHOWTIPS = .T.
    _CSEARCH = ""

    ADD OBJECT GRID1 AS GRID WITH ;
    GRIDLINES = 0, HEIGHT = 328, LEFT = 10, TOP = 50, WIDTH = 750, ;
    GRIDLINES = 3, DELETEMARK = .F., GRIDLINECOLOR = RGB(192,192,192) ,;
    FONTNAME = "Tahoma", FONTSIZE = 8, READONLY = .T., ANCHOR = 15

    ADD OBJECT LABEL1 AS LABEL WITH ;
    TOP = 15, LEFT = 10, ;
    CAPTION = "Buscar", BACKSTYLE = 0

    ADD OBJECT TEXT1 AS TEXTBOX WITH ;
    TOP = 10,LEFT = 55, HEIGHT = 23,WIDTH = 300, VALUE = "", ;
    TOOLTIPTEXT = "Comience a escribir para buscar los registros coincidentes. " + ;
    "Doble clic para borrar la búsqueda"

    PROCEDURE LOAD
    SET TALK OFF
    SET SAFETY OFF
    CLOSE DATABASES ALL
    SELECT COMPANY, CONTACT, TITLE ;
    FROM (HOME(2)+"data\customer") ;
    WHERE .F. ;
    INTO CURSOR JUNK1 READWRITE
    USE IN CUSTOMER
    ENDPROC

    PROCEDURE GRID1.INIT
    WITH THIS
    .RECORDSOURCETYPE = 6
    .RECORDSOURCE = "junk1"
    ENDWITH
    ENDPROC

    PROCEDURE TEXT1.DBLCLICK
    THIS.VALUE = ""
    ZAP IN JUNK1
    THISFORM.GRID1.REFRESH
    ENDPROC

    PROCEDURE TEXT1.INTERACTIVECHANGE
    LOCAL LCSEARCH
    LCSEARCH = UPPER(ALLTRIM(THIS.VALUE))
    THISFORM._CSEARCH = m.LCSEARCH
    *

    LOCAL NCUENTAWORD, CFILTROCADENA, ICUENTA,_VAL
    NCUENTAWORD=GETWORDCOUNT(ALLTRIM(m.LCSEARCH))
    CFILTROCADENA=[.t.]
    _VAL = ''
    FOR ICUENTA=1 TO NCUENTAWORD
    CPALABRA = ALLTRIM(GETWORDNUM(m.LCSEARCH,ICUENTA))
    CFILTROCADENA = CFILTROCADENA+IIF(ICUENTA>0,[ and ],[])+ 'UPPER(cust_id+company+contact+TITLE)'+[ like '%]+CPALABRA+[%']
    _VAL = _VAL+IIF(ICUENTA>1,[ or ],[])+["]+CPALABRA+[" $ UPPER(&_ROW)]
    ENDFOR
    IF !EMPTY(CFILTROCADENA)
    CFILTROCADENA=[(]+CFILTROCADENA+[)]
    ENDIF
    *
    ZAP IN JUNK1
    INSERT INTO JUNK1 SELECT COMPANY, CONTACT, TITLE ;
    FROM HOME(2)+"data\customer" ;
    WHERE &CFILTROCADENA
    GO TOP IN JUNK1
    *
    FOR I=1 TO THISFORM.GRID1.COLUMNCOUNT
    THISFORM.GRID1.COLUMNS(I).DYNAMICFORECOLOR=''
    _ROW = THISFORM.GRID1.COLUMNS(I).CONTROLSOURCE
    THISFORM.GRID1.COLUMNS(I).DYNAMICFONTBOLD='&_VAL'
    THISFORM.GRID1.COLUMNS(I).DYNAMICFORECOLOR='IIF(&_VAL,RGB(255,0,0),0)'
    ENDFOR
    *
    THISFORM.GRID1.REFRESH
    ENDPROC

    PROCEDURE DESTROY
    CLEAR EVENTS
    ENDPROC
    ENDDEFINE

    ReplyDelete
  2. Thank you! I am glad to see a participation in my posts.

    Yes, applying Anchor to grid is definitely good. For columns inside to be resized as well, please look for ssAnchorSizer class which I have shared inside Foxite forum before.

    However, I hope you won't take what I will write below in a negative manner. We are here simply to exchange ideas to give readers options in doing things, but:

    a. Closing the source table immediately on load is not advisable because we will still have to use the same on our later searching. What will happen is because we closed it there, then SQL SELECT will again open it.

    b. Using Dynamic properties in the Grid's InteractiveChange event, IMHO, it is also not advisable because the grid will perform that on every key presses on search box as versus setting it once in the Grid's Init event like what I have shown.

    Although in some cases, we can use that approach if Dynamic properties condition will constantly change based on new results.

    Warm Regards,

    Jun

    ReplyDelete
  3. Hola como ejecuto este códgio? donde y como lo instalo? Gracias Mariano

    ReplyDelete
    Replies
    1. Esto es sólo una muestra, que puede adoptar la forma que muestro en sus propias formas.

      That is just a sample, you can adopt the way I show on your own forms.

      Delete
  4. Excelentes ejemplos, y aportes fabulosos: Gracias Tigres del Zorro que sigue corriendo fuerte...

    ReplyDelete
  5. What a great blog, to learn from a great master, thanks so much for helping others

    ReplyDelete