Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Sorting Bug with Date Field

Topic Labels: Base design
137 2
cancel
Showing results for 
Search instead for 
Did you mean: 
JBJ
7 - App Architect
7 - App Architect
–– Post with many screenshots ––
 
Hi guys,
 
I have a problem with an automation that contains a 'Sort List' module which sorts records depending on a date.
 
CleanShot 2025-02-13 at 15.30.47.png
CleanShot 2025-02-13 at 15.31.39.png
 
 
The record values the module is unable to compare come from a {Start Date} field. This formula field outputs the value coming from one of those three fields: {CALC LinkedIn Conversation Start Date}, or {CALC Email Start Date}, or {RAW Miscellaneous Start Date}. As you can see, the values in the {Start Date} field are not formatted the same way depending on their source field – some values come from a date field, some from lookup fields. This had me hypothesize that the values coming from the two lookup fields must not have a date data type, but must be arrays.
 
CleanShot 2025-02-13 at 15.20.46.png
 
CleanShot 2025-02-13 at 15.22.10.png

 

 
But three things seem to contradict my hypothesis:
1. the two lookup fields have date formatting in their settings
2. when I applied the DATETIME_FORMAT function to those records values in a test field, I got no error
3. when I sort records in a view based on the value in {Start Date}, the sorting is correct
 
CleanShot 2025-02-13 at 15.39.30.pngCleanShot 2025-02-13 at 15.49.56.png
CleanShot 2025-02-13 at 15.49.19.pngCleanShot 2025-02-13 at 15.56.43.png

 
So this leaves me dumbfounded. Which data type do the values that are in {Start Date} and come from lookup fields have? Why does the 'Sort List' module not work?
 
Thanks.
2 Replies 2
Milan_Automable
6 - Interface Innovator
6 - Interface Innovator

Without knowing what causes this:

Wouldn't wrapping your Start Date formula inside a DATETIME_FORMAT, and uniformizing the formats that way, solve this?

Best, Milan - Automable.AI Free Airtable consultation

 

JBJ
7 - App Architect
7 - App Architect

@Milan_Automable I found the cause and solution to my problem. Here it is for possible future readers.

Even though lookups of date fields may look like date fields – 1. they have date formatting in their setting, 2. DATETIME_FORMAT can be applied to them, 3. sorting can be applied to those fields in views –, their values remain essentially arrays. One-item arrays of a date to be precise.

So to convert those arrays to date values, the trick is to use a combination of ARRAYJOIN and DATETIME_PARSE. This time, the sorting by date in the Automations panel will work.