Sort by collapsed fields

[JBJ]

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

[Milan_Automable]

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]

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