Speeding up Find on a related record

[cmartin]

Going crrrazy.

Here's my problem:

I am working on fixing up a very scarily designed, uncommented, undocumented, nasty db that is used to track various factors related to clinical research.

A very small piece that is currently an issue:

Two files: Study and CommitteeReview

Study with fields:

Study#

Title

TrialType

OpenDate

ClosedDate

etc.

CommitteeReview with fields:

ReviewDate

Decision

etc.

related in a 1-many relationship (StudyReviewRecord) by Study#

when viewing study record, the history of the committee review is displayed in portal...NOT A PROBLEM just want full picture expressed

Then, the same two files are related in a 1-1 relationship by the concatanation of Study# and MaxRvw Date (StudyMaxReview) so that there is a way to determine the most recent decision by the committee (ie CurrentStatus: approved, disapproved, etc.) where MaxRvwDate is a calculated field in BOTH files = Max(StudyReviewRecord::ReviewDate).

Okay, I'm getting to the actual problem, I swear.

Many, many reports are based on the CurrentStatus of the Study. I tried setting up a CurrentStatus field (calculated, text) in the Study record and then finding or omitting based on this but this field cannot be indexed due to its being based on a related field and this is way toooo slow.

Then I simply dropped the StudyMaxReview::Decision (which can be indexed) field onto the layout and tried to FIND on this but it returns no records.

What am I doing wrong? Any hints greatly appreciated!

[CobaltSky]

Your StudyMaxReview relationship is based on a calculation which uses the Max function on a related field, and therefore must be an unstored calculation.

Calculations do not work when the database is in find mode, so a relationship based on an unstored calc becomes invalid the instant you enter find mode - thus your find is not able to locate any matching records.

One way you might consider speeding working around this would be to create a stored 'CurrentStatus' field as a lookup based on the relationship, however you will then need to script a 'relookup' based on the Study# field in order to refresh the decision data before each find.

[cmartin]

I wll try this idea! Thanks for the input. I'll keep you posted.

[cmartin]

Is this really the best solution? I still get a bit of a time drag in that I need to perform the relookup every time each of the reports is run (as you mentioned). I must say though already a marked perofrmance improvement...so if that's what I have to take, i'll take it. Thanks for the advice.