Skip to main content
–– 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.
 
 
 
 
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.
 
 

 

 
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
 
  
 

 

 
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.

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

 

@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.

Reply


Terms & Conditions