Adding Filtered Views in UCI In Dynamics 365 CRM. Finally Achieved.


Filtered Subgrid in Dynamics 365 CRM U 

Finally, I was able to achieve the functionality of adding a filter to the subgrid view on the entity after the functionality was removed from the new UCI forms.

Earlier in the Legacy form, we use the unsupported way of adding the functionality for filtered view through DOM( Document.getElementBId()) objects. Now I found the way to achieve this through retrieve-multiple Plugin and in a fully supported way and the Paging issue also got resolved.

Before I show you the steps for the new UCI, I will like to show you the fetchXml I was trying to achieve in the filtered View.

post4.png
FetchXml We need to add in the filtered view.

Steps we need to follow.

If you need to take the GUID of the record of the entity form on which the Subgrid is you first need to create a view and add it in the subgrid as a filter on the related records.  This step is required to retrieve the GUID of the current record in our plugin.

post1Post2

You can then add your basic filter (filter on the primary entity) if you want, directly in the view. status eq active is that condition in my example.

The View has been filtered based on related records so that we can get the GUIDof the form on which the subgrid is in our plugin.

Post3

Then you need to write a plugin on RetrieveMultiple message and on the pre-operation pipeline.

Below is the Plugin code You need to add.

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

namespace RetrievemultiplePlugin
{
    public class RetrievemultiplePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));

            IOrganizationServiceFactory factory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));

            IOrganizationService service = (IOrganizationService)factory.CreateOrganizationService(context.UserId);
            ITracingService tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            try
            {
                if (context.Mode == 0 && context.Stage == 20 && (context.MessageName.Equals("RetrieveMultiple")) && context.Depth == 1)
                {
                    if (context.InputParameters.Contains("Query") && context.PrimaryEntityName == "contact") //The primary entity on which the plugin has been registered
                    {                                                                                       //Or you can say the entity which is getting filteren on the view
                        if (context.InputParameters["Query"] is FetchExpression) // In UCI the query we get is in fetchExpression
                        {
                            var thisQuery = context.InputParameters["Query"];
                            var fetchExpressionQuery = thisQuery as FetchExpression;
                            if (fetchExpressionQuery != null)
                            {
                                XDocument fetchXmlDoc = XDocument.Parse(fetchExpressionQuery.Query); //We will parse the fetchExpression in fetchXml.
                                //The required entity element

                                fetchXmlDoc.Descendants("fetch").FirstOrDefault().Attribute("distinct").Value = "true"; //By default we will change
                                                                                                                           //the distinct attribute of fetch to true

                                if (fetchXmlDoc.Descendants("link-entity").FirstOrDefault() == null) //If link-entity not present in the attribute it will return.
                                    return;
                                var entityElement = fetchXmlDoc.Descendants("link-entity").FirstOrDefault();
                                var entityName = entityElement.Attributes("name").FirstOrDefault().Value;  //Gets the name of the link attribute, this link attribute
                                bool replace = false;                                                       //is the one which we added in the subgrid as related record.

                                string currentAPRId = string.Empty;
                                if (entityName == "new_areaofprimaryresponsibility")   //Checks the name of the linked entity.
                                {
                                    var filterElements = entityElement.Descendants("filter");
                                    //We will get all the filters in the fetch and find the condition which is matching to our related record so that
                                    //we get the Guid of our entity record  on which the form is.

                                    //Find any existing statecode conditions
                                    var prmAPRCondition = from c in filterElements.Descendants("condition")
                                                          where c.Attribute("attribute").Value.Equals("new_areaofprimaryresponsibilityid")      //prm_allocationid
                                                          select c;
                                    if (prmAPRCondition.Count() > 0)
                                    {
                                        foreach (XElement el in prmAPRCondition)
                                            currentAPRId = (string)el.Attribute("value");  //Here we retrieve our required GUID to be furthere use in the code.

                                        prmAPRCondition.ToList().ForEach(x => x.Remove()); //Once we have our GUID, we will remove all the linked entity which is coming form the related part.

                                        //Now we will create our fetch in XMlDoc element.

                                        entityElement.ReplaceWith(new XElement("link-entity",
                                                    new XAttribute("name", "prm_contact_prm_location"),
                                                    new XAttribute("from", "contactid"), //not equal
                                                    new XAttribute("to", "contactid"), //not equal
                                                     new XAttribute("visible", "false"),
                                                    new XAttribute("intersect", "true"), //not equal
                                                    new XElement("link-entity",
                                                    new XAttribute("name", "prm_location"),
                                                    new XAttribute("from", "prm_locationid"), //not equal
                                                    new XAttribute("to", "prm_locationid"), //not equal
                                                    new XAttribute("alias", "ad"), //Inactive
                                                    new XElement("filter",
                                                    new XElement("condition",
                                                    new XAttribute("attribute", "prm_apr"),
                                                    new XAttribute("operator", "eq"), //not equal
                                                    new XAttribute("value", currentAPRId) //Inactive
                                                    )
                                                    )
                                                )
                                                )
                                                );
                                        replace = true;
                                    }

                                    if (replace)
                                    {
                                        fetchExpressionQuery.Query = fetchXmlDoc.ToString();
                                    }
                                }
                                else if (entityName == "prm_allocation")  //Used else because we are filtering on two entity.
                                {
                                    var filterElements = entityElement.Descendants("filter");
                                    //Find any existing statecode conditions
                                    var prmAPRCondition = from c in filterElements.Descendants("condition")
                                                          where c.Attribute("attribute").Value.Equals("prm_allocationid")      //prm_allocationid
                                                          select c;
                                    if (prmAPRCondition.Count() > 0)
                                    {
                                        foreach (XElement el in prmAPRCondition)
                                            currentAPRId = (string)el.Attribute("value");

                                        prmAPRCondition.ToList().ForEach(x => x.Remove());

                                        Entity allocation = service.Retrieve("prm_allocation", new Guid(currentAPRId), new ColumnSet("prm_apr"));
                                        EntityReference aprReference = allocation.Contains("prm_apr") ? (EntityReference)allocation["prm_apr"] : null;

                                        if (aprReference == null)
                                            return;

                                        entityElement.ReplaceWith(new XElement("link-entity",
                                                    new XAttribute("name", "prm_contact_prm_location"),
                                                    new XAttribute("from", "contactid"), //not equal
                                                    new XAttribute("to", "contactid"), //not equal
                                                     new XAttribute("visible", "false"),
                                                    new XAttribute("intersect", "true"), //not equal
                                                    new XElement("link-entity",
                                                    new XAttribute("name", "prm_location"),
                                                    new XAttribute("from", "prm_locationid"), //not equal
                                                    new XAttribute("to", "prm_locationid"), //not equal
                                                    new XAttribute("alias", "ad"), //Inactive
                                                    new XElement("filter",
                                                    new XElement("condition",
                                                    new XAttribute("attribute", "prm_apr"),
                                                    new XAttribute("operator", "eq"), //not equal
                                                    new XAttribute("value", aprReference.Id))))), //Inactive
                                                    new XElement("link-entity",
                                                    new XAttribute("name", "prm_contact_prm_businessrole"),
                                                    new XAttribute("from", "contactid"), //not equal
                                                    new XAttribute("to", "contactid"), //not equal
                                                     new XAttribute("visible", "false"),
                                                    new XAttribute("intersect", "true"), //not equal
                                                    new XElement("link-entity",
                                                    new XAttribute("name", "prm_businessrole"),
                                                    new XAttribute("from", "prm_businessroleid"), //not equal
                                                    new XAttribute("to", "prm_businessroleid"), //not equal
                                                    new XAttribute("alias", "ae"),
                                                     new XElement("filter",
                                                    new XElement("condition",
                                                    new XAttribute("attribute", "prm_name"),
                                                    new XAttribute("operator", "like"), //not equal
                                                    new XAttribute("value", "%Specialist%") //Inactive
                                                    )
                                                    )
                                                )
                                                )
                                                );
                                        replace = true;
                                    }

                                    if (replace)
                                    {
                                        fetchExpressionQuery.Query = fetchXmlDoc.ToString();
                                    }

                                }
                            }
                        }
                    }

                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message + ex.InnerException);
            }
        }
    }
}

