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/

Why Duplicate Detection job is impractical

Idea of Duplicate Detection Jobs of Dynamics 365 is with great value. It allows you to monitor the number of Duplicate records in a given time: so necessary action could be taken.

Setting a job is very easy because it allows you to select the entity (type with a Duplicate Detection Rules are set) and mention which View is selected to run against. Most importantly scheduling is possible. Once you schedule it, you will get a new report after each execution.

This is where frustration starts. For example, I am trying to monitor how fast duplicate Contacts are being grown by checking a report every week. Below is a sample report. D365 instance I am monitoring already got thousands of duplicates.

First I was surprised to see number 12. Then I realized, 12 is nothing, but the number of records showing in this page. (at least it could have been 10!, what is 12 anyway..)  Then I had to click next and go on. The simple mistake in this window is, it doesn’t show the total number. For me, it was a hectic task click next again and again to check the number of records.

Anyway, I ended up writing a console application to read the number of records in the report. Below is the Fetch Xml I used for it. May be this will help you too.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" >
    <entity name="duplicaterecord" >
        <attribute name="duplicateid" />
        <attribute name="createdon" />
        <order attribute="createdon" descending="true" />
        <filter type="and" >
            <condition attribute="createdon" operator="on-or-after" value="2020-07-03" />
            <condition attribute="createdon" operator="on-or-before" value="2020-07-06" />
        </filter>
        <link-entity name="asyncoperation" from="asyncoperationid" to="asyncoperationid" >
            <attribute name="name" />
            <attribute name="createdon" />
            <filter type="and" >
                <condition attribute="name" operator="like" value="%Weekly Duplicate Monitor Job: Active Contacts%" />
            </filter>
        </link-entity>
    </entity>
</fetch>

Client side Retrieval using Xrm.WebApi - Handling Lookups

One of the previous posts I provided sample codes for different client side operations using Xrm.WebApi. Click this to find it.

Anyway, one of the tricks I couldnt post was how to retrieve a Lookup which is little tricky. Lets check it using with examples.

Here I am trying to retrieve Full Name and Email Address as below.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1

It works fine. Check my result.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1)/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Smith","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8"}

Suppose, I need to retrieve a lookup field value. For example, Created By name. What I feel is just adding it as another field. So I am adding to the end of he field list.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1,createdby

Hmm.. here comes the confusion. I don't get any values for Created By lookup field. I am sure I spelled correctly, otherwise I would get a error message about unknown field.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1,createdby)/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Smith","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8"}

Well, this is the trick WebApi just doesn't return values from other entities. Lookup value is physically in related entity. So you have to extend the query with Expand keyword.

https://xxxx.crm6.dynamics.com/api/data/v9.1/contacts(73bc115c-4317-ea11-a811-000d3a6aa9c8)?$select=fullname,emailaddress1&$expand=createdby($select=fullname,systemuserid)

Here we get the result like a charm.

{"@odata.context":"https://xxxx.crm6.dynamics.com/api/data/v9.1/$metadata#contacts(fullname,emailaddress1,createdby(fullname,systemuserid))/$entity","@odata.etag":"W/\"77570311\"","fullname":"Aaron Bailey","emailaddress1":null,"contactid":"73bc115c-4317-ea11-a811-000d3a6aa9c8","createdby":{"@odata.etag":"W/\"29915606\"","fullname":"Paul Wicks","systemuserid":"267874e4-645c-e911-a870-000d3a6a065c","ownerid":"267874e4-645c-e911-a870-000d3a6a065c"}}

In summery, make sure you link entity with Expand command to retrieve lookup values.

Simple!

Power Automate - Loop through a list of records

Unarguably, It is necessary to learn how to use Power Automate/ Flow instead of Workflows. To be honest, I am sceptical on replacing all the complex workflows we have been developing for many years. In my mind, I try to figure out different approaches to address different requirements. This is the first post from a series I am planning to illustrate what I found. This shows how to loop through a list of records and perform the same action for each.

In my example, I have only three steps as shown below.

I am triggering for any update happen for Account record. This is simple.

Here I am reading a set of records from an entity called Office using a control called List Records (which I rename as OfficeList). One Account can have multiple Offices. Office entity have a lookup field called Main Account (su_mainaccount). Here you can notice how Account Id from triggering step being passed to map with Main Account, so related Office records would be returned.

Now I am using Apply to each control and most importantly I am passing List of records (type of Office) returned from  previous action.

Now, inside the Apply to each control I am creating a Task. In fact, we create task for each record in the list.

Here is one such task being created. Circled text shows the Name of the Office record, where I have passed as a variable in above control. So each office will get their respective names in it.

Hope this helps.

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.

Issue - Disable viewing of recently used items from Lookup

Within many user friendly features introduced in Dynamics 365, is lookup fields of Forms are shown recently worked items while attempting to fill it. This is really a nice feature that help users. Suppose we have a Contact lookup, so we will be shown recently used Contacts beside the field.

Some times we may be designing the system where Contact lookup is to be filled with a Contact of a specific type. Suppose, we use Contact entity to keep both Students and Lecturers of a University. Obviously, Student lookup should not be filled with a Lecturer. In such situations we usually force our look-up to stick in to a specific View. (say. All Active Students View) that filtered to shown only the type we are interested in. Still we have to be careful since recently used records shown, would contain All kinds of Contact.

If your look-up is a filtered one, make sure you disable the feature of showing recently used records. Below illustrates how to do this in field level.

This solves our problem.

Hmm... yet, if we use Business Process Flow in same form, we got an issue. You will notice this Disabling of showing recently used items is not working for the same field in BPF.

Illustration 1 - Click magnifying glass icon of the Lookup field - Shows the correct filtered values.

Illustration 2 - Click the rest of the space of the look-up field - Shows ALL the recently used records.

This is misleading and user may end-up filling a Lecturer in Student field.

I have recently raised a support ticket for this on-behalf of one of my clients. Anyway, MS is aware of this and confirmed its By Design as per now. If more people raised their concern on this, we can expect this to be fixed in a future release. Till then, play safe. Either train the users accordingly OR implement a client-side validation.