Issue with COUNTALL() and numeric fields
As it currently seems impossible to create new topics in the ‘Bugs’ category, I’m posting this here.
As best I can recall, this seems to be a recent change to the behavior of the COUNTALL() function — but I can’t remember where I made use of the function in an existing base to confirm.
The Formula Field Reference says
| Operator | Description | Example |
|---|---|---|
COUNTA(textOrNumber1, [number2, …])
|
Count the number of non-empty values. This function counts both numeric and text values. | COUNTA(1,2,3,'','four') => 4 |
COUNTALL(textOrNumber1, [number2, …])
|
Count the number of all elements including text and blanks. | COUNTALL(1,2,3,'','four') => 5 |
The context-sensitive documentation of COUNTALL() that pops up when configuring an aggregation function for a rollup field says
Counts the number of linked records. Choosing any field of the linked table will produce the same result. This function counts all values including blank records.
However, this is not how COUNTALL() works when the rolled-up value is a number or other numeric field (e.g., currency, duration); instead, it returns the same value as COUNTA(), ignoring blank values in the rolled-up field.
Here is a link to a base demonstrating this bug.
The first record in the [Parent] table links to 10 records in [Child]. [Child] contains two data fields, {Name}, an autonumber field, and {Value}, a number; one of the records in [Child] contains a blank value for {Value}.
As you can see from the following snippet of a screenshot, using a count field to count [Child] records from [Parent] works as expected, indicating 10 records.
A rollup field that rolls up {Value} using COUNTALL(), on the other hand, shows only 9 records — the same value returned by COUNTA(). Despite the online documentation’s claim that
[c]hoosing any field of the linked table will produce the same result
using COUNTALL() to roll up {Name}, where no record contains a blank value, returns 10.
Interestingly, if I duplicate {Child::Value} and change the field type from number to single-line text (shown here as {ValueText}), COUNTALL() correctly indicates 10 linked records.
