Counting the Days of Your Records
In my preparation for the webinar on calculated fields and dependent fields, I hit a minor A-HA moment whereas I came up with a unique solution to an issue I think some of us often face in our CRMs. And even though I announced this blog post back on the 23rd of January and spent way too long getting this up, at least you will now be able to track how many days out I was tardy in your SugarCRM instance!
So file this one under the “how to get cool data metrics out of your SugarCRM”. Here’s the business case. Let’s say we have a process, like a sales stage or a lead cycle, wouldn’t it be awesome to understand how many days records are spending in each stage? If, in your lead processing cycle, you noticed a bottleneck happening, wouldn’t you want to see what’s going on? Or, what if you are expecting a record to be in a certain stage for a certain amount of time, wouldn’t you want to know when something is getting left behind? This combination of calculated fields, custom fields, workflows, and reporting will allow you to do just that.
So here are the ingredients we are going to need to concoct a basic example of getting a “days in stage” metric:
- A drop down to drive the process
- A custom date field for each stage
- A custom integer field for the number of days in each stage
- A workflow for each status In my example,
I want to track the number of days for a lead in each of its stages. I will be using the out of the box drop-down field of “Status” in the leads module. For brevity of a blog post, I’m only going to show three stages, but you’ll get the picture to understand how to do any or all of what you want to track. We’re going to check the days a lead was in the “New” stage as well as “Assigned” and “In Process”.
The first step will be to create a custom date and custom integer field in the Studio management tool found in the Administration panel for the leads module. The field I am using for the Assigned stage date looks like this:
Please note that I did set the Enable Range Search checkbox because now I’ll be able to set criteria for reports based on date range if I ever want to see what leads were assigned in Q1, etc. The custom integer fields associated with this looks like this:
It’s a pretty simple field for the integer except for one property entry. I have the calculated value checked to true and have the following sugar logic in the formula:
What we are doing here is using a few functions to grab the number of days between two dates. The “Days Until” function will tell us the number of days between today and the field we use as a parameter. Because this is a date in the past, I’m multiplying this number by negative 1 to get a positive number. Lastly, I’m using the “subtract” function to get the difference between the two dates. For the very first stage, I’m using the “Date Created” field to be my baseline. For the layouts, I only need to expose the integer “Days” fields and I don’t have to put the date custom fields in the layouts if I don’t want to. Lastly, we need to touch the workflow manager also found in the administration panel. You will need to create a workflow for each time a level of the status changes. When the status gets set to assigned here is the workflow that I created:
The criteria for that workflow looks like this:
I don’t have have to set any alerts unless I want someone notified that the lead status has been changed to “Assigned”. I do have to create an action item for our workflow. What we need to do is timestamp the date that this status changed by putting today into the custom date field that we created for the status. Mine looks like this:
That’s it. Now, anytime I change a status, the date will get stamped in the appropriate box and our calculated field will figure out the number of days we had this record in that stage. I now have the ability to run a report based on these number of days. Want to see the average amount of time a lead record was in Assigned to stage? Then just use the Summation Report. Want to see that by each sales rep? Use the Matrix report. Want an alert to notify you if a record was in the new stage for over a week? Set up a new workflow with an alert.
Here is what my detail view of my lead now looks like:
This is a really basic example and assumes all stages must be used. If you need to skip around, you will probably need to use the “ifElse” function in the Sugar logic of the calculated field, but this should give the basics to get started. If you have questions about setting up calculated fields or workflows in Sugar, contact us.