Steps to reproduce
Task: Show oldest person by department
Create to tables (e.g. Department [Name] and People [Name, Age])
Link one to the other with a multi-selection field (multiple people within a department)
Populate an link the tables on a way that the department’s member’s ages are NOT in order
Department 1, in my case, links to Jill (20), Bob (42), Rebecca (29)
In the department table, create 2 formula columns: max({People.Age}) rollup("People","Age","max")
Expected behaviour
Both columns should show Bob’s age (42)
Observed behaviour
While rollup() works as expected, max() only shows the first person’s age. Always!
Additional information
I want to be very careful here, but after some more research, it looks as if there is a fundamental bug in the basic treatment of numeric lists in linked tables and formulas. I came across this first in suggestion 2 of this feature request, but as shown above, neither do any of functions work as expected, nor does a simple {People.Age}.
Whenever a linked list of numeric values is concerned, only the first value is evaluated. Only exception is rollup(), which works as expected with strings as well as numbers.
Hi, thanks for the explanation. Did you link all three records in one cell? The rollup only finds the max value from the linked records. If so, would you please provide some screenshots of your both tables. Thanks!
Thanks for your screenshots. I understand now.
It’s not a bug, because the link formula and the rollup function already can give you the correct answer, the max() function doesn’t really support such a comparison in formula columns.
Thanks for the reply! However, I don’t quite understand what you are trying to tell me. Maybe I’ll put it this way: If the max() function doesn’t support this, what is its intended purpose? Sorry that I keep asking, but maybe an additional answer will help others with a similar misunderstanding, too.
Especially since the functions are working for all other types of data, only not for numbers. And because there’s a similar (supposedly inconsistent) behaviour when simply referring to a multi-value number field with {name} (formula in the column header, only curly brackets replaced by “()”)
Thanks for the analysis and sorry for the confusion! Let me try to put things back in order. (I hope I succeed, if I don’t, please follow up.)
The formula column is designed to work with columns in the same table (see example below):
The formula column is NOT intended to work on columns in associated tables. (Some functions may work with the not-notation as you came to notice. But they need not. This should be improved. I agree.)
The link formula column does not let you down whenever you want to lookup/evaluate values in associated records.
Thanks for clarifying this! So close, yet so far away The link formula column does it, but needs another colum if I want to combine or transform data with a formula, right?
Final question: rollup(), however, is designed to work across tables, as stated before? In this case, I’m all set, and just need to avoid the normal functions when going across table links.
If the answer to this question is yes, I suggest clarifying this in the manual’s formula reference: The introduction reads as if all functions work across tables. An explicit distinction would be helpful for idiots like me.
The link formula column does it, but needs another colum if I want to combine or transform data with a formula, right?