Slider Arrow Icon
Back to SmartSuite blog
Last Contacted Date (and Other Date Rollups) in SmartSuite: A Comprehensive Guide

Last Contacted Date (and Other Date Rollups) in SmartSuite: A Comprehensive Guide

10 minutes

March 20, 2023

If you've been in a dilemma about keeping track of the last time you touched base with a prospect, when a particular ingredient in your product line expires, or simply need to figure out key dates for your business operations, you're going to love what I have in store for you today.

The Relevance of Date Calculations

Believe it or not, figuring out the most recent or the earliest dates is a big deal for many businesses. This weekend, I mingled with some folks from the SmartSuite community who were puzzled over managing expiry dates of ingredients in their candy bars. This got me thinking about how widespread the need for this date tracking challenge really is. Whether you're in sales, supply chain, production, or customer service, pinning down those dates is crucial.

SmartSuite at Work: Behind the Scenes with Contacts and Interactions

So, let's dive in and see how SmartSuite can play the hero in these scenarios. I'm going to show you a simple setup with just two tables - or as they're called in SmartSuite, two apps. We have a "Contacts" table and another for "Interactions" with those contacts.

Think of interactions as emails, phone calls, or any contact point with your prospects. It's important to keep track of these because, let's face it, who has the memory of an elephant to remember the last conversation date with each contact, right?

Now, to keep it straightforward, our interactions table only has a couple of fields - a status and a due date. And of course, each interaction is linked back to a contact.

Cracking the Code: Finding the Most Recent Interaction

Here's where we encounter a little hiccup. Normally, you might think of using a roll-up field to automatically scoop up the latest interaction date. Butroll-ups in many systems, including SmartSuite sometimes, aren't equipped to handle dates seamlessly. They're more comfortable crunching numbers or dealing with currency.

No sweat, though. We can bring formulas into play, fulfilling the same purpose as a roll-up, but with more flexibility. Let's get our hands dirty and set up a 'Last Contacted' formula field.

`Last Contacted`

In the advanced editor, we'll use a simple aggregation formula. Since we're on the hunt for the most recent date, we'll employ the max function. Had we been looking at expiration dates for ingredients, we'd use the function instead.

The setup looks something like this:

MAX({Link to Interactions}.Due Date)

We're using MAX to grab the date that's closest to the present from our linked interactions. And voila! SmartSuite picks up the latest date - let's say March 15 - as our most recent point of contact.

Leveraging Formulas: The Road to Smart Automation

Now, the real magic happens when we mix formulas. Wondering how many days it has been since you last made contact? Let's use another formula for that, calling it 'Days From Today'.

`Days From Today`= DATEDIFF({Last Contacted}, NOW(), 'days')

This tells us exactly how many days have passed since the last interaction. If it's been, say, four days, you might decide it's time for a follow-up.

Pushing the Boundaries: Automations and Beyond

Thinking ahead, imagine combining these date rollups with SmartSuite's automation features. You could set up an automation that creates a follow-up task if a certain number of days have lapsed since the last contact, ensuring no prospect slips through the cracks.

The possibilities are endless. Integrating formula fields with automations unlocks a whole new level of efficiency and ensures that you're always on top of your game.

Wrapping Up and How to Reach Out

I hope this tutorial has shed some light on how SmartSuite can tackle the ubiquitous challenge of managing dates in your business operations.

Until next time, keep automating and stay productive!

To experience SmartSuite's key benefits for yourself, start a free trial today and explore its intuitive work management platform.