Can Fetch Xml Builder be used to extend the limitations of Dataverse views ?

Base of Views in Dataverse is actually fetchxml. Views got limitations. Not all the features of Fetchxml being used in creation of views. In other hand Fetchxml builder allows filter data based on complex fetchxmls and it allows creating views in Dataverse based on them. Someone would thing, just as I did, this can be a way to views with more complex criteria. Hm.. lets find out. 

Lets take this example. We have below hierarchy of entities.

Out requirement is to create a view of Sub Depots where Account Category = Distribution and you need to show both Sub Depots against the Account Name.

Lets try this in Dataverse OOB capabilities. 

Though you can easily set the criteria, you will not be able to show the Account name since Account entity is 3 hops away as per the given hierarchy of entities. You are only able to show just two levels. In this case, Sub Depots and Depots.

Lets try Fetch Xml Builder of Xrm Tool Box.

And Results showing without any issue.

Now we will try our next step of transferring this same Fetch to Dataverse. 

Now select Save View as option;

Then you get below option to select if you need a personal or system view.

Then you are allowed to give a name to the new view.

View is now created.

Lets try the view now in Dataverse. When you check the fields you will see Dataverse is throwing a error saying these Account fields to be removed,

If you try to run as it is, it will through below error message.

Conclusion

NO! 

You cannot use Fetch Xml Builder to create Views in Dataverse that extends the limitations.

Handling the 5000 limit of FetchXml retrievals

When we retrieve records with FetchXml, most annoying constrain is limit of records it returns which is 5000. Here, we are checking how to overcome this limitation.

Paging solution

If you browse the web, you will find a lot of solutions using paging. Which is pretty cool. I am showing here one of the good codes I tried. This worked for me. For example I am retrieving all the active contacts in my CRM.

internal static void MainFunction(IOrganizationService service)
        {
            string fetchXml = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
                                        <entity name='contact' >
                                            <attribute name='firstname' />
                                            <attribute name='lastname' />
                                            <filter>
                                                <condition attribute='statecode' operator='eq' value='0' />
                                            </filter>
                                        </entity>
                                       </fetch>");

            List<Entity> contacts = GetTotalRecordsfromFetch(fetchXml, service);
            Console.WriteLine("contacts : " + contacts.Count);
         }
        
        public static List<Entity> GetTotalRecordsfromFetch(string fetchXML, IOrganizationService orgService)
        {
            List<Entity> lstEntity = new List<Entity>();
            int fetchCount = 5000;
            int pageNumber = 1;
            string pagingCookie = null;

            while (true)
            {
                string xml = CreateXml(fetchXML, pagingCookie, pageNumber, fetchCount);
                RetrieveMultipleRequest fetchRequest = new RetrieveMultipleRequest
                {
                    Query = new FetchExpression(xml)
                };

                var returnCollections = ((RetrieveMultipleResponse)orgService.Execute(fetchRequest)).EntityCollection;

                if (returnCollections.Entities.Count >= 1)
                {
                    lstEntity.AddRange(returnCollections.Entities);
                }

                if (returnCollections.MoreRecords)
                {
                    pageNumber++;

                    pagingCookie = returnCollections.PagingCookie;
                }
                else
                {
                    break;
                }
            }
            return lstEntity;
        }

        public static string CreateXml(string xml, string cookie, int page, int count)
        {
            StringReader stringReader = new StringReader(xml);
            XmlTextReader reader = new XmlTextReader(stringReader);

            XmlDocument doc = new XmlDocument();
            doc.Load(reader);

            XmlAttributeCollection attrs = doc.DocumentElement.Attributes;

            if (cookie != null)
            {
                XmlAttribute pagingAttr = doc.CreateAttribute("paging-cookie");
                pagingAttr.Value = cookie;
                attrs.Append(pagingAttr);
            }

            XmlAttribute pageAttr = doc.CreateAttribute("page");
            pageAttr.Value = System.Convert.ToString(page);
            attrs.Append(pageAttr);

            XmlAttribute countAttr = doc.CreateAttribute("count");
            countAttr.Value = System.Convert.ToString(count);
            attrs.Append(countAttr);

            StringBuilder sb = new StringBuilder(1024);
            StringWriter stringWriter = new StringWriter(sb);

            XmlTextWriter writer = new XmlTextWriter(stringWriter);
            doc.WriteTo(writer);
            writer.Close();

            return sb.ToString();
        }

