Aggregate Fetch XML Calculation C# CRM

Hi Just want to share the Code to Calculate the Aggregate in CRM, especially for those who are in the CRM version, older than 2015 so that there will be no calculated and roll-up field so far.
Using this helper code you just need to pass the Parent Id, and it will do the rest of Calculation and of course you just need to supply the Operator (like SUM, Count, AVG, etc)

public string CalculateAggregateFetchXML(string strEntityName, string strAggregateAttributeName,
                                                       AggregateOperator aggregateOperator, string strFilterXML) {
            string strFetchXML = string.Empty;
            string strAggregateAlias = "nec_alias";

            strFetchXML = string.Format(@" 
                            <fetch distinct='false' mapping='logical' aggregate='true'> 
                                <entity name='{0}'> 
                                    <attribute name='{1}' alias='{2}' aggregate='{3}' />
                                    {4}
                                </entity> 
                            </fetch>", strEntityName, strAggregateAttributeName, strAggregateAlias, aggregateOperator.ToString(), strFilterXML);

            EntityCollection aggregateResult = CrmService.RetrieveMultiple(new FetchExpression(strFetchXML));
            decimal totalValue = 0;

            foreach (var c in aggregateResult.Entities) {
                decimal aggregate2 = 0;
                if (c.Attributes.Contains(strAggregateAlias)) {
                    AliasedValue alias = ((AliasedValue)c[strAggregateAlias]);

                    if (alias.Value is Money) {
                        aggregate2 = ((Money)((AliasedValue)c[strAggregateAlias]).Value).Value;
                    } else if (alias.Value is Int32 || alias.Value is int) {
                        aggregate2 = ((int)((AliasedValue)c[strAggregateAlias]).Value);
                    } else if (alias.Value is Decimal || alias.Value is decimal) {
                        aggregate2 = ((decimal)((AliasedValue)c[strAggregateAlias]).Value);
                    }

                    totalValue = aggregate2;
                }
            }
            return totalValue.ToString();
        }

And the AggregateOperator Enum

public enum AggregateOperator {
            [Description("sum")]
            sum = 1,
            //
            [Description("avg")]
            avg = 2,
            //
            [Description("min")]
            min = 3,
            //
            [Description("max")]
            max = 4,
            //
            [Description("count(*)")]
            count = 5,
            //
            [Description("countcolumn")]
            countcolumn = 5,
        }

This usage is very easy

strTotalAttByRace = CrmContext.CalculateAggregateFetchXML(“entityname” “primaryidfieldname”,
                                                  NEC.ESBU.Helpers.CrmHelper.AggregateOperator.count,strFilterXML);

//strFilterXML You can skip this or use this in case you need more condition

It will result you a string or you can change to Integer, that is the total aggregate result

*Example for Aggregate Result with fetch xml

Is to return total Attendance by Race (that stored in the Contact entity)

Attendance is many to 1 relationship with Contact

strFilterXML = string.Format(@"<filter type='and'> 
                                                <condition attribute='{0}' operator='eq' uitype='{1}' value='{2}' />
                                                <condition attribute='pa_sessionid' operator='eq' uitype='pa_session' value='{3}' />
                                                <condition attribute='pa_type' operator='eq' value='{4}' />
                                            </filter>
                                            <link-entity name='contact' from='contactid' to='pa_participantid' alias='af'>
                                                <filter type='and'>
                                                    <condition attribute='pa_race' operator='eq' value='{5}' />
                                                </filter>
                                            </link-entity>",
                            strAttributeProductIdName, strEntityProductIdName, guidProductId.ToString(), guidSessionId.ToString(),
                            (int)productType, (int)raceType);

This will be very useful if you want to calculate child record, either SUM, COUNT, or AVG for instance, then just pass the parent ID or add more fetch XML to add more filter

*Work for Money, Whole Number, and Decimal data type

Hope this helps!
Thanks

CRM 2013 Using addCustomFilter() to get Filtered Lookup Field based on Linked Entity

We all have known that in CRM 2013 we can filter the lookup field using easier method compared to CRM 2011, that is using addCustomFilter() combined with addPreSearch() function.

This is one of the example how to use it, to filter contact based on email that contains @example.com

function onLoad()
{
    addEventHandler();
}

function addEventHandler() {
    // add the event handler for PreSearch Event
    Xrm.Page.getControl("parentcontactid").addPreSearch(addFilter);
}

function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
   var filter = "<filter type='and'>" +
                "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

Call the onLoad function during form onLoad event.

That code is looks good and it will work, yes, it will.

And if you notice, this is a very simple way, compared to the old code, using addCustomView(), which you need to build your own custom view and don’t forget to build layoutXML as well.

This is the addCustomView().

Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

However, there is a limitation, not only that is because those functions can only work for lookup field in the Refreshed UI Entity Form (it means cannot work for Price List Item, Quote Product, etc.), but this filter, if you notice, it can only work to filter using the attribute that is a part of that entity. So, it cannot be used to filter an entity based on the field from another entity.

The above example is to filter contact by email address (email address is one of the contact attribute), what if we are going to filter an entity record based on another related entity, for example in the Lead form, it has Existing Contact lookup field, then the users want to filter to show only Contact based on the Account Number or based on the Website/Email Address of Account or based on the Account Name that compared to the obtained Company Name in Lead form .

If you notice, the filter that is supported is currently just using the node that is started from <filter>..

//create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

However, from that XML we get (see the example below), if we are using another related entity as the filter, it is using <linked-entity>, which is not allowed in the addCustomFilter() method.

This is the example of the generated XML with criteria based on the another related entity’s field:

*The XML to get Contact based on Account’s email address.

.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
 <entity name="contact">
   <attribute name="fullname" />
   <attribute name="parentcustomerid" />
   <attribute name="telephone1" />
   <attribute name="emailaddress1" />
   <attribute name="contactid" />
   <order attribute="telephone1" descending="false" />
   <link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
   </link-entity>
 </entity>
</fetch>

So…

We cannot use these lines as variable filter.

<link-entity name="account" from="primarycontactid" to="contactid" alias="ab">
     <filter type="and">
       <condition attribute="emailaddress1" operator="like" value="%example%" />
     </filter>
</link-entity>

As we know that the only way is still using the old-fashioned CRM 2011 Code, that is using addCustomView() with many parameters that you might not want to build anymore.

Xrm.Page.getControl(arg).addCustomView(viewId, entityName, viewDisplayName, fetchXml, layoutXml, isDefault)

So, we should build our own fetchXML + layoutXML as well, in fact you have built your own view and you just need to filter it.

Then, as the alternative, you can use another code to find that related entity id, see this example:

var filter = "<filter type='and'>" +
             "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
             "</filter>";

    //add filter
Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);

