Skip to main content
Solved

Having trouble with dates in conditional rollup field

  • January 7, 2020
  • 1 reply
  • 5 views
V

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.

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.

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.

Best answer by Justin_Barrett

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)
View original
    Did this topic help you find an answer to your question?

    1 reply

    Justin_Barrett
    Forum|alt.badge.img+20

    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)
    V
    A
    2 people like this
    • V
      Victoria_Newbor
    • A
      ajp-jon

    Reply


    Most helpful members this week

    Terms & Conditions