FM Function of the Week: Count

If there’s a theme, or worse, a monotonous repetition to my descriptions of FileMaker’s many functions, it’s probably the bit where I say “this function isn’t complicated, but you can do cool stuff with it.” While I could possibly try harder, this is the last post of the year, so brace yourself for familiarity as we dig into the big bowl of vanilla that is the Count function.

count ( field 1 ; optionalField n )

Count simply tells you how many valid, non-blank values are present in the field or fields you specify. You may string together as many fields as you wish inside the parentheses.

three fields, one of which has no data in it

Given the fields above, Count ( Name ; Phone ; Email ) would return the number 2. If your mind isn’t instantly racing with a profusion of opportunities to use the Count function, you’re not alone. But I’ll give you two examples where Count is very useful to me.

Counting Related Records

Use a related field for the Count parameter and FileMaker will provide you with the total number of related records that contain a value in that field. This can be useful on layouts where you don’t have enough room to show every line of a portal. The number of related records shown at the bottom of the portal below can be derived using Count ( characters::name ).

A portal listing several names with text indicating that scrolling the portal will reveal more names.

Counting related records can also keep you out of hot water when scripting. The Go to Related Record script step will take you from the record you’re currently browsing to its related records on another layout. It’s extremely powerful and useful, but there’s a catch. If the record you’re viewing has no related records, the Go to Related Record script step will not switch layouts. This can be disastrous if, say, your script goes on to delete what you expect to be the related records. With no related records, your script never changed layouts and will go on to delete the parent record you started on. Sandwiching that Go to Related Record script step inside If and End If steps will give you the chance to use Count to test for related records before attempting to switch to them. Below is a simplified example of how the script could be structured.

example of testing for related records in a script

 

Validating Data Entry

When filling out a record, it’s pretty common to require some fields not be left blank. A very common way to confirm that there’s something in a field is to couple the not operator with the IsEmpty function. Using this method to confirm that all three fields from the first screenshot above have data, the calculation would look like this:

using not IsEmpty () to test for data

 

But using Count, we can compact that formula down to a much simpler calculation, and one that can detect invalid data (e.g. text in a date field):

using count to validate data

So once again a superficially unsexy function proves that it’s more useful than is immediately apparent. It’s not as cool as this count or this Count, but Count is indispensable in FileMaker calculations.

This entry was posted in FileMaker 13, Function of the Week. Bookmark the permalink.