Many people use workflows in Microsoft Dynamics CRM to trigger and send record-based notifications—for example, notifying sales representatives when a new account is assigned to them, or notifying a customer when a case is closed. But sometimes you don’t want a single-record notification—you want a cumulative notification when a certain threshold is reached.
Let’s say an investment manager wants to get alerted if a client calls in more than two times during a month to question portfolio performance so she can call the client and save the relationship before it reaches a critical state, and the firm uses Microsoft Dynamics CRM cases to track client issues.
Workflow alone will not satisfy this requirement—standard workflow can be triggered by any single record event, such as creation, deletion, assignment, or update of any field on the record or a child record. However, if you want to do a notification based on multiple records, such as X number of records created during a specific time period, you will need some mechanism to rollup or aggregate the records, then trigger the notification.
In CRM version 4 or earlier, you would need a fair amount of customization to handle this requirement, such as a custom plugin. However, CRM 2011 now includes a built in rollup mechanism—goals.
The goal functionality is most commonly used for sales goals or other performance metrics, but the unique capability to rollup a count of any type of record for any timeframe makes it a very useful feature for other unconventional requirements.
So, getting back to our example, I want to receive a notification if we receive more than 3 cases with a specific type of issue this quarter. First, I’m going to create a goal to handle the count of cases.
1. Create the number of cases metric.
I want the metric to be a “Count” metric, and I call the metric “No. of Cases.” The goal metric link is typically located in the Sales area of Microsoft Dynamics CRM.
2. Add the rollup field
From the Metric record, select the rollup field grid and click “add new rollup field.”
Select “Actual” for the Rollup Field, Case for the record type. I didn’t select a state or status, because I want to count all cases created, not just a specific status. For date field, select “Created on,” because we want to get a count of all cases created during the goal’s time period.
3. Create the goal
Link the “No. of Cases” metric created in step 1 to the goal in the “Goal Metric” lookup field, then select the desired time period in the “time period” section of the goal form. Set the target to the threshold level at which you wish to be alerted. In our example, I select 3, since I want to be alerted if there are 3 or more cases during the goal period.
Scroll down to the “Goal Criteria” section of the goal form. Set “Record Set for Rollup” to “All.”
Note—this is probably the most common reason why goals don’t rollup as expected—this field defaults to “owned by goal owner,” which will just count records owned by the user who owns the goal. In our example, we changed it to “all,” because we want to count all cases created by all users during the goal time period.
4. Create the Rollup Query
So far we have created a metric which counts the cases created, and a goal which defines the time period during which to count the cases, but we need to do one final thing—we need to create a filter that will filter the rollup to just the type of cases we want to count. In our example, cases, where the priority is “high.”
From the goal record, click the lookup button for “rollup query.” From the lookup dialog, select “new” to create a new rollup filter.
The rollup query allows you to create an advanced find filter that will filter the rollup to a subset of records—in our example, priority = ”High.” Remember, you don’t need to specify date ranges or record statuses in rollup queries—those are specified by he goal and goal metric records.
This also means that if you want to have several goals based on the number of cases, you only need to create one metric, number of cases, and you can associate it with multiple goals with different goal time periods or rollup query filters. This makes goal management easy, as you can reuse all components except for the goal header.
Now that your goal is complete, CRM will automatically calculate the number of cases that meet the criteria that you have specified. This rollup happens by default every 24 hours. You can force the rollup to happen by selecting the goal and clicking the “Recalculate” button in the ribbon.
Now the Goal record will show the total cases during the time period that meet the criteria, as well as the percentage of the target number achieved.
Creating the notification workflow
Now that the goal is created, we can create the cumulative notification workflow. Create a new workflow, selecting “Goal” as the entity.
Set the workflow to run upon update of the goal Percentage Achieved field.
Then add a “Check condition” step, with the following two conditions:
- Goal equals name of goal
- Percentage Achieved is greater than or equal to 100
Then add a send e-mail substep to send the notification e-mail to the appropriate person, and publish your workflow.
When high priority cases are created and the goal rollup recalculates the number of cases that meet the criteria for the current quarter, a workflow will notify the appropriate parties when the specified threshold is met or exceeded.
This is just one of the many unexpected uses for the goal rollup functionality in CRM, and it can be considered for just about any requirement for an automatically recalculating rollup.
Post by: Joel Lindstrom,
