Today, I continue to answer the question ‘what was the best way to use a workflow to send an email when a case had not been modified in 5 days?’ by discussing a potential solution using Calculated and Rollup Fields!
We are talking about potential solutions for dealing with Cases that have been sitting in the system but not worked on for several days.
To get a better understanding of our cases we are going to create two special fields.
- Calculated field on the number of days since the case was modified
- Rollup field for the Date of Last Activity
Calculated Field
We will do this in a solution so it is easy to share with your friends. Include the Case entity or your custom ticketing entity if that’s how you roll.
Then we will create a new field as Calculated using the DiffInDays function to determine how long since this case was updated as shown below.
Rollup Field
Similarly we can create a Rollup Field so look at this problem a little differently. In this example, I am going to get the date of the most recent completed activity. When is the last time you actually talked with this person about the issue.
Add to the Form
Finally we can add these fields to the form and see if there are any fixes needed. Look at a few test records and ensure this is working as expected. Correct, beautify and try again.
Is this a complete solution?
This is not a full solution to the problem. We wanted some kind of notification to the owner that they need to follow up. This solution will only provide visibility but not proactive notifications.
We can add these details to a chart, view and/or dashboard to put this in front of the user and possibly call out the oldest items. However, this can still be ignored.
The big issue with Calculated and Rollup fields in my opinion is that you cannot use these to trigger workflows or other automation. I now have a nice field that tells me how old this record is but I cannot use that to send the email.
Next time, we’ll look at using Charts, Views, and Dashboards. Be sure to check out the other solutions in this series and let me know in the comments below what you think of this solution!
Hi Kylie
Just read your blog via the community site and thought I would let you know I have a complete solution for this within a managed solution I created
Basically I have a managed solution I created which periodically (e.g. hourly, daily, weekly) polls a view for records and if there are results in that view sends list of records to each of the owners of records in the results
So for you problem you could create a view of active cases which have a modified date greater than (or less than?) 5 days in the past then configure to poll this view daily and send notifications
The managed solution can be downloaded here and the repository also contains all the code open source https://github.com/josephmcmac/JosephM.Xrm.WorkflowScheduler/releases/latest
This article gives brief details of the solution https://josephmcmac.wordpress.com/2017/12/21/dynamics-365-workflow-schedulersolution
The solution is implemented via a workflow which contains a custom workflow activity to poll the configured views. It then repeatedly waits and restarts itself to effectively have a never-ending workflow running on a schedule. Over the years since I initially implemented it I have also added separate monitoring of the processes into the workflows so they are more resilient and generally restart themselves in rare cases where the Dynamics platform has unexpectedly failed the recurring workflows for arbitrary reasons. It has been running very reliably for a good deal of time for me in various instances
Anyway hope this helps as it provides a complete solution for your problem and potentially avoids a great deal of work implementing other solutions
Thanks for sharing! Stay tuned for the next few days to see a few other potential solutions and ideas.