Total Number of Record : RetrieveTotalRecordCount

A very common request by customer is to get total number of record in D365 system for a given entities. It is pretty straight forward for D365 On Prem, you can do a count on SQL DB. But for D365 online you either need to rely on XrmToolBox fetch counter(which can still be used) or aggregrate function or even on DES database. However we do have a simple way now : RetrieveTotalRecordCount

I have all the entities in my system stored in a text file. The C# code to get count of all the entities will be like this:

            string[] entitylist = File.ReadLines(@"D:\entitylist.txt").ToArray();
            var client = new CdsServiceClient(connectionString);// I am using .NET core. Change it to var client = new CrmServiceClient(connectionString);           
            RetrieveTotalRecordCountRequest req = new RetrieveTotalRecordCountRequest
            {
                EntityNames = entitylist
            };
            EntityRecordCountCollection m = ((RetrieveTotalRecordCountResponse)client.Execute(req)).EntityRecordCountCollection;
            long count = 0;
            foreach (var i in m)
            {
                Console.WriteLine(i.Key + " =" + i.Value);
                count += i.Value;
            }
            Console.WriteLine(@$"Count ={count}");

I was having around 600 entitites with 200 million records in total and it took 2 second to get the result. So pretty sure its doing SQL count underneath.

If you are using POSTMAN – just use below url

GET {{webapiurl}}/RetrieveTotalRecordCount(EntityNames=[‘contact’,’account’])

and it ifs JS then just change pass the same request as above using webAPI.

function GetTotalCount(executionContext)
{
	var formContext = executionContext.getFormContext();
    var req = new XMLHttpRequest();
    req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v9.1/RetrieveTotalRecordCount(EntityNames="+JSON.stringify(['contact','account']) +")", true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function() {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
                for (var i = 0; i < results.EntityRecordCountCollection.Count; i++) {
                    var value = results.EntityRecordCountCollection.Values[i];
                    alert(results.EntityRecordCountCollection.Keys[i] + " = "+ value);
                }
            } else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }
    };
    req.send();
}

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/retrievetotalrecordcount?view=dynamics-ce-odata-9

We still dont have a way to apply a filter and then get the count directly. Hoping to get this feature in future.

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

%d bloggers like this: