CRM Templates and Formula for tracking company change

Tim Starkey

Hello all,

Two part question:

What success have you had using the Smartsheet CRM templates? We have decent adoption with Smartsheet, and no adoption with salesforce, so are looking to do our CRM in Smartsheet and leverage the data we already have (client info, contracts, etc)

Second, I am looking for a way (formula, automation) to keep track of a contacts's company history. We work with homebuilders a lot, and they often move from one builder to another. Its good to know how each is related (former firm, new firm, who worked together in the past, etc). I am thinking along the lines of a trigger so that when a contact's company info is updated, their prior company name only is added to a multi drop down list cell.

Column "company" would hold the current company name. Column "Former Companies" would be a multi drop down list of their prior firms. Updating "company" would trigger it to update the former companies. I will probably need a helper column to 'hold' the name of the current firm since once "company" is updated the trigger needs to look someplace for the info that was just changed. Is this possible with stock Smartsheet formulas? Or would I need an API that simply pulled the "company" cell history?

Thanks!

Tim

Andrée Starå

Hi @Tim Starkey

I hope you're well and safe!

I've developed a couple of CRM solutions for clients. The latest one was the most advanced; in short, we have a Company sheet, Contacts sheet, Opportunities sheet, and more. Combined with forms, Data Shuttle and DataMesh.

So, yes, it's possible to do what you need, but yours may not need to be as advanced.

You could store the history in a so-called log sheet.

Make sense?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Kleerfyre

How often is the company updated and who is making the updates? It might end up needing to be a process that you get your users to do. There isn't really a way to copy a cell's data that was there before the change occurred. You could use a checkbox to mark that the row is going to change, but that is more work than just having the user copy the current company name into the former company name and then entering in the new company name.

Andrée Starå

Hi @Tim Starkey

I hope you're well and safe!

I've developed a couple of CRM solutions for clients. The latest one was the most advanced; in short, we have a Company sheet, Contacts sheet, Opportunities sheet, and more. Combined with forms, Data Shuttle and DataMesh.

So, yes, it's possible to do what you need, but yours may not need to be as advanced.

You could store the history in a so-called log sheet.

Make sense?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tim Starkey

@Kleerfyre - not often. We may update the contact list for firm changes 8 or 9 times a year... maybe more. Just enough that it will add up over the years, but not often enough that anyone will remember to do it. And if its only done 'halfway', might as well skip it. This isn't mission critical... more of a "huh... wonder if" kind of thing.

@Andrée Starå - log sheet... I can see something along those lines. Having a helper cell that holds the company name (duplicate of company name until an update). Order of the automation would be critical. User updates company associated with contact. Automation 1 copies entire contact row to another sheet. Automation 2 copies current company into 'former company helper', so its ready for next time. Low priority for me, but something to think about. I am more interested in the CRM structure as a whole. As I get into this, I may reach out to you for your assistance.

Thanks!