In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
When using Microsoft Dynamic CRM, sometimes it's necessary to perform certain operations based on Aging. A very common scenario would be in the handling of customer service tickets. For example, you might want notifications or dashboard charts or views to be created that shows aged tickets.
While CRM does provide a long list of time options out-of-the-box, it does not provide very precise time calculation options. For example, while we can look for records created in the last X hours, we can't specify conditions to look for records created in last 20 minutes, between 12 hours and 24 hours, or other odd combinations like these.
However, you can achieve this using workflows and a few custom fields. Let's say, for example, that you would like to age your cases based on how long a case has been open and track it for less than 12 hours, 12 to 24 hours, 24 to 48 hours, 48 to 72 hours and over 72 hours. You can do this by following the steps below:
1. Create a custom field on the case to mark where the aging is set. This would be an option set with the following values: 12, 24, 48, 72. After this, you'll use a workflow to set the option set based on which hour mark you have crossed.
Note: Optionally, you can also add Date and Time Fields to track the exact time the ticket crosses over the different time slots. The following screenshot shows the ticket age and the 4 corresponding fields.
2. Now create a workflow that triggers on the creation of a ticket.
3. Set up the workflow steps as follows:
a. The first is a condition that checks to see if the ticket being created does not have a status of Closed.
b. Under this condition, you want to timeout the workflow to wait for 12 hours.
c. Once 12 hours is up, it will update the ticket age on the ticket to 12 hours.
d. Then wait again, but wait only for 12 hours more, in order to set the 24 hour age.
This will keep incrementing the ticket age from 12 – 24 – 48 – 72 as time goes by. But there is a small flaw here… can you guess what it is? The workflow will keep on running and waiting from the moment it starts until it sets 72 hours on the ticket, even if the ticket has been closed on the 6th hour or 20th hour or 50th hour. Let's see how we can make it bit more streamlined.
b. Add the same check condition to the other wait conditions, and you are done!
Once you have the aging set on the tickets, the ticket age field can be used to create out of box work views or queues, charts and quick dashboards. Visit PowerObjects blog for more details on using some of the time operators with workflow or using wait conditions with workflows.