Filtering records in grids

I have a grid and a combobox placed on a page of a pageframe.

The grid successfully shows the required data from a table.

The combobox is used to filter the data on the table.

I have placed the following code in the interactive change of the combobox.

m.cProjType = THIS.value

SELECT tmpProjData

SET FILTER TO tmpProjData.ProjType = m.cProjData

THISFORM.upgf1.page1.ugrid1.refresh

This code successfully filters the data in the grid.

If at some stage later the grid gets the focus, i.e. a mouse click, an error message appears "Variable 'cProjType' cannot be found" and all the records appear in the grid again.

If any other control is clicked the filtered records remain in the grid.

Can someone please advise what is causing the problem and how it can be overcome.

Many thanks.

The problem with your code is that cProjType variable is going out of scope.

But let me first tell you, that using FILTERS with grids is not recommended approach. The grid's scrollbars will behave wrongly and the performance may suffer, especially if only small amount of records satisfy filter criteria. You may want to look into alternative ways of sub-selecting data - local views, CursorAdapters (VFP8+ only), select-sql, even SET KEY

Having said that, to solve your problem you need to do something like

This way is going to work.

But as I said, you may be better off using other methods of filtering information.

Filters are not reliable and never has been. If you'd still use a filter then instead of a local or private variable use a variable that grid (where filter expression is needed) can see too. ie: A form property:

thisform.Addproperty('gridFilterData', this.value)

select tmpProjData
set filter to tmpProjData.ProjType == thisform.gridFilterData

But a key would be much more powerfull:

m.cProjType = this.Value

select tmpProjData

set key to m.cProjType

provided it has an index ProjType and it's the current index.

Yet another alternative is using SQL as recordsource of the grid. ie:

with this.myGrid

.recordsourcetype = 4

.recordsource = "select myField1, myField2 from myProjTable"+;

" where projType = thisform.myCombo.Value into cursor tmpProjData"

endwith

* refresh - combo.IAC

with thisform.myGrid

.recordsource = .recourdsource

endwith

Yet another alternative is using relations or childorder, linkmaster, relationalexpr properties.

Yet others exist like view, ca ...

Thank you for the assistance to date, but unfortunately I have not yet succeeded in my aims!

As there will be five comboboxes on the form to enable the users to filter out unwanted records the SQL method seems the most appropriate way to tackle the challenge as the SQL statement that is passed to the RecordSource can be constructed to take account of the user's selections.

To keep matters simple I only have one combo box active at present.

When the form is loaded I use an SQL statement in the Form's init method to place the SELECT statement in the RecordSource to populate the grid. This works successfully.

The code in the InterActiveChange method of the combobox is in two parts:

1. Firstly the code is directed to a method for the form I have created which will eventually gather all the variables from all the combo boxes. This method set the grids RecordSourceType = 4 and the SELECT statement into the RecordSource.

2. Use the coding you advised above * refresh - combo.IAC.

When the selection is made from the combobox the grid empties completely. I no longer get the error message when the grid gets the focus.

Out of interest I placed the SQL statement that works successfully when the form loads into the IAC method of the combobox but the grid remained blank.

Am I doing something in the wrong order or placing code in the wrong method

Can you assist me further please

The reason you're experiencing this problem is caused by so called grid reconstruction issue. I usually solve this problem using "safe selected" approach described by Andy Kramek in one of his blogs on foxite.

The idea is to create a cursor in form's Load with all the fields for the grid and then construct the grid in design time.

When you need to re-populate the grid, select records into the temp cursor, e.g.

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

This avoids grid reconstruction problem completely, though may slightly slow the process for the huge resultsets to insert.

The other approach I don't use anymore, but some still like to use, is to set grid's recorsource to "" before doing the select, do the select, then re-set the recordsource.

I must be doing something wrong somewhere!

Taking on board your advice I have tackled the problem in three ways

1. Using Andy Kramek's foxite blog and the first section of 'Safe Select' code

2. Using Andy Kramek's foxite blog and the second section of 'Safe Select' code creating a 'safeselect' method on the form.

3. Using the code snippet examples in your reply

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

First Way

I think that this got me the closest to achieving my goals.

I placed the safe select code in the combobox IAC method. The code ran without a hitch but at the end all I could see where the grid lines by no data. I placed a break in the code so that I could look at the cursor. The cursor was there with the correct data in it. For some reason I seem not to be pointing the controlsources of the grid columns to the cursor. After the safe select code has run I have code that sets the property of the grid; i.e.

RowSourceType = 4

RowSource = sqlTxt (a variable containing the SQL statement)

....... .ControlSource = cursor.fieldname (for each column)

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

Second Way

I created a new form method and placed the safe select code in it.

The method is then called from the combobox IAC by using "THISFORM.safeselect(param1,param2)", param1 being a variable containing the SQL Select statement, and param2 being the name of the cursor from which the grid is to obtain its data.

When a selection is made from the combobox the code stops at ZAP IN (tcAlias) with an error message "File is open in another work area'. By reference to the debugger screen the local variable lnSelect has a value of 1.

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

Third Way

In the form's load method the cursor is created.

The grid is populated with all records when the form is activated.

Your code snippet is then place in the combobox IAC.

When a selection is made from the combobox the code stops at ZAP IN GridCursor with an error message "File is open in another work area'. If I ignore the error the code stops at the next line INSERT INTO ..... with an error message "Cannot update the cursor GridCursor, since it is read only'.

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

As each way actually creates the cursor is the .grid.recordsourcetype = 4 still correct

Any suggestions please as to why the Safe Select method does not work for me

I'm sorry you wasted so much time because I neglected to say, that you should use alias as the RecordSourceType for the grid and name of cursor as the RecordSource.

Here are the exact steps that I never had any problems before:

1. In form's load

CREATE CURSOR GridCursor (field1 C(30),....)

INDEX ON field1 tag tag1

.....

When you need to re-populate the grid, use the code from my previous message.

You don't need to touch any grid properties which are set in design-time

Of course, this idea works only if you always select the same fields. If you need to change fields, displayed in the grid, then, of course, you would have to re-build the grid in code. I assume the first case where the cursor's structure doesn't change.

If in Load you create your cursor using select statement, then add READWRITE clause at the end.

So, again, don't use select SQL as recordsourcetype for the grid, but use Alias.

One more little catch - always specify column's ControlSources in Alias.Field way and not just Field. I had troubles in the past using just field portion.

Your name of GridCursor will not change. If you're using a builder in design-time, you may need to create a temp table with the same name as you're going to use for the grid's cursor, design the grid and drop the table.

Also use SET SAFETY OFF setting for your form.

Success at last, so easy when you know how!

Thankyou very much for your assistance.