Roll up most recent date that isn’t in the future

[Rob_Bowman]

I’m relatively new to airtable so thanks in advance for any help.

I want to show he equivalent of the ‘last contact’ rollup that is on the Sales CRM but exclude dates that might exist in the future.

I got as far as creating a field on the same table as the dates I’m interested in which filters out future dates entirely but rolling this up to show a max value doesn’t seem to work (because it is a formula field not a date field?)

So I think the answer to this might lie in rollup that uses an aggregate formula rather than function but I am looking for pointers on what that would look like.

thanks

[David_Skinner]

The MAX(values) formula in the rollup field worked for me when I tried to do this.

This is the formula I used in the field to filter out future dates:

IF(Date>TODAY(),BLANK(),Date)

Using BLANK() in this formula is crucial.

[Rob_Bowman]

Excellent.

So the thing that appeared to be interfering with this was the use of DATETIME_FORMAT in the filtering field to strip out the time that is in the raw date field. Now that I have removed that formatting, the roll up does work correctly.

Thanks again for the solution.

[Adam_Dexter1]

I deleted my last post because I figured this out. To help future folks looking for the answer and to provide more detail- here is what I did.

Using a setup very similar to the Sales CRM demo -> meaning that

1. I have an “Interactions” Table that logs touch points with contacts by date and time
2. I have a “Contacts” Table that attempts to summarize activity with the Contact.

On the the Interactions Table- I added two more Function columns “Past Dates” and “Future Dates”
Past Dates has the formula: IF({Meeting Date/Time}<NOW(),{Meeting Date/Time},BLANK())
Future Dates has the formula: IF({Meeting Date/Time}>NOW(),{Meeting Date/Time},BLANK())

Then on the Contacts Table I created two Rollup Columns:
Last Contact – which rolled up “Past Dates” and used the MAX(values) formula
Next Meeting – which rolled up the “Future Dates” and used the MIN(values) formula

I tried to get fancy and make a one-stop summary in a new Formula column with this formula:

IF({Last Contact},“Last Contact: " & DATETIME_FORMAT(SET_TIMEZONE({Last Contact}, ‘America/Los_Angeles’),‘M/DD/YYYY h:mm’),BLANK()) & “\n” & IF({Next Meeting},” Next Meeting: " & DATETIME_FORMAT(SET_TIMEZONE({Next Session}, ‘America/Los_Angeles’),‘M/DD/YYYY h:mm’),BLANK())

However, since Airtable doesn’t support line breaks or new lines, this just overflows the cell and isn’t very useful. Because it also doesn’t wrap in Card/Gallery view either… :frowning:

And Thanks @David_Skinner for the heads up on BLANK()

[Adam_Dexter1]

Also - as a bonus, here is a formula I wrote for a Column on the Contacts Table called: Time Since Last Contact which will output human friendly “pretty” elapsed time since last contact.

IF(
DATETIME_DIFF(NOW(), {Last Contact}, ‘s’) > 0,
(IF(
DATETIME_DIFF(NOW(), {Last Contact}, ‘h’) > 24,
(DATETIME_DIFF(NOW(), {Last Contact}, ‘d’)&" days ago"),
(IF(DATETIME_DIFF(NOW(), {Last Contact}, ‘h’) < 1,
(DATETIME_DIFF(NOW(), {Last Contact}, ‘m’)&" minutes ago"),
(DATETIME_DIFF(NOW(), {Last Contact}, ‘h’)&" hours ago")
)
)
)
),
“No Logged Contact”)

[W_Vann_Hall]

Adam_Dexter1:

However, since Airtable doesn’t support line breaks or new lines, this just overflows the cell and isn’t very useful. Because it also doesn’t wrap in Card/Gallery view either…

Airtable does support the newline character in grid view with line-height set to anything other than ‘short’. (As I recall, 'extra-tall gets you 6 lines of text – but that may be browser- and OS-dependent.)

It also supports newline in gallery and kanban views, but only for longtext fields, and only for 4 lines. You can copy-and-paste from the formula field to the long text field (either cell-by-cell or the entire column at one fell swoop). You can also configure Zapier to make the copy – and since the trigger and the action take place in the same record, it’s considered a two-stage Zap and can be run from a free account. I do just that in my Wardrobe Manager base for just that very reason: To create a multi-line field for gallery display. There’s a step-by-step guide to setting up the Zap in the [Documentation] table of Wardrobe Manager — or, at least, a step-by-step guide as of the time the base was published; I won’t swear there’ve been no changes to Zapier’s process flow since then.

Also, the Page Designer Block supports newlines, so you can use your multi-line formula field in that block with no problem.