how filter a grid from a value displayed in a text box

[AlastairOz]

I have a text box on a form with a grid
I want to filter the grid on the form based on the value in the text box

eg

SELECT alias
SET FILTER TO fieldname = textbox VALUE && somehow

 

[Mike Gagnon]

What is the question?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[cbasoz]

Hi,
Do not use "set filter" and do not use it especially with grids.

The ideal filtering for grid is, if the field to be filtered is indexed and you are doing something like:

Country = 'USA'

or:

Country = 'U' && starts with U when set exact is off

rather than Country contains 'SA' then:

set key to 'USA'

or:

set key to range 'U','UZZ'

is the fastest filtering for a grid.

Having said that, in the scenario you describe, probably the best method is to use an SQL recordsource for the grid and reload it. Here is a sample:

Public oForm
oForm = Createobject('SampleForm')
oForm.Show()

Define Class SampleForm As Form
  DataSession = 2
  Height=300
  Width=500

  Add Object txtName As TextBox With Top = 10, Left = 10
  Add Object myGrid As Grid With Top=40,Height=260,Width=500,Anchor=15

  Procedure myGrid.Init

    Local SearchName
    SearchName = '%' && do not need this if initially I want the grid empty

    With This
      .RecordSourceType = 4
      .RecordSource = "SELECT * " +;
        "  FROM ('" + _samples + "data\customer') "+;
        "  WHERE UPPER(Contact) like ?m.SearchName" +;
        "  INTO cursor crsResults nofilter"
    Endwith
  Endproc

  Procedure txtName.InteractiveChange
    Local SearchName
    * Contains
    *SearchName = '%'+Upper(Trim(This.Value))+'%'
    
    * Starts with
    SearchName = Upper(Trim(This.Value))+'%'

    * Exactly equal
    *SearchName = Upper(Trim(This.Value))
    Thisform.myGrid.RecordSource = Thisform.myGrid.RecordSource
  Endproc
Enddefine

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

You're right, Cetin. Even though there's grid.optimize, which helps, if indexes are available which can optimize the filter, the filter approach sooner or later get's sluggish and slow.

Despite of that I want to add: You were almost there with the FILTER approach: Set Filter To Thisform.Text1.Value is making the filter work.

Using the SQL-Select recordsource, like in Cetin's example also will get sluggish, the more data you have, but it rather depends on how much data the select retrieves, how large the resultset is. The size of the whole table matters too but less than linear, if SQL can be optimised.

Correcting indexing of data is of the essence.

Bye, Olaf.

 

[AlastairOz]

Thanks for your replies,

Like I said:
I want to filter the grid on the form based on the value in the text box

eg

SELECT alias
SET FILTER TO fieldname = textbox VALUE && somehow

can someone please show me how to do the above filter.
I am only just starting to learn VFP, so even though there
may be much better ways to do things, at this stage I have to keep my coding simple.
If I can filter the table based on the value in the text box, I will be very happy.
Please help.

 

[Mike Lewis]

Alastair,

In general, filters don't work very well with grids. I won't go into the reasons here. Please accept it for now.

It's usually better to create a cursor for the grid values. A cursor is simply a temporary table that's created on the fly, according to some criterion. In this case, the criterion is the value of the text box.

Let's say the underlying table contains a field called City. The user types a city name into the text box. You want the grid to only shows record with the city in question.

The first step is to remove the existing record source from the grid:

THISFORM.MyGrid.RecordSourceType = ""

Then create the cursor. This is usually done with a SQL SELECT statement:

SELECT * FROM SomeTable ;
WHERE ALLTRIM(UPPER(CITY)) = ;
ALLTRIM(UPPER(Thisform.TextBox1.Value)) ;
INTO CURSOR csrCity

Finally, apply the cursor to the grid:

THISFORM.MyGrid.RecordSource = "csrCity"
THISFORM.MyGrid.SetFocus

You've also got to decide at which point you want the grid to be updated. You could choose to do that as soon as the user hits a key in the text box. That way, the grid will always stay in sync with exactly what's in the text box while the user is typing.

If that's what you want, execute the above code in the text box's InteractiveChange.

The problem with that appoach is that it will be slow, especially if the table contains more than a few hundred records. The alternative is to do it when the user has finished typing the entire textbox, in which case you would execute the same code, but in the textbox's LostFocus.

I've simplified this explanation a bit, but I hope it will answer your question and help you get started.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

You are asking and repeating your question but not reading the replies.
Check my reply, I have already given you a piece of code that is fully operative. Copy it to a code window, select all, right click and execute to see. Then you may also want to spend time on checking the code too.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

BTW about performance, if you have several thousands of records then probably the best would be not to filter at all but sort once at the beginning and seek() the matching record. That is instantly responsive even with several ten thousands of records in a grid.

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

"I am only just starting to learn VFP"

Welcome to the community.

In light of your being new to the language, it might not hurt you to spend some time with the tutorial videos on the following website:

http://www.garfieldhudson.com/FreeVideos.aspx

And you might want to consider looking into some of the excellent books at:

http://www.hentzenwerke.com/catalog/zcatalogsubject.htm#visualfoxpro

Good Luck,
JRB-Bldr

 

[AlastairOz]

Cetin,
That code works a treat. I can see what some if is doing, but with my limit knowledge of VFP I am not sure if I could adapt it for my existing form

Regards

Alastair

 

[Olaf Doschke]

Alastair,

I repeat: Your idea of FILTER is working, you just need to reference the textbox correctly. Typically it's named Text1 and directly on the form, so it's Thisform.Text1, It's value is Thisform.Text1.Value, so it's

Select alias
Set Filter to field = Thisform.Text1.Value

Bye, Olaf.

 

[cbasoz]

Olaf,
set filter with a grid is a bad idea Try this:

Public oForm
oForm = Createobject('MyForm')
oForm.Show()

Define Class myForm As Form
  DataSession = 2

  Add Object myGrid As Grid With RecordSource='DoNotSetFilterWithAGrid'

  Procedure Load
    Create Cursor DoNotSetFilterWithAGrid (uID i Autoinc, myName c(10))
    Local ix
    For ix=1 To 1000000
      Insert Into DoNotSetFilterWithAGrid (myName) Values (Sys(2015))
    Endfor
    Set Filter To uID = 500000
  Endproc
Enddefine

If you think that is normal then hands down.

Cetin Basoz
MS Foxpro MVP, MCP

 

[cbasoz]

Alastair,
It is already adapted. If you can't adapt it to an existing form of yours, then maybe you should pick the wrong route and use set filter (then maybe at least you would want to use SET KEY instead).

Cetin Basoz
MS Foxpro MVP, MCP

 

[Olaf Doschke]

Cetin,

please read all my posts, I already agreed with you earlier and warned about filters not to scale well and getting sluggish with more data. You don't have to show me.

I just reacted to Alstairs demand to first learn the easy way and go from there.

Bye, Olaf.