Stuck trying to implement a personal CRM

[Kevin_Butler]

Hi, I’m evaluating using Airtable and decided to try implementing a personal CRM in the process. I’m having a few issues and appreciate any help.

First, I currently have two tables: Contacts and Interactions, with a one-to-many cardinality. For instance, in Contacts I might have John Doe and in Interactions I have an email conversation and phone conversation.

My goal is to be able to see all my contacts and only the most recent interaction. That way I can quickly see what happened last with everyone. However, I can’t figure out how to have lookups or rollups in the Contacts table that only bring in info about the most recent interaction.

If I were doing this in some other way, I’d use SQL to join the two tables, group by contact, sort by date, and keep only the last for each group. Any thoughts on how I do this in Airtable?

Another issue I have is that when I do have a lookup that is a single select from another table, it loses all of the formatting from the other table (it’s no longer in a colored pill). Is there a way to get this to flow through?

Thanks!

[Aron]

Hi Kevin,

These are great questions – appreciate the clarity!

My goal is to be able to see all my contacts and only the most recent interaction

Okay this takes a little bit of work and may not be the best solution but I’m pretty proud of it: essentially we’re going to check at the contact level the latest conversation date (rollup max dates of conversation). Then lookup that information in the contacts table to create a variable that indicates whether it’s the most recent conversation.

Then we do a conditional lookup on that field so we pull in the most recent conversation next to each contact.

You can see my solution here

The single select formatting is a known issue! Nothing to do there for now.

Again, not sure if this is the best solution but definitely works! Let me know if you adopt this solution, I’ll mark the thread closed

Best
Aron

Want to learn Airtable? Join me for a webinar at airtable.com/webinar