A Lightning Fast Alternative to the Count() Function
By Daniel Wood, 15 December 2010
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!
Something to say? Post a comment...
Comments
Daniel Wood 24/11/2022 9:00am (2 years ago)
hi Martin,
Doesn't matter if it's checked or not, the result is the same in both instances. This is because that checkbox only checks fields, if no fields are referenced at all then the checkbox does not apply.
Martin Pineault 21/11/2022 1:30am (2 years ago)
Hello,
Regarding the great Get ( Foundcount ) method, did you uncheck the "Do not evaluate if all referenced fields are empty"?
Or it's a "doesn't matter" case?
Thank you.
Tobias Sjögren 26/01/2021 5:59pm (4 years ago)
https://community.claris.com/en/s/question/0D53w00005CjdcrCAB/sloow-aggregate-functions
Tobias Sjögren 26/01/2021 5:31am (4 years ago)
Coming back to this great blog post after a few years of using this technique: I assume the functions "Average", "Max", "Min" and "Sum" also download all records since they also return Valid values. I have to check that - or have you already done so?
pierre 16/04/2019 4:42am (6 years ago)
Hi Phil,
I have used a technique, I do not know if it is the same, where I put a merge field <<Get(FoundCount)>> on a empty row of a portal.. and it summarizes the number of records WITHOUT having created a field in the child-table.
Daniel Wood 29/11/2016 12:33pm (8 years ago)
hi Phil, sorry for not responding sooner. Yes the issues you are experiencing will be due to the field being a global - for this to work it must be non-global and unstored. This means you'll need to put it on every table, it's not enough to have one of them on an arbitrary table and use that on layouts because it is global, this won't work :)
Phil Hanson 18/11/2016 7:14pm (8 years ago)
Thanks for the article Daniel.
I made this change in a few places, but some gave unexpected results. Could it be because I make the Get(FoundCount) field in the child table a global?
Miguel Angel Fernandez 29/02/2016 9:49am (9 years ago)
Hi,
That's simply awesome!
That aproach led me to the use of summary fields as there have many more options than just count records.
Thanks for this enlighting post.
Kind regards,
Miguel
Chad 08/07/2014 3:14am (10 years ago)
I couldn't get either features to work for me...especially in list view with a report....
I did find an alternative! In the parent table create a calculation field with the following formula with result as number: ValueCount( List(ChildTable:ChildID)) It seems to work much quicker than Count and works just like count.
Rusty 23/07/2013 5:03am (11 years ago)
Thanks!
The Get(FoundCount) method doesn't seem to work on a filtered portal though. I guess I have to decide if it is worth creating a new relationship instead of using a filter. Am I missing something?
May 21/09/2012 12:16pm (12 years ago)
Ooh - just did a search and figured it out! I start my script with Set Error Capture ON and it suppresses the no matching records "error" message. So easy!
May 21/09/2012 12:03pm (12 years ago)
I love this Get(FoundCount) for quick counts, and I have a long script that generates a report of many counts of status items by performing the find and then setting the field to that get(foundcount) - 1 by 1. This is great - however, my issue comes if the get(foundcount) is 0 (like for a status item that no one has yet completed - though they will in the future). When I run the script and it gets to the Find with a 0 found count, a diolog box comes up that says "No records match this find criteria." There's the option to "Continue" which is fine but I'm sure my end users viewing the report will all go "Huh?" when they try to generate the report. Is there any way to avoid this? I'm fine that my found count is 0 - I just want my field to set to be that instead of it telling me there are 0 matching records and needing to deal with a dialog box. Any ideas????
Daniel Wood 19/08/2011 9:11am (13 years ago)
Hey Darren, The sum method will be pretty slow yup, as would using the Count aggregate function. I've give this method another crack and you should notice a marked gain in speed. As far as I'm aware summing the 1 calc as a way of counting records may be fractionally faster than the count function, because I believe the count function has to first validate the contents of the field on each record that is used in the count, because Count function only counts valid records. But all that aside, GFC thru relationship is instant.
Darren 19/08/2011 8:22am (13 years ago)
Hi Dan,
I've always used a stored calc = 1 for this, and then sum(calc::field), I also use the GFC method and I can't say I've noticed a difference in speed - but I also can't say that I have explicitly tested the lag time. I'm now guessing that the sum() method is probably slow as hell on large data-sets. I think I might do some testing, ho-hum!
Darren.
Daniel Wood 07/04/2011 6:33pm (14 years ago)
hi Chong, thanks for the comments. To answer your questions:
1. Yes the List function will behave just as slow as the count() function. The reason is that whenever you use the List function, although you may be retrieving a list of one particular field, FileMaker sends you the entire record (minus containers and summary fields) - so what you are actually getting is much more information than the list itself. In this respect it is the same as the count function, which also downloads the records in order to validate the single field, very inefficient on FileMakers behalf if you ask me.
2. As far as I'm aware you cannot use this technique as a summary field replacement for the purposes of showing on a sub-summary report.
Chong-Yee 07/04/2011 4:27pm (14 years ago)
Hi, Daniel,
Great post (as with all the others), which I read this morning while waiting to see the doctor. I couldn't wait to get out of the surgery to try out the technique!
A couple of questions spring to mind:
1. Do the issues you describe with the Count() function also apply to a two step technique, where you first set a variable to List() the IDs of the related records, and then do a ValueCount() on the items in the variable?
Looking through my code, it seems like this is what I tend to do - for no other reason than that it hadn't occurred to me that I could directly Count() the related records.
2. Similarly, with summary fields; can you, in the child table, replace a summary field of the number of records with a field to Get( FoundCount ) to get the speed increases?
Regards,
Chong-Yee
Rahul Kohli 18/02/2011 10:08pm (14 years ago)
Nice!
Daniel 13/01/2011 12:40pm (14 years ago)
I should have said:
Last ( Child::RecordNumberField )
, you need to put Get(RecordNumber) in a calc field, and then use the last function to obtain that field value through the relationship, it won't work directly referencing Get(RecordNumber) in the Last function?
Daniel 13/01/2011 12:39pm (14 years ago)
Hi Jon,
the Last function is used to obtain the value of a field from the last record through a relationship, ie "Last ( DateField )" , would return the value from DateFIeld on the last record through relationship.
The idea here is that if you have an unstored calc on your child table set to "Get(RecordNumber)" , then each record will contain a number 1 thru to the # of records in the relationship.
If you then grab the record number of the last record through the relationship, ie:
Last ( Get ( RecordNumber ) )
then this should tell you the total number of records found through the relationship.
There is actually a nicer way to implement this solution which I mention in the followup section, which means the whole use of the Last function for this is somewhat not needed, though both methods still work fine.
Jon 13/01/2011 12:30pm (14 years ago)
What do you mean use the Last() function? What do you put in the Last() function?
Tony White 04/01/2011 7:01am (14 years ago)
Thanks for this post and the others as well. Lots of good stuff.
I just swapped out some count fields. I used the last function as the relationships that I am using are not sorted and I also wanted to be able to find on the "count" field. I tested and was not able to search using the Get(FoundCount) method.
I look forward to reading more of your posts. Thanks again.
All the best,
Tony White
Tony White Designs, Inc.
http://www.twdesigns.com
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments