Use Dynamics WebAPI to get Record Count

Use Dynamics WebAPI to get Record Count

When working in an environment where tools like XrmToolbox are not available or allowed getting a total record count can be a real pain sometimes. One way of doing it just using the browser involves using the Dynamics WebAPI. This came in handy last time we were doing a data load on our production system and wanted to see the status of how many records had been imported.

Small Data Set ( < 5k)

If you are trying to get the count for 5,000 or less records. you can do a simple count on the data.

Format

/api/data/v9.0/<Entity Set Name>/?$count=true

Example

/api/data/v9.0/contacts/?$count=true

Data Returned

{"@odata.context":"https://org.crm.dynamics.com/api/data/v9.0/$metadata#contacts","@odata.count":3,"value":

Large Data Set ( > 5k & < 50k)

If your record set will have 5k or more you can utilize the webapi by creating a FetchXml aggregate query, this does have a limitation of 50k records.

Query Format

/api/data/v9.0/<Entity Set Name>?fetchXml=<FetchXml query that has been URI encoded>

FetchXml Format

<fetch version="1.0" mapping="logical" aggregate="true"> <entity name="contact"> <attribute name="contactid" aggregate="count" alias="count" /> </entity> </fetch>

Encoding FetchXml

To encode the FetchXml you can open the Console window in your browser and utilize the encodeURI JavaScript function.

encodeURI('<fetch version="1.0" mapping="logical" aggregate="true"><entity name="contact"><attribute name="contactid" aggregate="count" alias="count" /></entity></fetch>')

Example

/api/data/v9.0/contacts?fetchXml=%3Cfetch%20version=%221.0%22%20mapping=%22logical%22%20aggregate=%22true%22%3E%3Centity%20name=%22contact%22%3E%3Cattribute%20name=%22contactid%22%20aggregate=%22count%22%20alias=%22count%22%20/%3E%3C/entity%3E%3C/fetch%3E

Data Returned

{"@odata.context":"https://org6744e6cd.crm.dynamics.com/api/data/v9.0/$metadata#contacts","value":[{"count":3}]}

Comments

  1. MetricoidJanuary 12, 2021 at 4:13 AM

    I found your blog on Google and read a few of your other posts. I just added you to my Google News Reader. You can also visit API Development And Integration for more Metricoid Technology Solutions related information and knowledge, Keep up the great work Look forward to reading more from you in the future.


    ReplyDelete
  2. Lakshya PareekBRAugust 20, 2021 at 9:13 AM

    This comment has been removed by the author.

    ReplyDelete

Post a Comment

Popular posts from this blog

Add User As Local Administrator On Domain Controller

I recently was settting up a new Microsoft SharePoint 2010 machine and had promoted the machine to a domain controller before creating my SharePoint admin accounts.  I needed to add several of my accounts to the local Administrators group.  Unfortunately after you promote a server to a domain controller you can no longer access the GUI for Local Users and Groups.  Instead I had to use the command line to add the users. Open a command promt using the "Run as administrator" function and then run the following command. net localgroup Administrators /add {domain}\{user} Note: do not include the {} brackets.
Read more

How to Create SharePoint Items with Power Automate Desktop

Image
Overview Power Automate Desktop is a great way to automate many of your daily task so you can focus on real work. A prime example of this is getting data from one place to another, especially when those data sources do not have an API such as a legacy desktop application or a file. In this example I demonstrate several ways in which an Excel sheet containing fictitious customer data could be loaded into a SharePoint list. In order to do this I first generated some fake data for my customers using Mockaroo . I then created a new SharePoint list and added some columns to match my spreadsheet. Methods My original plan was to just use the PAD recorder , which I did, but after creating that Flow I decided to find other possible ways to accomplish the task. The list below is not a complete list but should provide some ideas. The flexibility of PAD allows for an even wider range of possibilities for carrying out tasks such as these. At the end of each section I have placed the s...
Read more

Calling Dataverse Web API in PowerShell using Client Credentials

Image
Connecting to Dataverse using PowerShell can be very helpful for data migrations and use within Azure DevOps. Connecting to an instance in a non-interactive way can be tricky though. This article will provide you the links you need for creation and App registration and adding the app user to your environment. You can then utilize the script provided to call Web API requests including ones you define using the new Custom API functionality now available. The script was written so that it is not dependent on any outside libraries such as the Microsoft.Xrm.Tooling connector. This is helpful in situation where involving an outside library will slow down your deployment time by having to be approved in a change control board. If utilizing an outside code library is not a concern you can create a connection to Dataverse utilizing the Microsoft.Xrm.Tooling connector Get-CrmConnection cmdlet. Create App Registration The first thing to do is create an App Registration within Azure AD for ...
Read more