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 .

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

Duplicate Detection Plugin Crm 2011


Hi ,

Recently I had a requirement to create Duplicate detection rule using c# (Plugin) in Account Entity, to avoid redundancy.

You can try like bellow method

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//CRM SDK Namespace
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace Sample
{
    public class Duplicate_Detection : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = (IOrganizationService)serviceFactory.CreateOrganizationService(context.UserId);
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                Entity entity = (Entity)context.InputParameters["Target"];
                var Name = entity.GetAttributeValue<string>("name");

                #region  Retrieve All Account Record

                QueryExpression Account = new QueryExpression { EntityName = entity.LogicalName, ColumnSet = new ColumnSet("name") };
                Account.Criteria.AddCondition("name", ConditionOperator.Equal, Name);
               Account.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);

               EntityCollection RetrieveAccount = service.RetrieveMultiple(Account);

                //If the retrieved Account Count Greater Than 1 , following Error Message Throw

               if (RetrieveAccount.Entities.Count > 1)
                {
                    throw new InvalidPluginExecutionException("Following Record with Same Name Exists");

                }
               else if (RetrieveAccount.Entities.Count == 0)
                {
                    return;
                }
                #endregion
            }
        }
    }
}
  • Register your plugin Message = Create
  • Primary Entity = account
  • Post Operation

Hope This Help You . . .

Display Images In Email body crm 2011


Hi ,

In this Article I would like to explain how to insert image in Email Body .

  • First we need to publish our Image in IIS . Where we will use the url reference to get available of the Image .
  • You can directly copy paste the image inside Email body , by selecting the image which is available in iis and paste it in Email body .

Using  C#

  • Select particular Image  and copy the url and use like bellow code.
  • Eg:
  • onlin
   //published Image Url Link
   string crmModule = "<html> <img  src='http://www.bse-c.co.kr/image/CRM%20capabilities.png'  width='150' height='150'></html>";
  • You can also Display Image Which are Created in CRM as Web Resource.
  • Upload your image as Web resource in Crm and Copy the URl available at webresource bellow.

web

Use like bellow code,

//Uploaded Crm webresource Url.
string webresource = "<html> <img  src='~/WebResources/new_Crm'  width='205' height='150'></html>";
  • Refer Bellow Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//CRM SDK Namespace
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace Sample
{
    public class Email : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            // Obtain the execution context from the service provider.
            IPluginExecutionContext context =
            (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.UserId);

            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {

                Entity Lead = (Entity)context.InputParameters["Target"];
                string Emailid = Lead.GetAttributeValue<string>("emailaddress1");
                string Name = Lead.GetAttributeValue<string>("lastname");

                //From Current User
                Entity from = new Entity("activityparty");
                from["partyid"] = new EntityReference("systemuser", context.UserId);

                // To the Newly Created Lead User
                Entity to = new Entity("activityparty");
                to["partyid"] = new EntityReference("lead", Lead.Id);

                //Uploaded Crm webresource Url.
              string webresource = "<html> <img  src='~/WebResources/new_Crm'  width='205' height='150'></html>";

              //published Image Url Link

              string crmModule = "<html> <img  src='http://www.bse-c.co.kr/image/CRM%20capabilities.png'  width='150' height='150'></html>";

                //Hyperlink (or) Re-Direct Url link
              string Hyper = String.Format("<a href='https://parthimscrm.wordpress.com/'> Click Here</a>");

                //Create Email
             Entity Email  = new Entity("email");
             Email["from"] = new Entity[] { from };
             Email["to"] = new Entity[] { to };
             Email["subject"] = "welcome Mr : " + Name;
             Email["description"] = "<h3> Dear  " + Name + "</h3>" + "<br/>" + "Microsoft Dynamics CRM 2011 Module" + "<br/>" + crmModule + "<br/>" + "visit my wordpress blog " + Hyper + "<br/>" + webresource;

                //SendEmailRequest
                Guid _emailId = service.Create(Email);
                SendEmailRequest reqSendEmail = new SendEmailRequest();
                reqSendEmail.EmailId = _emailId;
                reqSendEmail.TrackingToken = "";
                reqSendEmail.IssueSend = true;
                SendEmailResponse res = (SendEmailResponse)service.Execute(reqSendEmail);

            }
        }
    }
}
  • Register your plugin Message = Create
  • Primary Entity = lead
  • Post Operation
  • 10

Hope This Help You . . . 

Cascade Deactivate Related Record using C# plugin CRM 2011


Hi all ,

In this article I would like to explain how to De-Activate child records (Related records) when parent record status gets changed.

  • In my Scenario I am taking Account entity, which consists of related Contact record in Left navigation.
  • I would like to De-Activate all list of contact associated with Account.

Cascade Deactivates Record CRM 2011

  • List of contact Associated with Parent Account

2

