How does work the Count() function?

Hi,

the question says it all. The documentation reads Returns the number of valid, non-blank values in a field. What is a value in there ?

It's Friday afternoon, and my mind just can't understand.

Many thanks

Non-blank means isempty (field) would return false. Valid applies to fields that expect a value type (date, time, timestamp). A ? in these fields indicates an invalid value.

And I forgot… value is anything, including nothing, in a field.

Just as it says. If a field is empty, it doesn't count :smile:

think of survey responses. Count how many people provided an answer to question 3.

I am copying a response to a private message to clarify my last post in this conversation.

A value, in the context of a field, is the field's content. If a field contains nothing, then nothing is its value. An empty value is still a value.

From a statistical perspective, empty values are omitted from statistical sets because they are considered invalid, missing or non-existant values. Empty values require a proxy to be considered in statistics. Proxy, here, could be a calculated field that equates empty to a non-empty value, such as true or false. IsEmpty (value) is one such calculation.

Hope this helps.

To sum up,

Count() works with the record set for the TO the Layout is based on - in fact the Found Set if a Find was executed. It goes through this set and count the fields that are not empty.

Whew, now I understand what the original developer was doing. He writes something in this field under a certain condition or wipe.

Count() in the hep file could benefit from a revision IMHO.

Count doesn't work on found sets. It works on a related set or a series of values. If you give it a field of the current context, it will only count the value of the current record.

There is a summary field type equivalent to Count. Summary fields source their values based on the found set.

2 Likes

Thanks for the added explanation.

The “count” set of functions is a bit more nuanced than the provided explanations.

COUNT works across a relationship for records. The relationship provide the impetus for the found set of child records (think of relationships in FileMaker as being equivalent to queries)

But the equivalent exists in a single table; the summary calc COUNT auto calculates to the found set in that table.

In either case, the function has to traverse the set of records in order to return a value.

A very common use case for count is the number of records in a related table BUT it can be accomplished far faster by setting an UNSTORED calculation field in each table that does a Get(FoundCount). Referencing this field across a relationship gets a record count of the related records without the overhead of a parent count calculation.

Another TRICK in the stable of Count approaches is a combination of the list command (across a relationship) and a patternCount. I use this a lot for quick dashboards displayed in segments of a button bar.

PatternCount ( list ( relatedPRIORITYfield ) ; “high” )

Returns the number of priority items in the child table that are high priority. Repeat for each

A general tip; look at the list of summary calculation options. Each works within the scope of a single table AND each has an equivalent calculation command to work across relationships. Both have situational value.

That can fail if a list contains, say, "high school" and "highlight". I suggest the following instead:

ValueCount (FilterValues (List (relatedField); "high))
1 Like

Good approach on a list of data that is highly variable - you'd have to add those filter value items to the command, or use ValueListItems () to get there. That chain of 4 nested commands would be more readable in a sequence of LET statements, accomplishing the same thing.

For things like Priority, a value list limits the input.
A popup field is best for short lists, especially on Windows where type-ahead in popup lists does not work, as it does not allow alternative input.
A drop down field works best if on windows for longer lists, but adding the field validation of always matching value list content is "nagware" - not a fan, but forced to comply on Windows.