SET FILTER does work even with a large table. It is the grid that doesn't work properly with a filtered record set. There's no way to fix it. I wrote a long article for FoxPro Advisor on this in 97.

Your options are:

1) Build filtered indexes on the fly. Index on SortSequence tag sometag for filterexpression. That is worse than using SQL.

2) Use SET KEY. If you allow the user to reorder the columns, you'd need specialized indexes for each column times the number of possible "filters". This is usually an unknown number. This can work, but in the long run, it becomes very cumbersome to maintain.

Assume a filter on City, but ordered by customer name. You'd have to keep an index in the customer table that references the city table. IMO, that's a bad practice. You'd have to open the city table before you ever set order to that tag.

SELECT Customers
INDEX on PADR(UPPER(ALLTRIM(Cities.Cty_Name)) + ":" + UPPER(ALLTRIM(Customers.Cus_Name)),50) tag sometag

To "Filter" customers from Toronto that start with "A"...

SET KEY TO "TORONTO:A","TORONTO:A"

3) Extract only the records the user wants by creating an SQL command using the filter. Index the resulting set per column. That can be a problem when the user asks for large numbers of records.

4) Build your own custom grid control that uses Rushmore aware commands to navigate from record to record and stops when it ...

a) runs out of records to display (EOF)

b) has filled in all the "rows"

I toyed with this for a while and it was extremely fast, but it's a lot of work to make a grid that has the flexibility of the VFP grid. I thought of using lists, but it just doesn't look like a grid.

I end up going with choice 3 regularly, almost exclusively. It is scalable to SQL Server or other backends. It has a significant advantage over the other methods. If you had a customer table and a city lookup table, it is a lot of work to show the customer table ordered by city name, using relations etc. With SQL, you grab the data, including the city name and then index that. If you want to filter the customer records by city, that also is easier with SQL. The grid then has no problem slowing down because there are no filters. There is a performance hit extracting large numbers of records. But the customer rarely needs to see more than 10,000 records.

HTH


>I have a VFP8 application that includes grid controls on forms that are bound to a table that has 4 million records and is over a gigabyte in size. The grid controls work very well for users to view and edit the data.
>
>While the users can find their data easily with a SEEK command, I would like to add the ability to filter the data based on a user specified criteria.
>
>Obviously SET FILTER is out of the question with a table of this size, but at the command prompt a simple BROWSE FOR table.field = "xyz" for the commonly needed filtering works very well with the indexes that I have.
>
>Is there an way to get the same Rushmore optimized filtering in a grid control?
>
>Thanks,
>Jeff