Working with Dynamics CRM Rollup Fields and Indirectly Related Activities
Occasionally, a support case comes across my desk that brings me to something new. Recently I had a support case that introduced me to creating rollup fields based on “Indirectly Related Activities.” The customer wanted to track the latest appointment activity date related to an Account. They wanted to include any appointment related to the account, an opportunity related to that account, or a contact related to that account.
Unfortunately, there is some misinformation on how the “Indirectly Related Activities” work for rollup fields. It is understandable because indirectly related activities work differently for some out of the box grids than they do for the rollup field. My client was wondering why the activities that were associated with an account were working for her rollup field but indirect entities such as opportunities and contacts were not getting caught by her rollup field.
Sometimes you have to go right to the source, (I cannot tell you how many times second-hand information sent me in the wrong direction). Here is the documentation on why the rollup wasn’t finding the opportunity visits. Per Microsoft’s documentation using their example:
Aggregate data for a record from all related activities and activities indirectly related via the Activity Party entity
In this example, we count the total number of emails sent to an account, where the account is listed on the email’s “To Recipient” line or “Cc Recipient line. This is done by specifying the Participation Type in FILTERS for the Activity Party entity in the rollup field definition. If you don’t use filtering, then all available participation types for an activity are used in the calculation. For more information about the Activity Party entity and participation types available for a particular activity, see MSDN: ActivityParty entity.
Another example from Microsoft:
Certain entity forms, such as Account or Contact, out-of-the-box, contain the associated grids. For example, an Account form includes Contacts, Cases, Opportunities and other grids. Some of the records shown in the Account form grids are directly related to the account record; others, indirectly, through the relationships with other records. In comparison, the rollup field aggregation uses only direct relationships explicitly defined in the rollup field definition. No other relationships are considered. To illustrate the difference in behavior, let’s look at the following example.
- The account A1 has a primary contact, P1. The case C1 is associated with the account A1 (C1.Customer field = A1) and the case C2 is associated with the contact P1 (C2.Customer field = P1).
- The Cases grid on the Account form for the A1 record shows two cases, C1 and C2.
- The rollup field on the account entity, called Total Number of Cases, is used to count the cases associated with the account.
- In the account rollup field definition, we specify the cases that have the Customer relationship with the account. After aggregation, the Total Number of Cases is equal to 1 (case C1). The case C2 is not included in the total, as it is directly related to the contact, not to the account, and can’t be explicitly defined in the account rollup field definition. As a result, the total number of cases returned by rollup operation doesn’t match the number of cases shown in the Cases grid.
So, going by that documentation the Appointment activity (renamed ‘Visit’ by this client) related to the Opportunity isn’t getting caught because the Account isn’t referenced in the Visit’s “Regarding”, “Required” or “Organizer” fields. (These were the fields identified in their Indirectly Related Activities filter.)
Solution: The Appointment needs to have the account listed in one of the fields available from the Indirectly Related Activities filter. For this customer, they created a workflow that would find the account on the creation of an appointment and insert that account into the unused “Optional attendee” field. We then based the Indirectly Related Activities filter on the “Optional attendee” field.