I've been unable to find a suitable workaround to the following issue, and was hoping someone here might be able to help:

I need to be able to perform finds in my Invoice file for fields such as "Amount Paid" and "Balance Due." The problem I'm encountering is that these fields are based on related records which I access through a portal. Each related record contains order and payment data for one line item on my Invoice. FileMaker 7 does not seem to support Find requests that require accessing information from related records.

The only solution I've seen for this issue is to create a separate set of fields that can contain copied data from the original fields. In order to use this method for a find however, I'd have to run a script before each find that copied and pasted data from these fields in over 50,000 records, which would be very time consuming and inconvenient. Any suggestions would be greatly appreciated.

The solution can be streamlined. For example, suppose you create a calculated field which works out the amount due after payments are made to individual items. This involves a calculation over a set of related records and cannot be stored. Transferring this data to a second (non-calculation) field comprises a one-off initialization, which will take a while, and then script the copying to take place whenever one of the calculation fields changes value, which is a tiny extra step in your payment or invoicing process. That way each such field is always up-to-date and ready for a find. However, if your 50,000 fields are constantly changing in value or being imported in large batches then you do have a problem.

"FileMaker 7 does not seem to support Find requests that require accessing information from related records."

I dunno where you got that idea from!

Finds in related fields are a bit slower than finds in local fields, but they work.