I thought about dynamically modifying the record source but then this would need some tuning features because over time when they add new offices and have more transactions for the older offices. You'd need to keep a running tab of office record count to know whether you were going to use an index or a SELECT/VIEW. That sounds fairly complicated.

>You're going to have to tolerate a hit in some place. Maybe if it's the main office, you just have an index where all their records are at the top? Then use the SELECT/VIEW for the other offices?
>
>
>>The problem with SQL SELECT and views is that the primary office would be terrible slow because it is the majority of the table.
>>
>>>Grids and filters do not mix well, as you've seen. I'd suggest you either pull out the pertinent records via SQL SELECT into a cursor and use that cursor as the source for the grid or use a view for the grid.
>>>
>>>
>>>>VFP 8 - Windows 2000
>>>>
>>>>We've got a generic finder which has a grid with possible selections that allows the user to change orders on the fly. Each order that the user can select has a corresponding index in the table.
>>>>
>>>>We're using this finder in a simple order-entry application.
>>>>
>>>>We have added an office to their underlying data design because the client is just now opening a new office and needs to handle ordering from each office.
>>>>
>>>>We have a couple tables with more than 150,000 records.
>>>>
>>>>In the finder we need to add a "filter" for that office so that the user sees only the records for that corresponding office.
>>>>
>>>>The problem is that this is very fast for the primary office but very slow for the secondary office which has very few records.
>>>>
>>>>Right now the code looks something like this:
>>>>
>>>>** Set the office filter
>>>>** Issue a Locate
>>>>** Refresh the grid.
>>>>
>>>>The refresh of the grid is very slow.
>>>>
>>>>There are a couple of solutions I can think of, none of which I like.
>>>>
>>>>1. I can add filtered indexes for each of the above offices. This is messy and would require a change of indexes on the fly.
>>>>
>>>>2. I could add a filtered index with reference to a variable. This would mean that the table couldn't be open without first setting the variable.
>>>>
>>>>Having two separate databases for the different offices is out of the question at this point because we be too costly.
>>>>
>>>>Is there an elegant solution to this problem?