Jul 03, 2019 08:16 PM
I’ve been searching throughout the forums but couldn’t find the answer to this question.
I currently have an “Interactions” table where I capture the date of the interaction and link it to a contact record in the “Contacts” table. I also have a field in the Interactions table where I select the type of interaction e.g. Meeting, Phone Call, Email, Task.
What I would like to do in the “Contacts” table is create a view to capture servicing items. One of those is to capture the last date I held a meeting with a specific contact. Is there a way I could create a field in the “Contacts” table to automatically pull the last date I had a meeting with that contact from the interactions table?
I can easily pull the last interaction date with the contact using a rollup field with the formula Max(Values) but I would like to specify interactions that are of the Meeting type only so it doesn’t pull the other types such as Phone Calls, Emails etc.
Solved! Go to Solution.
Jul 03, 2019 08:43 PM
Make a formula field in [Interactions]
named something like {Mtg Dates}
, using this formula (assuming {Date}
is the existing date field, and {Type}
is your type chooser):
IF(Type="Meeting", Date)
Only meeting dates will appear in that field, which you can then hide and roll up using the MAX(values)
aggregation in your [Contacts]
table. (Note: wrap “Date” in the formula above inside a DATETIME_FORMAT()
function to format it as you choose.)
Jul 03, 2019 08:43 PM
Make a formula field in [Interactions]
named something like {Mtg Dates}
, using this formula (assuming {Date}
is the existing date field, and {Type}
is your type chooser):
IF(Type="Meeting", Date)
Only meeting dates will appear in that field, which you can then hide and roll up using the MAX(values)
aggregation in your [Contacts]
table. (Note: wrap “Date” in the formula above inside a DATETIME_FORMAT()
function to format it as you choose.)
Jul 03, 2019 09:28 PM
That’s awesome. Simple and straight forward. Love it. Thanks for your help.
Jul 04, 2019 07:10 AM
So strangely enough, the formula isn’t working. I double checked the field names and they are correct. I’m using the formula you outlined above, the field names match, but it’s resulting in a blank field/result.
I’m wondering, does it matter if the field TYPE is a single select field instead of a text field?
Jul 04, 2019 07:29 AM
It shouldn’t matter. When querying the value from a single select, Airtable auto-converts it into a single string. My only thought is that if your “Meeting” entry in the single select options has an errant space at the beginning or end, they won’t match (i.e. "Meeting " != “Meeting”). To work around this, you could try this alternate formula (or just remove any spaces you find):
IF(TRIM(Type)="Meeting", Date)
Jul 04, 2019 01:48 PM
I figured it out, the field had these icons/emojis’ before the actual text of the Interaction Type. Once I removed those it all worked out. Thanks again!
Jul 05, 2019 12:22 PM
FWIW, it would still work with the emojis if you included them in the comparison string in your IF() function. For example, if your meeting choice looked like this:
:clock2: Meeting
…then the IF() would look like this:
IF(Type="🕑 Meeting", Date)