none
How to Filter Records in a Grid

    Question

  • I have a simple form with a Grid that displays my customer database.  I would like to place a combo box at the top of this form and when I start typing in it the records would start filtering down.  I Have an index created for Lastname and I can list the records but I can't figure out how to make the filter work as I start typing.

     

    Thanks for your help...Scott

    Thursday, May 17, 2007 8:27 PM

Answers

  • Try

     

    SELECT MyTable && never use one letter area code

    SET ORDER TO LNAME && LNAME

    lcKey = alltrim(this.value) && What user entered in the textbox

    SET KEY TO m.lcKey && we now will have only records exactly matching what user entered

    GO TOP

     

    This may not work, as I not tested it.

     

    Another alternative would be in using RANGE clause of SET KEY command, e.g.

     

    lcKeyMin = alltrim(this.value)

    lcKeyMax = padr(m.lcKeyMin,7,'Z') && where 7 is the length of the field in question, you need to put actual field length

     

    SET KEY TO RANGE m.lcKeyMin, m.lcKeyMax

     

    --------------------------------------------------------------------------------------------------------------

    But I also agree with Marcia, that this problem can be solved with local view or Cursor Adapter.

    Friday, May 18, 2007 2:59 AM
  • I have a simple form with a Grid that displays my customer database.  I would like to place a combo box at the top of this form and when I start typing in it the records would start filtering down

     

    The easiest way to do this is to create a parameterized view on your customer table. You can to this quite easily in the view designer and the on-line help tells you how to use it. Then, in the valid of the textbox, all you have to do is requery the view. I would not do this in the InteractiveChange of the textbox because this would requery the view on every key stroke, grinding your application to a halt. This code in the text box's valid to requery the view being used as the grids recordSource:

     

    Code Snippet

    vp_lastname = UPPER( ALLTRIM( This.Value ) )

    REQUERY( [lv_customer] )

    Thisform.grdCustomer.Refresh()

     

     

    Friday, May 18, 2007 10:46 AM

All replies

  • Look into SET KEY command in Help

     

    You may use texbox (why do you want to use combobox?) and put your code either in KeyPress or in the InteractiveChange of the textbox.

     

    I don't recommend using filters with grid.

    Thursday, May 17, 2007 8:34 PM
  • I just saw an example in the foxpro 9.0 sample files that used a combo box, but I'm still learning so if you have any good suggestions I'm all ears... 

     

    I checked out the set key but I'm not clear on how to proceed with the code in the textbox(keypress) code to do what I'm looking for.  Can you shed some light on that as well.

     

    Thanks.... 

    Friday, May 18, 2007 1:22 AM
  • I've become a big fun of Cursor Adapter lately, so I would probably use Cursor Adapter here as well.

     

    Anyway, let's try to work out textbox and SET KEY idea.

     

    In the textbox interactive change try:

     

    lcKey = alltrim(this.value)

     

    ** Assuming the table is ordered by Customer and it is the current alias

     

    SET KEY TO m.lcKey && This should filter all records matching the entered value

     

    ************************************************************************************************************************

    If this would not exactly work as you want, you may construct lcKeyMin, lcKeyMax and use the second version of SET KEY command. Check details in the help.

     

    Let me know, if you would be able to make this idea work.

     

    Other ideas would be using local parameterized view.

    Friday, May 18, 2007 1:30 AM
  • Okay, I've put the lcKey = alltrim(this.value) command in the interactive change code but I'm not sure where to put the SET KEY to m.lckey command.  I thought that it might go in the Keypress code but when I put it there I get a "variable not found LCKEY" error message.

     

     

     

    Friday, May 18, 2007 2:00 AM
  • Both lines of code were for InteractiveChange, I'm sorry if it was not clear. Also the table should be ordered.
    Friday, May 18, 2007 2:23 AM
  • Okay, here's the code that's in the interactivechange.

    SELECT A 

    SET ORDER TO LNAME && LNAME

    lcKey = alltrim(lname)

    SET KEY TO m.lcKey

     

    I suppose the dumb question is do I have the value set right in the "lckey = alltrim(this.value)" command above?  lname is the field that my index in built on which is my customers lastname field.  When I run the form and enter last name of a customer into the textbox nothing appears to be happening..

    Friday, May 18, 2007 2:54 AM
  • Try

     

    SELECT MyTable && never use one letter area code

    SET ORDER TO LNAME && LNAME

    lcKey = alltrim(this.value) && What user entered in the textbox

    SET KEY TO m.lcKey && we now will have only records exactly matching what user entered

    GO TOP

     

    This may not work, as I not tested it.

     

    Another alternative would be in using RANGE clause of SET KEY command, e.g.

     

    lcKeyMin = alltrim(this.value)

    lcKeyMax = padr(m.lcKeyMin,7,'Z') && where 7 is the length of the field in question, you need to put actual field length

     

    SET KEY TO RANGE m.lcKeyMin, m.lcKeyMax

     

    --------------------------------------------------------------------------------------------------------------

    But I also agree with Marcia, that this problem can be solved with local view or Cursor Adapter.

    Friday, May 18, 2007 2:59 AM
  • I have a simple form with a Grid that displays my customer database.  I would like to place a combo box at the top of this form and when I start typing in it the records would start filtering down

     

    The easiest way to do this is to create a parameterized view on your customer table. You can to this quite easily in the view designer and the on-line help tells you how to use it. Then, in the valid of the textbox, all you have to do is requery the view. I would not do this in the InteractiveChange of the textbox because this would requery the view on every key stroke, grinding your application to a halt. This code in the text box's valid to requery the view being used as the grids recordSource:

     

    Code Snippet

    vp_lastname = UPPER( ALLTRIM( This.Value ) )

    REQUERY( [lv_customer] )

    Thisform.grdCustomer.Refresh()

     

     

    Friday, May 18, 2007 10:46 AM