Problem with Aggregate

Above solution doesn't work when u need to Aggregate records in FetchXml. For example I need to run below FetchXml to get count of Accounts group by Primary Contacts. Paging doesn't work here.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true" >
  <entity name="account" >
    <attribute name="accountid" alias="AccountId" aggregate="count" />
    <attribute name="primarycontactid" alias="PrimaryContactid" groupby="true" />
    <filter>
      <condition attribute="statecode" operator="eq" value="0" />
    </filter>
  </entity>
</fetch>

At last, what I had to do was, not so pretty, to retrieve values by chunks. For example, below I am looping though the alphabet so that I am executing the fetch 26 times for each letter where contact name is starting from particular letter. 

internal static void MainFunction(IOrganizationService service)
        {
            const string alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            foreach (char ch in alphabet)
            {
                string fetchXml = string.Format(@"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true' >
                                      <entity name='account' >
                                        <attribute name='accountid' alias='AccountId' aggregate='count' />
                                        <attribute name='primarycontactid' alias='PrimaryContactid' groupby='true' />
                                        <filter>
                                          <condition attribute='statecode' operator='eq' value='0' />
                                          <condition attribute='primarycontactidname' operator='like' value='{{0}}' />
                                        </filter>
                                      </entity>
                                    </fetch>");
                string fetchXmlCompiles = string.Format(fetchXml, ch + "%");

                EntityCollection accounts = service.RetrieveMultiple(new FetchExpression(fetchXmlCompiles));
                Console.WriteLine(accounts.Entities.Count);
            }
            Console.ReadLine();
         }

This is not the best, yet I don't know better way of doing this. If you find one, pl don't forget to comment below.

Reference;

https://nishantrana.me/2017/09/25/sample-code-to-retrieve-more-than-5000-records-using-fetchxml-in-crm/

Passing FetchXml to Xrm.WebApi

Previously we explained how to do basic operations using Xrm.WebApi. Please refer this for relevant post.

What we didn't mention there is passing of FetchXml. Pass a FetchXml to retrive a specific set of records is a very useful operation. Lets check below scenario.

In this fictitious system, we have custom entity called Office that is associated to Account and contains two custom fields called Territory and Type. While we are in Account, suppose we need to select all the associate offices where Territory is Asia-Pacific and Type is Regional. So, this is pretty realistic requirement. If we try to do only with basic operations, it may be complex and would require many server-calls which is not ideal.

This is a scenario that easily achieved with FetchXml. You can easily download the FetchXml through Advanced Find as shown below;

Find below code snippet to understand how we pass this to WebApi;

And this online tool help you with converting your FetchXml to a string that could be used in Java Script: https://www.ashishvishwakarma.com/FetchXmlFormatter/

function onAccountLoad(executionContext) {

    formContext = executionContext.getFormContext();
    var accountId = formContext.data.entity.getId();

    var fetchXML = new String();
    fetchXML += "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";
    fetchXML += "    <entity name='su_office'>";
    fetchXML += "        <attribute name='su_officeid' />";
    fetchXML += "        <attribute name='su_name' />";
    fetchXML += "        <attribute name='createdon' />";
    fetchXML += "        <order attribute='su_name' descending='false' />";
    fetchXML += "        <filter type='and'>";
    fetchXML += "           <condition attribute='su_mainaccountid' operator='eq' value='" + accountId + "' />";
    fetchXML += "            <condition attribute='su_territoryid' operator='eq' value='{A25642F7-61EF-E411-80EB-C4346BACE124}' />";
    fetchXML += "            <condition attribute='su_type' operator='eq' value='100000001' />";
    fetchXML += "        </filter>";
    fetchXML += "    </entity>";
    fetchXML += "</fetch>";

    Xrm.WebApi.online.retrieveMultipleRecords("su_office", "?fetchXml=" + encodeURIComponent(fetchXML)).then(
        function success(result) {
            var eligibleOffices = result.entities;
            if (eligibleOffices != null && eligibleOffices.length > 0) {

                for (i = 0; i < eligibleOffices.length; i++) {
                    // Do the logic
                    alert(eligibleOffices[i].su_name);
                }
            }
        },
        function (error) {
        }
    );
}

Hope this is helpful.