Filtering based on data in a linked table

[Joel]

Hey, I’m creating a sort of CRM.

The first table has Contacts

• Email
• Name
• etc
• Link to associated Correspondence rows

The second table has every Correspondence

• Type (email, meeting, etc)
• Date

I want to create a filter for the Contacts table based on the date of the last correspondence.
i.e. “show me all my contacts who haven’t been contacted in the last month”

Is there a way to do this within the UI or the API?

Thanks!

[Joel]

I’ve tried to create a lookup field with the linked correspondence dates, but the filter I’m looking for—“is not within the past month”—doesn’t exist.

[Alex_Wolfe]

Hmm, I think you can work around this if you create a formula in your Correspondence table that determines if the date is within the last month (or whatever date-related calculation you need). Then you can create a Lookup in your Contacts table for your new ‘within the last month’ formula in your Correspondence table, which you can use to filter your records in your Contacts table.

That make sense? Let me know if that helps!

[Joel]

Hey, this helps, but it still doesn’t let me create the filter I need.

Let’s say I have two Correspondence entries associated with a Contact.
Each of these has a formula attached that returns # of days since last contact.

• Contacted 25 days ago
• Contacted 52 days ago

The Contact then has 25, 52 associated with it.

But now when I try to get only contacts that haven’t been contacted in the last thirty days, say by doing >30 on this linked formula, I still get that contact because it matches 52.

What I need is a way to negate the filter. If there is any correspondence <30, don’t show the row.

Is there any way to do this?

[Joel]

Ended up changing the formula so it returns 1 or 0 for whether the last contact was over 30 days ago, then I remove the 0s with a filter. I wish there was a better way to do this, but thanks for the help!

[Kasra]

You may be able to do this using a rollup field to get the min/max correspondence date: https://support.airtable.com/hc/en-us/articles/202576599-Rollup-field-reference

[Partner_Marketi]

I would do a rollup of the actual dates they were contacted using “min” to create a last contacted field. Then in your contacts view you can create a filter that is “last contacted is not within 30” and use all their built in date range filters. This would give the reps flexibility to raise or lower the threshold of days vs having to adjust that formula in another table.