Counting Related FileMaker Records

A core feature of FileMaker systems is counting related records…

There are [3] popular ways to do this.

The examples below are based on a data model of ActivityTemplate -< Activity

[1] Count (TO::FN)
Add a field to ActivityTemplate BaseTable
image

Place field on an ActivityTemplate context Layout

Display = slow
Finding and Sorting = slow

[2] FoundCount (TO::FN)
Add a field to the Activity BaseTable
image

Place field on an ActivityTemplate context Layout
image

Display = fast
Finding and Sorting = does NOT work

[3] Last (TO::FN)
Add a field to the Activity BaseTable
image

Add a field to the ActivityTemplate BaseTable

Place field on an ActivityTemplate context Layout

Display = fast (when using an unsorted relationship // our default practice)
Finding and Sorting = works (equally slow as [1])
Please note that I do not know who came up with this, otherwise I'd give attribution

[Summary] I like... [3] Last method ...as the display is fast AND we can Find and Sort on it when we need to. // Best of both worlds.

Here are the results that we got from testing the [3] different methods:

2 Likes

Great info and reporting, thanks!

It seems weird that the most natural method (using a Count() function in the parent table) is 50x slower. Conceptually, method [1] and [2] seem like they should be doing the same thing:

  • determine a set of records (the Found Set or/or Related Records)
  • count them

So I would expect both to be about the same speed. Method [3] is different, since (in theory) it only needs to find a single record (the Last() one) and that could be much faster?

Have you thought about reporting this to Claris as bug or feature enhancement?

1 Like

Thank you for your kind words.

Yes, while Count is the approach that looks right, testing shows there are better ways...

Side note: We occasionally use {{FoundCount}} in a filtered portal (with a Refreshing script Step).

Also, Last is perhaps the last :wink: FileMaker Function that you might think of for this use case, while it is our first :wink: choice for the reasons stated above!

I am relatively content with the tools available for counting related records. I do have some other feature enhancements in mind a topic for another post!

Are these tests local in FMPro or running on a file hosted with FileMakerServer? We've seem some pretty dramatic (10x to 100x) speed differences with Pro vs. Server, and also across version #s. Supposedly there are some speed enhancements in FMS 21 (2024) as well...

If I recall correctly, the tests that I did were with a File hosted on FileMaker Server. I would expect the relative speeds would be similar (hosted vs local).

Someone might want to test using the FileMaker versions that came out since the test that I did in 2020-09-04.

the explanation regarding count and found count is really simple. The count downloads the records but the found count is almost instant since filemaker use it internally like lets say meta data