Slooow Relational Finds

[LemlerInv]

Hi All,

I'm sure this has been discussed before, but I couldn't find it in the archives.

I'm on a Mac, OSX, FMP6. The databases are being served with FMPServer 5.5 on a Mac G5 Desktop.

I have two files, Defendants.fp5 and Checks.fp5. One to many relationship. While working in the Defendants file, I need to perform a Find which includes one related field from the Checks file. The find parameters can include defendant's zip code or address, warrant outstanding, warrant not served and that the check is "active". The Active field lies in the related Checks file. The longest I've let the Find run is 15 minutes before giving up. I've made sure all indexable fields are indexed

I also set up an Active calculation field in the Defendant's database, but it's based on the data in the Checks database and being unstored takes just as long.

I've thought about moving the Active results in the Defendant's file to another field which looks up the data in the Active field, but don't believe the data in the lookup field will be updated when the original field changes from Active in the Checks file. It would be very bad to arrest someone with a zero balance. And I don't want to store all defendant information which might be searched with each check. I'm at 1.2 GB now.

The results of these Finds will allow for the organization and streamlining of warrants to be served.

Any suggestions or options are much appreciated.

Thanks, Lisa

[-Queue-]

When doing a find on unstored calculations or related records, it's often much faster to perform your find without those fields included, then loop through the found set and omit the ones that don't meet the extra criteria. Something like

Set Error Capture [On]

Perform Find [Restore]

If [status(CurrentFoundCount)]

Loop

If [not Sum(Checks::cActive)]

Omit Record

Else

Go to Record/Request/Page [Exit after last, Next]

End If

Exit Loop If [not Status(CurrentFoundCount)]

End Loop

where cActive is a calculation number field in Checks equal to Active = "yes". If Active is already a boolean number field, then you can use it instead of cActive. If you are not using the Restore option with Perform Find, then you only need to remove the step to Set Field [Checks::Active, "yes_or_something_similar"]. If you are using it, then after you run the script, Cmd-R to Modify Last Find, remove the value in the related Active field, and press Enter. Then open the script, click OK, and select the option to Replace the Find Request.

[Fenton]

I'll just throw in my 2 cents. View as Form (before the loop) can make a Loop go many times faster. Freeze Window at the top also helps. You can View as List again when you're done.

[LemlerInv]

Wonderful! I'll work on that today.

Thank you both for your responses.

[LemlerInv]

Very cool. I can process about 130 defenants in about 20 seconds. I can't thank you enough for being out there.

[-Queue-]

I don't know about Fenton, but I'm ALWAYS "out there".