#region  De-Activate Related Contact Associated with Account Record

    //Note for  Active record  state value = 1 .
   // For In - Active record  State value = 0 .

  /// Check weather state value == 1.
         if (state.Value == 1)
            {
                    /// querry expression to retrieve related Contact associated to Account.
                    QueryExpression contact = new QueryExpression { EntityName = "contact", ColumnSet = new ColumnSet("contactid", "parentcustomerid") };
                    contact.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, Account.Id);

                    // Check weather statecode of the record are Active
                    contact.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
                    EntityCollection Retrievecontact = service.RetrieveMultiple(contact);

                    if (Retrievecontact.Entities.Count > 0)
                    {

                        foreach (var a in Retrievecontact.Entities)
                        {
                            SetStateRequest contactsetStateReq = new SetStateRequest();
                            contactsetStateReq.EntityMoniker = new EntityReference(a.LogicalName, new Guid(a.Id.ToString()));
                            contactsetStateReq.State = new OptionSetValue(1);
                            contactsetStateReq.Status = new OptionSetValue(-1);
                            service.Execute(contactsetStateReq);
                        }

                    }

                }
    #endregion
  • De – Active Parent Record

3

  • Related child contact record status gets updated to In-Active. When Parent Account record gets In-Active

5

#region Re-Activate In-Active Contact Records

   /// Check weather state value == 0. For In active records
        if (state.Value == 0)
          {
    /// querry expression to retrieve related Contact associated to Account.
       QueryExpression contact = new QueryExpression { EntityName = "contact", ColumnSet = new ColumnSet("contactid", "parentcustomerid") };
         contact.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, Account.Id);

      // Check weather statecode of the record are In-Active
        contact.Criteria.AddCondition("statecode", ConditionOperator.Equal, 1);
          EntityCollection Retrievecontact = service.RetrieveMultiple(contact);

       if (Retrievecontact.Entities.Count > 0)
                    {

                        foreach (var a in Retrievecontact.Entities)
                        {
                            SetStateRequest contactsetStateReq = new SetStateRequest();
                            contactsetStateReq.EntityMoniker = new EntityReference(a.LogicalName, new Guid(a.Id.ToString()));
                            contactsetStateReq.State = new OptionSetValue(0);
                            contactsetStateReq.Status = new OptionSetValue(-1);
                            service.Execute(contactsetStateReq);
                        }
          }
          }
  #endregion
  • Re- Activate Account Record to Active State

6

  • Related Contact Record status gets Updated to Active .

8

Plugin Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//CRM SDK Namespace
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace Sample
{
    public class Cascade : 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.InputParameters.Contains("EntityMoniker") && context.InputParameters["EntityMoniker"] is EntityReference)
            {

                //Parent Entity (Account)
                EntityReference Account = (EntityReference)context.InputParameters["EntityMoniker"];

                //Capturing record  State & Status
                OptionSetValue state = (OptionSetValue)context.InputParameters["State"];
                OptionSetValue status = (OptionSetValue)context.InputParameters["Status"];

                #region De-Activate Related Contact Associated with Account Record

                //Note for  Active record  state value = 1 .
                // For In - Active record  State value = 0 .

                /// Check weather state value == 1.
                if (state.Value == 1)
                {
                    /// querry expression to retrieve related Contact associated to Account.
                    QueryExpression contact = new QueryExpression { EntityName = "contact", ColumnSet = new ColumnSet("contactid", "parentcustomerid") };
                    contact.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, Account.Id);

                    // Check weather statecode of the record are Active
                    contact.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
                    EntityCollection Retrievecontact = service.RetrieveMultiple(contact);

                    if (Retrievecontact.Entities.Count > 0)
                    {

                        foreach (var a in Retrievecontact.Entities)
                        {
                            SetStateRequest contactsetStateReq = new SetStateRequest();
                            contactsetStateReq.EntityMoniker = new EntityReference(a.LogicalName, new Guid(a.Id.ToString()));
                            contactsetStateReq.State = new OptionSetValue(1);
                            contactsetStateReq.Status = new OptionSetValue(-1);
                            service.Execute(contactsetStateReq);
                        }

                    }

                }
                #endregion

                ///<Re Activate Relate record when Parent record status gets Activated>

                #region Re-Activate In-Active Contact Records

                /// Check weather state value == 0. For In active records
                if (state.Value == 0)
                {
                    /// querry expression to retrieve related Contact associated to Account.
                    QueryExpression contact = new QueryExpression { EntityName = "contact", ColumnSet = new ColumnSet("contactid", "parentcustomerid") };
                    contact.Criteria.AddCondition("parentcustomerid", ConditionOperator.Equal, Account.Id);

                    // Check weather statecode of the record are In-Active
                    contact.Criteria.AddCondition("statecode", ConditionOperator.Equal, 1);
                    EntityCollection Retrievecontact = service.RetrieveMultiple(contact);

                    if (Retrievecontact.Entities.Count > 0)
                    {

                        foreach (var a in Retrievecontact.Entities)
                        {
                            SetStateRequest contactsetStateReq = new SetStateRequest();
                            contactsetStateReq.EntityMoniker = new EntityReference(a.LogicalName, new Guid(a.Id.ToString()));
                            contactsetStateReq.State = new OptionSetValue(0);
                            contactsetStateReq.Status = new OptionSetValue(-1);
                            service.Execute(contactsetStateReq);
                        }

                    }

                }
                #endregion
            }
        }
    }
}

  • Register your plugin Message = SetStateDynamicEntity
  • Primary Entity = Account
  • Post Operation

9

Hope This Help You . . .