Jul 03, 2018 09:07 AM
When a field using FROMNOW() is sorted from A-Z, the sort appears to be based on the first number of the output, rather than the time. Example:
Days should be considered smaller than months, which should be smaller than years. Output should be read the same as DATETIME_DIFF():
Secondary issue: FROMNOW shows deadlines a day earlier than the actual deadline (see difference in images above)
Jul 03, 2018 11:16 AM
I think if you have
A-Z option to sort, that is a string and not a time/date/number. So what you get seems normal to me.
Jul 03, 2018 01:45 PM
A-Z is the only option for the output of FROMNOW(). Yet FROMNOW() is categorized as a Date/Time Function, and the description in the Formula field reference says “Calculates the number of days between the current date and another date.”
Doesn’t it seem more normal that I would want to put things in order of when they’re next due?
Jul 03, 2018 02:58 PM
You could find part of an anwser to this here :
I encountered a similar problem with
It won’t resolve a thing, but it was very helpful to try to understand why… :grinning_face_with_sweat:
Concerning the bug,
FROMNOW() simply seems to be a « friendly user format » field in opposition to
Jul 03, 2018 03:41 PM
I just comment on what I see. Maybe the documentation should specify that the return value is a string, with a correspondant
' days' suffix.
You can make it a number just with
INT(SUBSTITUTE(Days, ' days', '')), and you could order by number, on top of using
Jul 03, 2018 04:27 PM
@Elias_Gomez_Sainz has it right: If the result of a formula field is, in part, a string, the entire thing is a string — and the string
'5 years' is less than the string
'6 days'. That the only available sort options are
'Z-A' is a dead giveaway the result is a string.
And Date/Time Functions return dates, numbers, or strings, depending on the function.
Frankly, I’d suspect it is a rare to-do list that commingles items due in days and years.
One possibility would be to create a hidden field with
FROMNOW(), and group by bins of days:
< 30 (or is it
< 7; does
FROMNOW() ever return in weeks?),
< 365, and