You can use OData Query to find the Account Id.

The best part that you should do is by getting the AccountId and I believe the OData Query is will be your best friend.
To get used using odata, you can refer to my another blog post:

http://missdynamicscrm.blogspot.com/2014/10/tips-and-trick-odata-crm-2011-2013.html

Another limitation in addCustomFilter() is it only supports 'And'
(based on this link:http://msdn.microsoft.com/en-us/library/gg334266.aspx#BKMK_addCustomFilter)

If you need more than one Account Id, then you might need to add Operator IN, see this example:

<filter type="and">
        <condition attribute="accountid" operator="in">
          <value uiname="Fourth Coffee (sample)" uitype="account">{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Litware, Inc. (sample)" uitype="account">{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
          <value uiname="Adventure Works (sample)" uitype="account">{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>
        </condition>
</filter>

You might choose whether you want to use addCustomView() with those many required parameters or addCustomFilter() with that limitation and have to find the ID, but with no need to pass many parameters as the addCustomView() required.

Summary:

1. To use addCustomFilter() to filter based on the same entity field:

function addFilter() {
   //find contact contains this @example.com
   var email = "%@example.com%";

    //create a filter xml
    var filter = "<filter type='and'>" +
                 "<condition attribute='emailaddress1' operator='like' value='" + email + "'/>" +
                 "</filter>";

    //add filter
    Xrm.Page.getControl("parentcontactid").addCustomFilter(filter);
}

2. To filter based on the related entity (one record only):

 //create a filter xml
 var filter = 
"<filter type='and'>" +
     "<condition attribute='parentcustomerid' operator='eq' uiname='Fourth Coffee” (sample)' uitype='account' value='{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}' />" +
"</filter>";

3. To filter based on the related entity (multiple records)

//create a filter xml 
var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value uiname='Fourth Coffee (sample)' uitype='account'>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Litware, Inc. (sample)' uitype='account'>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value uiname='Adventure Works (sample)' uitype='account'>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";

//or you can just use this (uiname and uitype are not mandatory)

//create a filter xml
 var filter =
"<filter type='and'>" +
        "<condition attribute='accountid' operator='in'>" +
          "<value>{DBBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DDBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
          "<value>{DFBB8E56-B9D9-E311-9410-001CC4EECDD6}</value>" +
        "</condition>" +
"</filter>";

Result for Number 2 (Single Record):



Result for Number 3 (Multiple Records):



*Using the addCustomFilter() has advantage it will not break the column order and position you defined in the Customization, because you are just changing the criteria to filter, not creating a new custom view, so that you will not find any issue like I found and posted here.


Hope this helps!

Thanks.