Whether reporting through the Advanced Find or through the Report Wizard in Microsoft Dynamics CRM, one of the greatest challenges is getting information related to the Contact to show on the report. Out of the box, Activities have several ways of being “tracked” against a record in CRM. The best one for reporting is the “Regarding” field.
For this reason, if you intend to do reports on activities that show related data to the Contact then you MUST train users to ALWAYS set the Regarding on their activities to the Contact. This includes when you are tracking Emails from Outlook. In order to get consistent reports on activities created from Outlook, everyone must set the Regarding (not just Track in CRM) or the Regarding field will not be filled out. Below is an example of the coveted Advanced Find list of phone calls with additional information about the Regarding Contact highlighted in yellow.
The problem with this policy of having users track all activities against the Contact is that it goes against CRM best practices. If you are working on an Opportunity, best practices are to track your phone calls and activities against the Opportunity. If you are working on a Case, then you want all activities to be regarding the Case. Likewise, if you are working on something built as a custom entity, such as a Project. There is no other way to associate activities with a specific Opportunity, Case, or Project without setting the Regarding field to that record.
Many people ask, “Can you set the Regarding to multiple records?” The answer is no. The regarding can only be set to one record. For example, an activity cannot be connected to Contact and an Opportunity through the Regarding field. If you could, it would make reporting on the Regarding field even harder because of the possible one-to-many relationship.
However, with the exception of Tasks, all Activity Types have another field for indicating the Contact.
- Phone Calls
The Recipient for outgoing phone calls and Sender for incoming phone calls indicate the contact associated. This field is *special*, meaning it can contain a Lead, Contact, Account, or User.
The From and To fields can indicate the contact associated with the account. Not only can these fields contain Leads, Accounts, Contacts or users, they can also contain multiples of those, as well as unresolved Email addresses (Email addresses that are not found in CRM, such as when an Email is tracked in CRM from Outlook).
The Required and Optional fields on the Appointment indicate the contact associated with the Appointment. Not only can these fields contain Leads, Accounts, Facility/Equipment, Contacts or users—they can also contain multiples of those, as well as unresolved Email addresses (Email addresses that are not found in CRM, such as when an Appointment is tracked in CRM from Outlook.)
- Campaign Responses
The Customer field (effectively the Regarding field) on the Campaign response shows the relationship to the Contact. This field is also a *special* field allowing a Lead, Account, or Contact to populate the field.
Because of these fields, you CAN set the Regarding on the Activity to the Opportunity, Case, or Project AND also have it associated with the Contact. So, it sounds like you can get your cake and eat it too!?
The problem comes when you try to report on information related to the Contact through one of these fields (Recipient, Sender, From, To, Required, Optional, or Customer). Due to the complexities behind the database relationships of these *special* fields on each of the activities, there is no way to report using Advanced Find or Report Wizard on any information related to the Contact associated with the Activity. You can query those fields as part of the Advanced Find, but you cannot show Contact fields as additional columns in your report. We are back as square one with the fact that the only direct relationship to the Contact is to set Regarding to the Contact.
At PowerObjects we have overcome this hurdle with a little bit of custom development on top of MSCRM. To each activity type, we added a new direct relationship to the Company (Account), Contact, and Lead. When one of the potential contact fields (Recipient, Sender, From, To, Required, Optional, or Customer) on an activity is updated, our plug-in cycles through the contents of the field and looks for the first valid Lead, Contact, or Account. It then fills in the Related Record fields with a direct relationship to that record.
Since these new lookups are direct relationships to the Contact, you can then Report through those relationships and display fields related to the Contact on an Advanced Find query or a Campaign Response. This solution is expected to work in 99% scenario where you are most often communicating with just one person. The solution does break down when there is an activity that is associated with many contacts. This will typically only happen with an Email to multiple people, but may also be an Appointment with multiple contacts in the Required or Optional fields. But for example, if I send an Email to two contacts at a customer through Outlook and track it in CRM, Microsoft CRM only creates one record for that Email. Therefore, the Related Record fields (shown above) will only be filled out by the plug-in with the FIRST contact in the To field.
An added bonus with this solution is that you can also do workflows using these Related Record fields. The ability to do workflows on Activities is limited by the same complex relationships on the Recipient, Sender, From, To, Required, Optional, and Customer fields discussed above. For example, you can’t have a workflow that sets the Recipient of a follow-up Phone Call based the Regarding field on an E-mail. This is because the Regarding can be a lookup to ANY entity in CRM, whereas the Recipient is a different type of lookup—it only to an Lead, Account, Contact or user. However, with the solution PowerObjects has developed you can fill out the Recipient field with Related Record lookup.
This PowerObjects solution really takes the cake AND let’s you eat it too!