Rollup & Max & Dates & Formulas & Formatting = Loop / Freeze


#1

MAX(values) doesn’t work properly in the scenario where you rollup a date field which uses formatting. It’s no longer recognised as a date and can cause problems.

It also looks like old field level formatting is stored when a datatype changes. I used an existing field that was formatted for currency and changed the formula to simply show a date field. My rollup in the parent record caused a major problem later on although the child field appeared as it should: I was locked out the parent table as it got stuck on the ‘Loading’ screen.

To re-enable access to that table I had to change the formula in the child table.

FYI. :slight_smile:


#2

I think I’m having a similar issue. I’m trying to use MAX() to return the most recent date from two rollup fields, but it doesn’t work as expected.

The formula MAX({Rollup Date 1}, {Rollup Date 2}) returns 0

while the formula ‘DATETIME_PARSE(MAX({Rollup Date 1}, {Rollup Date 2}))’ returns 1/1/1970


#3

Hey folks - I came here hoping to find a solution to this bug that I’m also experiencing. Unfortunately, it looks like one does not yet exist, so I had to build a bit of a workaround. Check it out here - while this certainly isn’t ideal, it solves the problem of finding the latest (or ‘max’) date.

Hope this helps!


#4

Here’s another way to find the latest of three dates — still jumps through too many hoops, but at least has the good grace to do so within a single formula. :wink:

Assumes three date fields, {Date1}, {Date2}, and {Date3}. Returns a date, with formatting specified in the field configuration.

DATETIME_PARSE(
    MAX(
        VALUE(
            DATETIME_FORMAT(Date1,'x')
            ),
        VALUE(
            DATETIME_FORMAT(Date2,'x')
            ),
        VALUE(
            DATETIME_FORMAT(Date3,'x')
            )
        ),
    'x'
    )

Oddly enough, this also seems to work with rollup fields, although I can’t for the life of me understand why. (Even though Airtable tells me the the rolled-up date field — which it displays in ISO-8601 interchange format; e.g., 2018-11-13T00:00:00.000Z — is not recognized as a date, seemingly it is a valid input to DATETIME_FORMAT().)

BTW, the 'x' format specifier causes DATETIME_FORMAT() and DATETIME_PARSE() either to return or to expect a date expressed as a Unix timestamp. I increasingly make use of such in Airtable, as doing so avoids a myriad potential pitfalls owing to mismatched or region-specific formats.