Help

Having trouble with dates in conditional rollup field

Solved
Jump to Solution
1996 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Victoria_Newbor
4 - Data Explorer
4 - Data Explorer

Hi there!

I’m working in two tables: Contacts and Interactions. The latter keeps records of all emails to and from Contacts (linked), including time/date and a single select field to indicate whether the email was inbound or outbound.

I also have simple formula fields – IF(Type=‘To Contact’, Date, ‘0’) and IF(Type=‘From Contact’, Date, ‘0’) – because I’m trying to create respective rollup fields in the Contacts table to show the date of the most recent inbound email and the most recent outbound email.

Interactions Table

I can’t get my rollup to display any dates. MAX(values) displays zeros. Using ARRAYJOIN and then using DATETIME_PARSE(MAX({arrayjoinfield})) displays 1/1/1970.

ContactsTable

Is there anything I can add to the Interactions formulas so that the dates are read as values? Or is there a different function you can recommend for the rollups? I’m really scratching my head here.

1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Victoria_Newborn! :grinning_face_with_big_eyes: I have a hunch that you’re encountering this issue because of the “0” string you’re using as the fallback value in those formulas. Because that fallback value is a string, Airtable assumes that the output in that field should always be a string, so it turns the date into a string in the most simple way it knows. That’s why you can’t use MAX() to find the latest date: because the date is no longer a date, but a string.

To fix this, just drop the “0” string completely, and Airtable will leave the field empty if the condition fails. Not only that, but it will then use the only other option—the date from the {Date} field—to determine that the field formatting should remain as a datetime object, and not be converted into any other format. Your {To Contact Condition} formula would become:

IF(Type='To Contact', Date)

And the formula for {From Contact Condition} would be

IF(Type='From Contact', Date)

See Solution in Thread

1 Reply 1
Justin_Barrett
18 - Pluto
18 - Pluto

Welcome to the community, @Victoria_Newborn! :grinning_face_with_big_eyes: I have a hunch that you’re encountering this issue because of the “0” string you’re using as the fallback value in those formulas. Because that fallback value is a string, Airtable assumes that the output in that field should always be a string, so it turns the date into a string in the most simple way it knows. That’s why you can’t use MAX() to find the latest date: because the date is no longer a date, but a string.

To fix this, just drop the “0” string completely, and Airtable will leave the field empty if the condition fails. Not only that, but it will then use the only other option—the date from the {Date} field—to determine that the field formatting should remain as a datetime object, and not be converted into any other format. Your {To Contact Condition} formula would become:

IF(Type='To Contact', Date)

And the formula for {From Contact Condition} would be

IF(Type='From Contact', Date)