If I have a lookup field for a date, it loses its data type as a “date” and I cannot create a timeline block or place events on a calendar. The only solution seems to be making a new column with the data type of “date” and copy/pasting the lookup field info over… manually, every time there is a change or new entry.
Lookup fields lose data type
Lookup fields return arrays — even if there’s only a single element to the array. Usually Airtable manages to suss out the field type and recognizes it as a date… but not always.¹ Instead of a lookup field, try defining it as a rollup field, one that follows the same link and references the same field in the linked record, but use the following aggregation function:
DATETIME_PARSE(values&'')
That should work — but if it doesn’t, you may need to add a format specifier. First, determine in what format the referenced date is being passed by using the aggregation function
values&''
Then, modify the first ag function to provide an explicit format specifier, using the codes given here. For example, you might use
DATETIME_PARSE(
values&'',
'MM-DD-YYYY'
)
- Hmmm… it just occurred to me this may be a function of whether the linked-record field is set to allow links to multiple records; I’ll have to test that hypothesis some time
Lookup fields return arrays — even if there’s only a single element to the array. Usually Airtable manages to suss out the field type and recognizes it as a date… but not always.¹ Instead of a lookup field, try defining it as a rollup field, one that follows the same link and references the same field in the linked record, but use the following aggregation function:
DATETIME_PARSE(values&'')
That should work — but if it doesn’t, you may need to add a format specifier. First, determine in what format the referenced date is being passed by using the aggregation function
values&''
Then, modify the first ag function to provide an explicit format specifier, using the codes given here. For example, you might use
DATETIME_PARSE(
values&'',
'MM-DD-YYYY'
)
- Hmmm… it just occurred to me this may be a function of whether the linked-record field is set to allow links to multiple records; I’ll have to test that hypothesis some time
That had not occurred to me, but I just made sure that the linked fields were set to only allow one record. No luck.
That had not occurred to me, but I just made sure that the linked fields were set to only allow one record. No luck.
Yeah, I’m still not sure why Airtable can sometimes identify a looked-up date and sometimes not. AFAIK, though, DATETIME_PARSE(values&'') always works — although, conceivably, it might hiccup on certain browser/OS locale settings. If so, DATETIME_PARSE(values&'',[FormatSpecifier]) should take care of it.
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.