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 .

Aggregate

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 . . .

Advertisements

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

  1. Hello.. Thanks for the Code, it is very helpful.. However I am facing an issue here. Our Organzation’s Base currency is USD, however my entity’s transaction currency is EUR. While performing fetchxml it is always returning the aggregate value in base currency and it is trying to update the same value into the Total field (euro) of the parent which will end up into wrong figure. Do you have any solution for that

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s