Calculate SUM from Child Entity Using Fetch (Aggregate) Function

(0) Share

Report

Posted on by Community Member

Hello Sir and Ma'am,

Can you help me find what's wrong in my code. I am having a hard time in using Calculate SUM, Using aggregate function number of related record associated with Parent.

I have 2 entities: parent and child. I need to get the sum of all the amounts of child and update it to the total amounts of parent.

Whenever a new amount is created to the particular child, it calculates over all total Amount and Update the new_total_amount in parent.

 (new_parent)

    ** new_parent_ID 
    ** new_description 
    ** new_total_amount 
    ** new_ispaid 
    ** new_status 
    ** new_transaction date 
  
(new_child) 
    ** new_child_ID 
    ** new_item 
    ** new_amount 
    ** new_parentID 
    ** new_transaction date

Please help me whats wrong on my code, thanks

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)
        {
       
       
                   // Obtain the execution context from the service provider.
                   Microsoft.Xrm.Sdk.IPluginExecutionContext context = (Microsoft.Xrm.Sdk.IPluginExecutionContext)
                       serviceProvider.GetService(typeof(Microsoft.Xrm.Sdk.IPluginExecutionContext));
       
                   //Extract the tracing service for use in debugging sandboxed plug-ins.
                   ITracingService tracingService =
                       (ITracingService)serviceProvider.GetService(typeof(ITracingService));
       
                   IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
                   IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);
       
                   if (context.InputParameters.Contains("Target") &&
                           context.InputParameters["Target"] is Entity)
                   {
                   Entity entity = (Entity)context.InputParameters["Target"];
                       Guid a = ((EntityReference)entity["new_parent"]).Id;
                       Entity parent = new Entity("new_parent");
                       parent.Id = a;
       
                               //fetchxml to get the sum total of estimatedvalue
                   string value_sum = string.Format(@"         
                       <fetch distinct='false' mapping='logical' aggregate='true'> 
                           <entity name='new_child'>
                               <attribute name='new_amount' alias='amounts_sum' aggregate='sum' />
                                  <filter type='and'>
                                   <condition attribute='new_parent' operator='eq' value='{0}' uiname='' uitype='' />
                                  </filter>
                           </entity>
                       </fetch>", a);
       
                              EntityCollection value_sum_result = service.RetrieveMultiple(new FetchExpression(value_sum));
                              decimal TotalValue = 0;
                              foreach (var c in value_sum_result.Entities)
                              {
                                   TotalValue += ((Decimal)((AliasedValue)c["amounts_sum"]).Value);
                              }       
                               parent.Attributes["new_total_amount"] = TotalValue;
                               service.Update(parent);
                   }
        }
    }  
}

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Pramod M 1,445 on at

Like (0)

Report

Hi Domz,

Couple of things

1) Why don't you use the rollup fields -

www.dynamicscrmpros.com/microsoft-dynamics-crm-2015-new-functionality-aggregate-calculate-rollup-data

2) You can use query expression to retrieve the records and use the linq to sum up

totalAmount = childLists.Sum(a => a.GetAttributeValue<Money>("amount").Value);

ChildLists is the list of your child record with amount as the individual amount.

3) For your fetchxml case did you try running the fetchxml in your code?

you can use the below tools

xrmtoolbox.codeplex.com

http://fxb.xrmtoolbox.com/

4) Also check the type of your "new_total_amount" field, is that decimal or money type.If money then use new Money(TotalValue);

Hope this helps.

Regards,

Pramod

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you sir, I'm gonna check these things.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Dear Domz,

Could you tell me ,this plugin will register on which step ?

I have same case, i have an associated child records that contain amount field which i would like to sum of these values.

Thanks

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report

Hi,

There are other options problably to do what you are looking for, but you can use the following function to calculate an Aggregate values:

private int GetAggregateValue(string entityName, string attributeName, ConditionExpression expression)

{

decimal rc = 0;

StringBuilder sb = new StringBuilder();

sb.AppendLine("<fetch distinct='false' mapping='logical' aggregate='true'> ");

sb.AppendLine(" <entity name='" + entityName + "'> ");

sb.AppendLine(" <attribute name='" + attributeName + "' alias='" + attributeName + "_sum' aggregate='sum'/> ");

sb.AppendLine(" <filter type='and'>");

sb.AppendLine(" <condition attribute='" + expression.AttributeName + "' operator='eq' value='" + expression.Values[0].ToString() + "' /> ");

sb.AppendLine(" </filter> ");

sb.AppendLine(" </entity> ");

sb.AppendLine("</fetch>");

EntityCollection totalCount = OrganizationService.RetrieveMultiple(new FetchExpression(sb.ToString()));

if (totalCount.Entities.Count > 0)

{

foreach (var t in totalCount.Entities)

{

rc = (decimal)((AliasedValue)t[attributeName + "_sum"]).Value;

break;

}

}

return rc;

}

You can call it the following way:

ConditionExpression exp = new ConditionExpression("new_parentid", ConditionOperator.Equal, parentId);

decimal totalAmount = GetAggregateValue("new_child", "new_amount", exp);

Entity parent = new Entity("new_parent");

parent.Id = parentId;

parent["new_total_amount"] = new Money(totalAmount);

service.Update(parent);

Hope this helps.

Was this reply helpful? Yes No