Our last example tackles a more complicated real-world request. Eventually you will be asked to store a value on a parent entity that is calculated from values in the related child entities. Frequently these calculated values are simple aggregates, such as the count of child entities or perhaps a sum of some attribute on the child entities. For example, you might be asked to populate a custom attribute named new_assignedleadcount on the systemuser entity with the number of leads owned by that user. In addition, you might need to keep track of the average revenue of those leads in another custom attribute on the systemuser named new_averageleadrevenue.
Because these aggregate values typically do not need to be calculated synchronously, they are good candidates to be updated in a workflow. We’ll implement a custom activity called CalculateRelatedAggregateActivity that can iterate through related entities and expose the aggregate values in output properties. We’ll start by preparing the now-familiar custom activity class template as shown in Example 6-17.
Example 6-17. The beginning of the CalculateRelatedAggregateActivity class
using System;
using System.Collections.Generic;
using System.Reflection;
using System.Workflow.ComponentModel;
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Metadata;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.SdkTypeProxy.Metadata;
using Microsoft.Crm.Workflow;
namespace ProgrammingWithDynamicsCrm4.Workflow
{
[CrmWorkflowActivity("Calculate Related Aggregate",
"Programming Microsoft CRM 4")]
public class CalculateRelatedAggregateActivity: Activity
{
}
}At this point, there is nothing new. As usual, we’ll continue by adding our dependency properties. Because CalculateRelatedAggregateActivity has a lot of dependency properties and their use is more complex, we’ll start by just adding the input properties as shown in Example 6-18.
Example 6-18. CalculateRelatedAggregateActivity’s input properties
public static DependencyProperty RelationshipNameProperty =
DependencyProperty.Register("RelationshipName", typeof(string),
typeof(CalculateRelatedAggregateActivity));
[CrmInput("Relationship Name")]
public string RelationshipName
{
get { return (string)GetValue(RelationshipNameProperty); }
set { SetValue(RelationshipNameProperty, value); }
}
public static DependencyProperty IncludeInactiveRecordsProperty =
DependencyProperty.Register("IncludeInactiveRecords", typeof(CrmBoolean),
typeof(CalculateRelatedAggregateActivity));
[CrmDefault("false")]
[CrmInput("Include Inactive Records")]
public CrmBoolean IncludeInactiveRecords
{
get { return (CrmBoolean)GetValue(IncludeInactiveRecordsProperty); }
set { SetValue(IncludeInactiveRecordsProperty, value); }
}
public static readonly DependencyProperty PrimaryEntityIsParentProperty =
DependencyProperty.Register("PrimaryEntityIsParent", typeof(CrmBoolean),
typeof(CalculateRelatedAggregateActivity));
[CrmDefault("false")]
[CrmInput("Primary Entity Is Parent")]
public CrmBoolean PrimaryEntityIsParent
{
get { return (CrmBoolean)GetValue(PrimaryEntityIsParentProperty); }
set { SetValue(PrimaryEntityIsParentProperty, value); }
}
public static readonly DependencyProperty UsePreImageProperty =
DependencyProperty.Register("UsePreImage", typeof(CrmBoolean),
typeof(CalculateRelatedAggregateActivity));
public CrmBoolean UsePreImage
{
get { return (CrmBoolean)GetValue(UsePreImageProperty); }
set { SetValue(UsePreImageProperty, value); }
}
public static readonly DependencyProperty SumAttributeProperty =
DependencyProperty.Register("SumAttribute", typeof(string),
typeof(CalculateRelatedAggregateActivity));
[CrmInput("Sum Attribute")]
public string SumAttribute
{
get { return (string)GetValue(SumAttributeProperty); }
set { SetValue(SumAttributeProperty, value); }
}
public static readonly DependencyProperty AverageAttributeProperty =
DependencyProperty.Register("AverageAttribute", typeof(string),
typeof(CalculateRelatedAggregateActivity));
[CrmInput("Average Attribute")]
public string AverageAttribute
{
get { return (string)GetValue(AverageAttributeProperty); }
set { SetValue(AverageAttributeProperty, value); }
}We added six input properties: RelationshipName, IncludeInactiveRecords, PrimaryEntityIsParent, UsePreImage, SumAttribute, and AverageAttribute.
RelationshipName is the schema name for the relationship that ties the two entities together. You can get the relationship’s schema name by opening the parent entity definition from the Customize Entities view in Microsoft Dynamics CRM and clicking the 1:N Relationships area. For the example mentioned earlier, systemuser is related to lead through the relationship with a schema name of lead_owning_user.
IncludeInactiveRecords is a Boolean property used to indicate whether inactive records should be included in the calculation.
PrimaryEntityIsParent is also a Boolean property, but it is used to indicate whether the workflow’s primary entity is the parent or child in the relationship. This property is necessary because an entity can be self-referential in Microsoft Dynamics CRM 4.0. Most often, your workflows will be associated with the child entity in the relationship, so you will be able to leave this set at the default value of false.
UsePreImage is a Boolean property that can be used to indicate that the parent ID should be retrieved from the pre-image instead of the current entity instance. You set this property to true if you need to update a parent entity that just had a child associated with a new parent. You could then include two CalculateRelatedAggregateActivity steps: one to update the old parent entity and one to update the new parent. This property is ignored if PrimaryEntityIsParent is true.
SumAttribute can be used to indicate the name of the child attribute that should be summed.
AverageAttribute can be used to indicate the name of the child attribute that should be averaged.
Next we’ll define the output properties, which are shown in Example 6-19.
Example 6-19. CalculateRelatedAggregateActivity’s output properties
public static DependencyProperty CountProperty = DependencyProperty.Register(
"Count", typeof(CrmNumber), typeof(CalculateRelatedAggregateActivity));
[CrmOutput("Count")]
public CrmNumber Count
{
get { return (CrmNumber)GetValue(CountProperty); }
set { SetValue(CountProperty, value); }
}
public static DependencyProperty SumProperty = DependencyProperty.Register(
"Sum", typeof(CrmFloat), typeof(CalculateRelatedAggregateActivity));
[CrmOutput("Sum")]
public CrmFloat Sum
{
get { return (CrmFloat)GetValue(SumProperty); }
set { SetValue(SumProperty, value); }
}
public static DependencyProperty AverageProperty = DependencyProperty.Register(
"Average", typeof(CrmFloat), typeof(CalculateRelatedAggregateActivity));
[CrmOutput("Average")]
public CrmFloat Average
{
get { return (CrmFloat)GetValue(AverageProperty); }
set { SetValue(AverageProperty, value); }
}All three of the output properties are used to return the aggregate results. As you might guess, Count is set to the number of child entities, while Sum contains the sum of the attribute specified by SumAttribute, and Average contains the average of the attribute specified by AverageAttribute. Count will always be populated, but Sum and Average are only populated if the corresponding input property was set to specify an attribute name. Next we’ll take a look at the Execute method, shown in Example 6-20.
Example 6-20. CalculateRelatedAggregateActivity’s Execute method
protected override ActivityExecutionStatus Execute(
ActivityExecutionContext executionContext)
{
if (!String.IsNullOrEmpty(this.RelationshipName))
{
if (this.PrimaryEntityIsParent == null)
{
this.PrimaryEntityIsParent = new CrmBoolean(false);
}
if (this.IncludeInactiveRecords == null)
{
this.IncludeInactiveRecords = new CrmBoolean(false);
}
if (this.UsePreImage == null)
{
this.UsePreImage = new CrmBoolean(false);
}
IContextService contextService =
executionContext.GetService<IContextService>();
IWorkflowContext workflowContext = contextService.Context;
List<DynamicEntity> relatedEntities = GetRelatedEntities(workflowContext);
RollUpEntities(relatedEntities);
}
return ActivityExecutionStatus.Closed;
}Execute starts by setting up some simple defaults for properties, and then calls GetRelatedEntities to get a list of the child entities. It passes this list to RollUpEntities, which is responsible for calculating the aggregates. We’ll take a look at GetRelatedEntities first, which is shown in Example 6-21.
Example 6-21. The GetRelatedEntities method
private List<DynamicEntity> GetRelatedEntities(IWorkflowContext workflowContext)
{
OneToManyMetadata relationship = GetRelationship(workflowContext);
if (relationship == null)
{
throw new InvalidOperationException(String.Format(
"Could not find relationship with name '{0}' and {1} as {2}.",
this.RelationshipName,
workflowContext.PrimaryEntityName,
this.PrimaryEntityIsParent.Value ? "parent" : "child"));
}
Guid parentEntityId = GetParentEntityId(workflowContext, relationship);
List<DynamicEntity> relatedEntities = new List<DynamicEntity>();
if (parentEntityId != Guid.Empty)
{
RetrieveMultipleRequest retrieveRequest = new RetrieveMultipleRequest();
retrieveRequest.ReturnDynamicEntities = true;
retrieveRequest.Query = CreateQueryForRelatedEntities(
workflowContext, parentEntityId, relationship);
ICrmService crmService = workflowContext.CreateCrmService(true);
RetrieveMultipleResponse retrieveResponse =
(RetrieveMultipleResponse)crmService.Execute(retrieveRequest);
foreach (DynamicEntity relatedEntity in
retrieveResponse.BusinessEntityCollection.BusinessEntities)
{
relatedEntities.Add(relatedEntity);
}
}
return relatedEntities;
}GetRelatedEntities starts by calling GetRelationship to retrieve the relationship metadata. After validating that the correct relationship was found, GetParentEntityId is called to retrieve the parent entity ID. As long as a valid parent entity ID exists, GetRelatedEntities continues by calling CreateQueryForRelatedEntities to build a query, and then executes it against the CrmService. The resulting dynamic entities are bundled into a list and returned. Next we’ll take a look at the GetRelationship method, shown in Example 6-22.
Example 6-22. The GetRelationship method
private OneToManyMetadata GetRelationship(IWorkflowContext workflowContext)
{
IMetadataService metadataService = workflowContext.CreateMetadataService(true);
RetrieveRelationshipRequest relationshipRequest = new
RetrieveRelationshipRequest();
relationshipRequest.Name = this.RelationshipName;
RetrieveRelationshipResponse relationshipResponse =
(RetrieveRelationshipResponse)metadataService.Execute(relationshipRequest);
if (relationshipResponse.RelationshipMetadata.RelationshipType !=
EntityRelationshipType.OneToMany)
{
return null;
}
OneToManyMetadata relationship =
(OneToManyMetadata)relationshipResponse.RelationshipMetadata;
if (this.PrimaryEntityIsParent.Value &&
relationship.ReferencedEntity != workflowContext.PrimaryEntityName)
{
return null;
}
if(!this.PrimaryEntityIsParent.Value &&
relationship.ReferencingEntity != workflowContext.PrimaryEntityName)
{
return null;
}
if (relationship.ReferencingAttribute == "owninguser" ||
relationship.ReferencingAttribute == "owningteam")
{
relationship.ReferencingAttribute = "ownerid";
}
return relationship;
}GetRelationship executes a fairly straightforward metadata request to retrieve the relationship information. In addition, it validates that the workflow’s primary entity is either the child or parent in the relationship, based on the value of PrimaryEntityIsParent. It also maps the internal attribute names owninguser and owningteam to the publicly visible ownerid attribute. Internally, Microsoft Dynamics CRM uses the owninguser and owningteam attributes to associate an entity owner to either a team or user, but by the time the end users see these attributes, they are combined into the ownerid attribute.
Next let’s examine the GetParentEntityId method, which is called by GetRelatedEntities after it retrieves the relationship metadata from GetRelationship. GetParentEntityId is shown in Example 6-23.
Example 6-23. The GetParentEntityId method
private Guid GetParentEntityId(
IWorkflowContext workflowContext, OneToManyMetadata relationship)
{
Guid parentEntityId = Guid.Empty;
if (this.PrimaryEntityIsParent.Value)
{
parentEntityId = workflowContext.PrimaryEntityId;
}
else if (this.UsePreImage.Value)
{
DynamicEntity preImage = workflowContext.PrimaryEntityPreImage;
parentEntityId = GetReferenceValue(
preImage, relationship.ReferencingAttribute);
}
else
{
DynamicEntity image = workflowContext.PrimaryEntityImage;
parentEntityId = GetReferenceValue(
image, relationship.ReferencingAttribute);
if (parentEntityId == Guid.Empty)
{
image = RetrievePrimaryEntity(
workflowContext, relationship.ReferencingAttribute);
parentEntityId = GetReferenceValue(
image, relationship.ReferencingAttribute);
}
}
return parentEntityId;
}GetParentEntityId is responsible for retrieving the ID of the parent entity. If PrimaryEntityIsParent is true, the parent entity ID is simply the ID of the workflow’s primary entity. Otherwise, if UsePreImage is true, the parent ID is retrieved from the pre-image. Finally, if both of these properties are false, the parent ID is retrieved from the primary entity. The method first checks in the primary entity image, and if it doesn’t find the entity there, the method calls RetrievePrimaryEntity to fetch a copy of the entity from the CrmService. The GetReferenceValue helper method is called throughout to help extract the parent entity ID from the DynamicEntity instances. GetReferenceValue and RetrievePrimaryEntity are both straightforward methods and as such are both shown in Example 6-24.
Example 6-24. The RetrievePrimaryEntity and GetReferenceValue methods
private DynamicEntity RetrievePrimaryEntity(
IWorkflowContext workflowContext, params string[] attributeNames)
{
ICrmService crmService = workflowContext.CreateCrmService(true);
RetrieveRequest retrievePrimaryEntityRequest = new RetrieveRequest();
retrievePrimaryEntityRequest.ReturnDynamicEntities = true;
retrievePrimaryEntityRequest.ColumnSet = new ColumnSet(attributeNames);
retrievePrimaryEntityRequest.Target = new TargetRetrieveDynamic
{
EntityName = workflowContext.PrimaryEntityName,
EntityId = workflowContext.PrimaryEntityId
};
RetrieveResponse retrievePrimaryEntityResponse =
(RetrieveResponse)crmService.Execute(retrievePrimaryEntityRequest);
return (DynamicEntity)retrievePrimaryEntityResponse.BusinessEntity;
}
private static Guid GetReferenceValue(DynamicEntity entity, string attributeName)
{
if (entity != null && entity.Properties.Contains(attributeName))
{
CrmReference reference = entity[attributeName] as CrmReference;
if (reference != null)
{
return reference.Value;
}
}
return Guid.Empty;
}RetrievePrimaryEntity creates a simple RetrieveRequest to get the primary entity from the CrmService, while GetReferenceValue safely extracts a Guid from a DynamicEntity. After the parent entity ID is determined, RetrieveRelatedEntities calls CreateQueryForRelatedEntities to build a QueryExpression that can retrieve the child entities. Example 6-25 shows the source code for CreateQueryForRelatedEntities.
Example 6-25. The CreateQueryForRelatedEntities method
private QueryExpression CreateQueryForRelatedEntities(
IWorkflowContext workflowContext, Guid parentEntityId,
OneToManyMetadata relationship)
{
QueryExpression query = new QueryExpression();
query.EntityName = relationship.ReferencingEntity;
ColumnSet cols = new ColumnSet();
if (!String.IsNullOrEmpty(this.SumAttribute))
{
cols.AddColumn(this.SumAttribute);
}
if (!String.IsNullOrEmpty(this.AverageAttribute))
{
cols.AddColumn(this.AverageAttribute);
}
query.ColumnSet = cols;
query.Criteria.AddCondition(
relationship.ReferencingAttribute,
ConditionOperator.Equal,
parentEntityId);
if (this.IncludeInactiveRecords == null || !this.IncludeInactiveRecords.Value)
{
query.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
}
return query;
}If the SumAttribute and AverageAttribute properties have values, those columns are included in the results. The only condition that is guaranteed to be added is that the relationship’s ReferencingAttribute is equal to the parent entity’s ID. If IncludeInactiveRecords is not true, the query also filters on the statecode attribute.
Once the query is executed, all of the related entities are passed back to the Execute method, which calls RollUpEntities to calculate the aggregate values. RollUpEntities is shown in Example 6-26.
Example 6-26. The RollUpEntities method
private void RollUpEntities(List<DynamicEntity> entities)
{
this.Count = new CrmNumber(entities.Count);
if (!String.IsNullOrEmpty(this.SumAttribute))
{
double value = 0.0;
foreach (DynamicEntity entity in entities)
{
if (entity.Properties.Contains(this.SumAttribute))
{
value += ConvertValueToDouble(entity[this.SumAttribute]);
}
}
this.Sum = new CrmFloat(value);
}
if (!String.IsNullOrEmpty(this.AverageAttribute))
{
double value = 0.0;
double averageCount = 0.0;
foreach (DynamicEntity entity in entities)
{
if (entity.Properties.Contains(this.AverageAttribute))
{
value += ConvertValueToDouble(entity[this.AverageAttribute]);
averageCount++;
}
}
if (averageCount > 0)
{
this.Average = new CrmFloat(value / averageCount);
}
}
}RollUpEntities starts by capturing the number of related entities in the Count property. It then loops through the related entities and calculates the Sum and Average properties if SumAttribute and AverageAttribute values were specified. RollUpEntities only depends on one other method, ConvertValueToDouble, which is shown in Example 6-27.
Example 6-27. The ConvertValueToDouble method
private double ConvertValueToDouble(object property)
{
if (property != null)
{
PropertyInfo valueProperty = property.GetType().GetProperty("Value");
if (valueProperty != null)
{
return Convert.ToDouble(valueProperty.GetValue(property, null));
}
}
return 0.0;
}ConvertValueToDouble uses reflection to extract a value from the DynamicEntity value passed in and then converts that value to a double. And with that, we are done!
CalculateRelatedAggregateActivity demonstrates some of the powerful reusable functionality that you can provide by implementing your own custom workflow activities within Microsoft Dynamics CRM. Using this activity, you can maintain calculated values on parent entities based on attributes found on related child entities.