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?