A Lightning Fast Alternative to the Count() Function

Today, a colleague of mine here at Digital Fusion pointed me to a particular thread on the FileMaker TechTalk Forum in which the poster - Morgan Jones of One Part Harmony posed the following (abridged):

Dear Folks,

I have for many years just used the "Count" function to determine how many related (child) records exist for a given parent record. I just point this function to the unique key in the child records to determine a related-record count.

In cases where the number of related records for a given parent is large (a couple of thousand, for example), FileMaker pops up a progress bar saying "Summarizing field: xxx" when my app accesses a different parent record with many related child records. "Summarizing" can take 5 seconds for a couple of thousand child records when connected via a LAN, and much, much longer if connecting over the WAN.

I'm just wondering if there is a more efficient method for obtaining the count of related records, preferably one that can be done in a single calculation such that FileMaker doesn't have to traverse the list of values of the field referenced in the Count function call.

This is a classic problem with the summary functions that FileMaker provides. I'm sure any developer has been frustrated with the slowness of the Count function across a large number of records.

A brilliant solution was given by Tom Elliot:

In child: unstored calc field = Get ( RecordNumber )
in parent: unstored field = Last ( )

this is based on the *assumption* that neither Last() nor Get
( RecordNumber ) need to traverse records to get a result. Also, I believe that Count() returns the number of values that are non-
empty and valid (rather than unique) - at least that's what the
documentation says

Simple, elegant, and it works unbelievably well. To understand why this method of counting related records works so well, it pays to first understand why Count performs the way it does.

The Count Function

The Count() function, according to the FileMaker help, returns the number of values that are non-empty and valid. The key word here is "valid". This means for example, that if you run the Count function on a relationship of 500,000 records, then every single one of those 500,000 records must be scanned and validated before the total can be known.

So what does this mean? Well, if the database is hosted on FileMaker server it means all 500,000 records must be downloaded from the server to the client machine, the validation check actually occurs on the client machine itself. The slowdown occurs with the downloading of records - slow on LAN, even slower on WAN. Indeed this is what is happening when you see the "summarising record x" dialog.

That is all fine and good, but it is pointless when all we want to do is find the total number of records, without worrying about the validity of the field contents we are counting.

Tom's solution is to skip the Count function and instead use a combination of Get(RecordNumber) and Last() functions.

First, the Get(RecordNumber) function. Typically this function is used to display a records number within a found set of records. if a found count on a layout has 100 records, then the last record will have a record number of 100. that is not to say the record number will always be 100 for that specific record - the record number changes based on the contents of the found set.

Tom has shown that the Get(RecordNumber) function can also be used through a relationship, in addition to a found set.

In the example above, if there are 1,000 related child records, then if we find the contents of the Last records Get(RecordNumber) calculation, then this tells us how many records are in the related records.

There is no need for the Last() function to evaluate any of the other records other than the very last single record. All it needs to do is evaluate Get(RecordNumber) for the last record, it's brilliant !

So in a set of 500,000 related records, the count() function needs to validate 500,000 records , whereas Last() needs to reference one!

Speed tests on 500,000 records in a local database, show that the Count() function takes about 90 seconds, whereas Last() takes less than one second!

This is definitely one of the coolest techniques I have ever seen for FileMaker, and I can't wait to implement it in my solutions! If this technique is old news and been around for years then I apologise (it's new to me!)

FOLLOWUP: An Even BETTER method!

I have subsequently implemented this technique in a customers solution with great results. In the end I did not use the Last() implementation, rather I used the Get(FoundCount) method. This involves creating an unstored calculation on the table you want to count records from, and set it's calculation to "Get ( FoundCount )".

Subsequently, you can count records from any portal by placing on the layout this calculation field, obtained via the portal relationship you want to count. This method works brilliantly, and can be used on both unsorted and sorted portals (the last method requires the relationship to be unsorted.

The solution I changed ended up having about 150 calls to the Count function that I had to change. this involved running a DDR on the solution, and searching for all instances of the "Count" function. It was incredibly fast to make the change, as it required removing the count function, and replacing the field that was being counted, with the new unstored calculation added to the table. Everything worked seamlessly after this change.

The great thing about the using the Get(FoundCount) implementation, is that no additional calculation field is required. The foundcount calculation can be placed direct on the layout from the related table (via the relationship you want to count). Whereas using the Last() implementation, a secondary calculation is required to obtain the record number of the last record.

This is why I now recommend the Get(FoundCount) method over the Last() method!