4

Is it possible to count all rows in a given entity, bypassing the 5000 row limit and bypassing the pagesize limit?

I do not want to return more than 5000 rows in one request, but only want the count of all the rows in that given entity.

According to Microsoft, you cannot do it in the request URI: 

The count value does not represent the total number of entities in the system. 

It is limited by the maximum number of entities that can be returned.

I have tried this: 

GET [Organization URI]/api/data/v9.0/accounts/?$count=true

Any other way?

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
uba2012
  • 373
  • 1
  • 3
  • 14
  • Check this: https://community.dynamics.com/crm/f/117/t/239363 – Arun Vinoth-Precog Tech - MVP Jul 26 '18 at 17:59
  • Hmm. that does not actually answer my question. I just want to count them, isn't there a way of doing it through the URI. – uba2012 Jul 26 '18 at 18:03
  • That’s the idea to explore, you ll end up with 5k limit or 50k limit with fetchxml.. https://hihaj.com/retrieve-the-accurate-total-count-of-entities-via-dynamics-crm-web-api-6e777fae650d – Arun Vinoth-Precog Tech - MVP Jul 26 '18 at 18:05
  • Aha. Do i put the fetch XML in the URI? – uba2012 Jul 26 '18 at 18:35
  • This is a devastating showcase for the design principle violation of do not let the implementation leak to interface. There should be only one method for counting the elements in list, not 3 methods that you need to use if the result varies. – ant Sep 02 '22 at 06:30

5 Answers5

7

Use function RetrieveTotalRecordCount:

If you want to retrieve the total number of records for an entity beyond 5000, use the RetrieveTotalRecordCount Function.

Your query will look like this:

https://<your api url>/RetrieveTotalRecordCount(EntityNames=['accounts'])
Pat
  • 411
  • 5
  • 4
  • 1
    It worked for me, only one comment, it will accept bare entity names, so `account` not `accounts`. – shytikov Aug 19 '20 at 11:31
  • 1
    it should be note that this gives a response calculated from a snapshot taken some time in the last 24 hours, not a live value. – Andrew Hill Sep 26 '22 at 05:25
  • Documentation: `The data retrieved will be from a snapshot within the last 24 hours.` ... so not really reliable/useful? Name an org where you *know* the number of Accounts doesn't change in 24 hours... – Don Cheadle Feb 23 '23 at 23:01
6

Update:

Latest release v9.1 has the direct function to achieve this - RetrieveTotalRecordCount

————————————————————————————

Unfortunately we have to pick one of this route to identify the count of records based on expected result within the limits.

1. If less than 5000, use this: (You already tried this) 

GET [Organization URI]/api/data/v9.0/accounts/?$count=true

2. Less than 50,000, use this: 

GET [Organization URI]/api/data/v8.2/accounts?fetchXml=[URI-encoded FetchXML query]

Exceeding limit will get error: AggregateQueryRecordLimit exceeded. Cannot perform this operation.

Sample query:

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

Do a browser address bar test with URI:

[Organization URI]/api/data/v8.2/accounts?fetchXml=%3Cfetch%20version=%221.0%22%20mapping=%22logical%22%20aggregate=%22true%22%3E%3Centity%20name=%22account%22%3E%3Cattribute%20name=%22accountid%22%20aggregate=%22count%22%20alias=%22count%22%20/%3E%3C/entity%3E%3C/fetch%3E

The only way to get around this is to partition the dataset based on some property so that you get smaller subsets of records to aggregate individually.

Read more

3. The last resort is iterating through @odata.nextLink and counting the records in each page with a code variable (code example to query the next page)

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • Hi Arun, the last solution is fine but requires that I request all the rows, which would be overkill as I am only interested in the count. – uba2012 Jul 26 '18 at 19:50
  • To elaborate: my goal is to count the amount of record in an entity then afterward load them into a database. I need to make sure that no record is missed between the initial count and the insert to database. – uba2012 Jul 26 '18 at 19:51
  • @uba2012 I agree its an overkill thats why its last resort. You can go batch by batch using some filter like account starts with 'A', 'B' then 'C', 'D' or based on created months, etc. Also you can put try catch, increment the counter whenever insert fails. – Arun Vinoth-Precog Tech - MVP Jul 26 '18 at 19:58
  • I used fetchxml. I am still surprised that it is not possible to do without using fetchxml. – uba2012 Jul 26 '18 at 21:56
  • 2
    @uba2012 whole community is waiting for MS to give a direct service method to get this :) – Arun Vinoth-Precog Tech - MVP Jul 26 '18 at 22:54
  • @uba2012 Looks like MS delivered the direct function in v9.1 :) – Arun Vinoth-Precog Tech - MVP Apr 28 '20 at 13:55
2

The XrmToolBox has a counting tool that can help with this .

Also, we here at MetaTools Inc. have just released an online tool called AggX that runs aggregates on any number of records in a Dynamics 365 Online org, and it's free during the beta release.

Aron
  • 3,877
  • 3
  • 14
  • 21
1

You may try OData's $inlinecount query option. Adding only $inlinecount=allpages in the querystring will return all records, so add $top=1 in the URI to fetch only one record along with count of all records.

You URL will look like /accounts/?$inlinecount=allpages&$top=1

For example, click here and the response XML will have the count as <m:count>11</m:count>

Note: This query option is only supported in OData version 2.0 and above

Vinit
  • 2,540
  • 1
  • 15
  • 22
0

This works:

[Organization URI]/api/data/v8.2/accounts?$count

dataguy
  • 1
  • 1
    As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Sep 29 '21 at 11:06