slow finding through relationships (I know)

[Ivan X]

Apologies if this has been covered many places elsewhere -- I did search around and couldn't quite find anything that answered my question.

I am using FMP 6 with a hosted solution (FMP 5.5 Server running on an eMac 1 GHz). I know that you're not supposed to find through relationships when using hosted solutions, because it takes a long time. Well, that's the problem I've got, but I just can't seem to figure out what to do differently.

I've got a CONTACTS table, an ARTISTS table, and a TRACKING table which joins the two. When a contact is associated with an artist, a single record is created in TRACKING with an ID which is a concatenation of the numeric ID's from the contact and artist records. Each tracking record contains various checkbox fields (with value 1 or empty).

The main layout lives in the tracking table. It displays both the tracking record fields and various fields which belong to the contacts table (through a relationship). We regularly need to do Finds where a tracking field is set and a state is chosen. For example, Find all tracking records for The Rockets, but only for contacts playing their CD in New York. This would mean that "The Rockets" and the "Playing CD" checkbox are selected (both live in TRACKING), and "NY" is entered into "state" (which lives in CONTACTS).

The problem, of course, is that the Find takes forever because the state field is through a relationship. I've tried various things, such as performing the State find in CONTACTS, and then getting those contacts to display in TRACKING with Go To Related Record(Show) after a Copy All Records on a layout with just the contact ID field, and then refining the set. The problem is that it takes just as long (sometimes longer) waiting for Copy All Records to complete.

So I give up. Here are my questions:

- Does FMP 7 make finding through relationships any faster? Upgrading might just be the easiest solution.

- What could I do to design my database differently, or construct my finds differently, in order to get faster finds of this type?

(What makes matters worse is that I have "upgraded" the client from a completely flat file version of the same database, where every artist's tracking records got their own field -- there were over 2,000 fields in it! However, it made all their finding very fast, and now I'm having a hard time explaining why it's slow...)

Any assistance much appreciated.

Thanks,

Ivan Drucker

New York

[Ender]

A common solution to this is to create fields in the join file that act like copies of the original from one of the parent files. These are easily created using Lookups, looking up the data from the parent. These local fields can then be indexed and searched very quickly. The main trouble with this is that when the parent fields subsequently change, the changes do not automatically propagate to all the related records. So a script can be used to update the related records as needed.

In FM7, this is no longer as big an issue. Searches on related fields are pretty quick (especially with Server 7 as the host.)

[Ivan X]

Thanks for the response -- I thought of doing what you suggested with lookups, but I was worried about things getting out of sync. Having a script clean things up is not a bad idea but man that seems cumbersome for something which doesn't seem like that big a deal!

So I'm most interested in FM7 as a solution if the money-spenders will bite. Is the improved performance on related fields present when the relationship is to separate files, or only when it is between tables in the same file?

Again, thanks for your response.

Ivan.

[Ender]

Is the improved performance on related fields present when the relationship is to separate files, or only when it is between tables in the same file?

Performance is the same for both.