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
😛
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
(new_frominvoce) what should I replace here? I couldn’t understand this part of code, please clarify if it possible.