Calculate SUM from Child Entity Using Fetch (Aggregate) Function , CRM 2011:Plug-in

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

😛

Hope This Help You . . .