Has a client ever asked you to show records that haven’t had any activity? Microsoft CRM’s list of available filter criteria for views is fairly extensive, but this is the one frustrating gap that functional consultants encounter with almost every client.
With my last client, a co-worker and I were determined to find a solution. Our internet research returned very little and our developers told us it wasn’t possible. Below is a list of steps that we took to resolve this issue.
- Create a custom date field on the appropriate entity, ensure that the field is visible for the time being
- Create a workflow for each activity entity to update the custom date field created in step one
- Once you have created your workflow, ensure that you update the options for automatic processes:
- Scope = organization
- Record is created = checked
- Record status changes = checked
- Once you have created your workflow, ensure that you update the options for automatic processes:
- Record fields change: For our purposes I selected Modified On, Status & Status Reason
- Next validate your record through the check statement tool. We decided to validate that the regarding field was set to our custom entity, and that the record was the correct type.
- If the regarding field was set to our custom entity and the type was correct, we wanted the custom field called “Last Activity Date” that we created in step one.
- Set properties: We selected the original start date for the appointment activity to update the last activity date on our related entity.
- If the regarding field is not for our custom entity, then end the workflow.
- Activate your workflow and test that when you create the activity record and set the regarding field to your entity, the field you created in step one is updated.
- Repeat steps two and three for each type of activity.
- The next step is to create a view for your entity and filter on the custom field created in step one (Last Activity Date).
- For our purposes, we needed to filter the type of record and show those that didn’t have any activity in the last month.
Please note: there is a limitation on the filter values that you can select; CRM only lets you use a criteria of ‘Older Than X Months’. This means you are unable to show records with no activity in the last X days or X weeks
- Go back to the appropriate entity and hide the custom date field.
- Then go to your entity in CRM, and change the view to the one you just created. It should now display records that haven’t had any activity.
By Emma Shields, Microsoft Dynamics CRM Consultant, BDO Solutions.
BDO Solutions is a , including Ontario, Alberta, British Columbia, Manitoba and Saskatchewan. Microsoft Gold Certified Partner and Reseller of the Year in Canada for 2010, 2011 and 2012.
