Hi,
In this article I would like to explain Calculate SUM, Using aggregate function number of related record associated with Parent.
I have 2 Entity (1. Customer and 2. Orders)
- First I am capturing Customer name and Total Amount (Total Number of Order) in Customer Entity.
- In Order Entity I am capturing Product name and Amount
- First we need to calculate Number of related Orders to the particular Customer using Fetch XML.
- Return value should be updated in Parent Record .
-
Whenever a new order created to the particular customer, it calculates over all Order Amount and Update the amount in Customer
• First we need to calculate Number of related Orders to the particular Customer using Fetch XML.
- Return value should be updated in Parent Record .
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;
namespace Aggregate
{
public class Sum : IPlugin
{
public void Execute(IServiceProvider ServiceProvider)
{
IPluginExecutionContext Context = (IPluginExecutionContext)ServiceProvider.GetService(typeof(IPluginExecutionContext));
IOrganizationServiceFactory ServiceFactory = (IOrganizationServiceFactory)ServiceProvider.GetService(typeof(IOrganizationServiceFactory));
IOrganizationService Service = ServiceFactory.CreateOrganizationService(Context.UserId);
if (Context.PostEntityImages.Contains("PostImage") && Context.PostEntityImages["PostImage"] is Entity
{
Entity quantity = (Entity)Context.InputParameters["Target"];
var quantity = (EntityReference)quantity.Attributes["new_customer"];
decimal Total = FetchResult(quantity.Id, Service);
// Updating Parent Entity
Entity customer = new Entity("new_customer");
customer.Id = quantity.Id;
customer["new_total"] = new Money(Total);
Service.Update(customer);
}
}
private static decimal FetchResult(Guid quantity, IOrganizationService service)
{
string value_sum = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true'>
<entity name='new_quantity'>
<attribute name='new_amount' alias='totalamount_sum' aggregate='sum'/>
<filter type='and'>
<condition attribute='new_frominvoce' operator='eq' value='{0}' />
</filter>
</entity>
</fetch>";
decimal TotalValue = 0;
value_sum = string.Format(value_sum, quantity);
EntityCollection value_sum_result = (EntityCollection)service.RetrieveMultiple(new FetchExpression(value_sum));
foreach (var c in value_sum_result.Entities)
{
decimal aggregate2 = ((Money)((AliasedValue)c.Attributes["totalamount_sum"]).Value).Value;
TotalValue = aggregate2;
}
return TotalValue;
}
}
}
- Register your plugin Message = Create
- Primary Entity = new_customer
- Post Operation
Register New Image
Image Type : Post Image
Entity Alice : Post Image
Parameters : All Attributes
😛
Dynamics CRM 365 