Count relationships to other files

[wplate]

Hello.

I have a MAIN file which references a few OTHER files. The MAIN file has thousands of records, but I would like to only see those records that actually reference a least one OTHER file.

I figure I'll make a script that on the file's opening, it will find all records whose Reference Count is greater than Zero.

but I can't figure out how I count the references. Thank you.

[The Bridge]

You could do this:

Reference_Count (unstored, number) = Count(Relationship1::Related_Field) + Count(Relationship2::Related_Field) + Count(Relationship3::Related_Field)

or

has_Reference (unstored, number) = IsValid(Relationship1::Related_Field) + IsValid(Relationship2::Related_Field) + IsValid(Relationship3::Related_Field)

Where Relationship1...3 are the names of relationships you've built to File 1, File 2, and File 3 respectively, and Related_Field is a field in each of those Files which you are certain will contain a value.

Reference_Count will give you a total number of records that a given record in Main is related to. has_Reference will give you a total number of files that a given record in Main is related to.

[wplate]

Thanks, I'll try your suggestion.

[wplate]

It works, but it is very very slow processing the file's 37000 records each time I open the file.

Suggestions for a faster solution would be appreciated. Thank you.

[The Bridge]

I'm not sure how you can really get past the speed barrier, since I know of no other way to determine whether a record has related records in another file.

However, you can make the process a little less painful for users by making this function happen when the file closes down. Instead of defining those fields as unstored calculations (which FileMaker has to process every time), make them standard number fields. Then, on shutdown, run a script that will use Replace... or a loop through records to set the number fields to the appropriate values, using the calculations I gave earlier.

Your startup script would then just need a simple find step to find records with values > 0 in those fields.

The drawback, of course, is that users may complain about how long it takes to shut down the file, but somehow I find it easier to wait for something to close than for it to open. Your users may feel the same way.

Hope this helps, if even a little bit.