You can cross-check the plugin with the fetchXml on the top for better understanding and how to parse as per your requirements.

Finally, the filtered subgrid will look like this.

Post5

You can reach out to me for more information, Comment or mail me from the About Us tab section.


CI  forms

17 thoughts on “Adding Filtered Views in UCI In Dynamics 365 CRM. Finally Achieved.

  1. Hi Sanket,

    You can convert to/from QueryExpression and FetchXML by using the FetchXmlToQueryExpressionRequest and QueryExpressionToFetchXmlRequest, so the whole horrible manual editing of the XML is not necessary.
    You can convert the fetch XML to a query expression. Do your modifications there in a at least semi object model and then convert back.

    Like

      • Hi Sanket,

        Any luck with getting this to work with the subgrid. MS does it again. Get something working they make a change and you have to learn something different.

        Thanks

        Like

      • does this also work if there is no direct relationship between root and end entity? I tried updating fetchXML in pre-operation but in post operation it reverts back to the original fetchXml and does not show results based on updated fetchXml.

        Like

  2. Hi Sanket,
    As per you I have written the code, but exception is throwing at this line :
    fetchXmlDoc.Descendants(“fetch”).FirstOrDefault().Attribute(“distinct”).Value = “true”;

    can you please help me.

    Thanks,

    Like

    • Hey, that line of code is not mandatory you can also remove that and check..it should work fine.
      BTW, It looks like your fetch from the system doesn’t have a distinct attribute.

      Like

  3. Hi Sanket,
    Interesting topic.I also have to use same thing like in case entity contact lookup there based on contact lookup value need to show related cases in subgrid in Case entity.Can i use this same approach ?

    Like

  4. Entity allocation = service.Retrieve(“prm_allocation”, new Guid(currentAPRId), new ColumnSet(“prm_apr”));

    Unable to retrieve using service. It says a specific domain cannot be reached. not sure what I’m doing wrong?

    Like

  5. Hi Sanket,

    Thanks for the article. I am trying this on activities subgrid for the account record. I want to show all the activities for the company from the child contacts.

    Below FetechXML works fine in xrmtoolbar and returns the results.
    .

    However when implementing in the plugin I get error “An attribute cannot be added to content” and the error is at following replace function: any idea what is wrong here:

    entityElement.ReplaceWith(

    new XElement(“link-entity”, “activityparty”),
    new XAttribute(“from”, “activityid”),
    new XAttribute(“to”, “activityid”),
    new XAttribute(“alias”, “epartytype”),
    new XAttribute(“link-type”, “inner”),
    new XElement(“link-entity”,
    new XAttribute(“name”, “contact”),
    new XAttribute(“from”, “contactid”),
    new XAttribute(“to”, “partyid”),
    new XAttribute(“alias”, “contact”),
    new XAttribute(“link-type”, “inner”),
    new XElement(“filter”,
    new XElement(“condition”,
    new XAttribute(“attribute”, “parentcustomerid”),
    new XAttribute(“operator”, “eq”),
    new XAttribute(“value”, accountId)
    )
    )
    )
    );

    before above function, the execution of followng line of code

    accountCondition.ToList().ForEach(x => x.Remove());

    returns the following which I am trying to replace with above replace function….

    {

    }

    Any suggestion how this can be fixed please?

    Thanks

    Like

  6. Sorry for some reason both FetchXML didn’t show in my comment above and not sure why:
    Below original “link-entity” after “Remove” function call, tried removing corner brace from the beginning and end in case any script on this blog is blocking the display…

    link-entity name=”account” from=”accountid” to=”regardingobjectid” alias=”bb”
    filter type=”and” /
    /link-entity

    The original FetchXML which was tested in xrmtoolbox and returns the expected results.i.e. activities for the company(where its contacts are involved as one of the party)

    fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”
    entity name=”activitypointer”
    attribute name=”activitytypecode” /
    attribute name=”subject” /
    attribute name=”statecode” /
    attribute name=”activityid” /
    order attribute=”createdon” descending=”true” /
    attribute name=”scheduledstart” /
    attribute name=”scheduledend” /
    link-entity name=”activityparty” to=”activityid” from=”activityid” link-type=”inner” alias=”eparty”
    attribute name=”participationtypemask” alias=”epartytype” /
    attribute name=”partyid” distinct=”true” /
    attribute name=”participationtypemask” /
    attribute name=”partyidname” /
    attribute name=”participationtypemaskname” /
    link-entity name=”contact” from=”contactid” to=”partyid” visible=”false” link-type=”inner” alias=”Contact”
    attribute name=”fullname” /
    attribute name=”parentcustomerid” /
    attribute name=”jobtitle” /
    filter
    condition attribute=”parentcustomerid” operator=”eq” value=”XXXXXXXXXXXX” /
    /filter
    /link-entity
    /link-entity
    /entity
    /fetch

    Like

  7. Dude, thanks for this. Really saved the day. It’s hard to believe that this is the only concise explanation of this tactic I could find anywhere online.

    Like

  8. Great article, this solution can be applied just for specific view of entity, or it’s applied to for all? Because i don’t find in the code, where you define the only view that you want apply custom filter.

    Like

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 )

Connecting to %s