Thanks, I spent the day playing with yours and other suggestions.
I noticed SET KEY in the suggestions below and did some research/testing on it. It appears to function quite well. Do you beleive that SET KEY is a more efficient/faster way of connecting tables than SET FILTER???
NOTE: I have decided that I have too much time already invested (one month of a two month project) in my current code to start again (which I would have to do if I changed my logic to VIEWs rather than the current FILTER {or possibly SET KEY} system.)
>Ian,
>
>Grids and large tables with filters perform horribly slow, even when proper indexes exist. Grids were not optimized to work with what I'd call FP2.x style coding. SET KEY provides some relief from the slowness. If you use the LinkMaster, RelationalExpr and ChildOrder properties they will perform better but IMHO they are a pain to work with because you have to make sure the parent table stays selected.
>
>In all of my grids now I use either a parameterized view of child tables or simple temp cursors. Once you work through the mechanics of p-views they are wonderfully simple to use. There is an example on my website showing the two styles of grids. It also shows how easy it is to create a p-view. Why don't you take a look and see if you think it'll work.
>
>The p-view would be like:
>
create sql view viewPurchasePartners as;
>select * ;
> from purchase_partners ;
> where purchase_partners.sale_id = ?sales.sale_id ;
> order by SomeField
>
>Drag the p-view into the DE, set it to NoDataOnLoad=.t., then in the Form.Init after you are on the sales record you want to start do a Requery( "viewPurchasePartners" )
>
>With the two p-views set up for the child tables all you have to do in the Refresh() of your form, which you have to call after your parent table record navigation anyway, all you have to do is:
>
>Requery( "viewPurchasePartners" )
>Requery( "viewSalesLine" )
>
>>Here is the mission; should you choose to accept it...
>>I have five tables open in a DATA ENVIRONMENT for a form, 3 that are linked to each other and 2 FREE TABLES. The two FREE TABLES should be irrelavent here. I want one of the 3 linked tables (
SALES) to control two other linked tables (
SALE_LINES and
PURCHASE_PARTNERS) in that environment.
>>
>>
sales.sale_id is a Primary index
>>
purchase_partners.sale_id is a Regular index.
>>
sale_lines.sale_id is a Regular index
>>
>>In the data environment I have set the FILTER for
PURCHASE_PARTNERS to
purchase_partners.sale_id=sales.sale_id purchase_partners information is displayed on the form using typical controls; nothing unusual. This information is filtered properly and appears as expected in the form.
>>
>>In the data environment I have set the FILTER for
SALE_LINES to
sale_lines.sale_id=sales.sale_id. sale_lines information is displayed in a grid. The expected entries in my
sale_lines grid appear correctly only when the form is first loaded. When I SKIP+1 in the
sales table then grid simply shows no entries. If I SKIP-1 in the
sales back to the original sale then the correct
sale_line information for that sale is displayed in my grid.
>>
>>The
sale_lines table filter does not seem to be changing as the
sales change.
>>
>>I have spent 3 hours trying all the usual stuff, including:
>>- removing the tables from the data environment and readding them
>>- forcing the filter to update after my: SELECT SALES, SKIP+1, by doing a SELECT SALE_LINES, SET FILTER TO SALES_LINES.SALE_ID = SALES.SALE_ID
>>- checking the Help files
>>- swearing
>>Nothing has helped. I am now baffled. This can't be a limitation of VFP. A VFP table must be able to control two other tables in the same database using the filter command.