Counting ALL rows in Dynamics CRM Online web api (ODATA)

3
votes

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?

5
odatadynamics-crmdynamics-crm-onlinedynamics-crm-365dynamics-crm-webapi
Check this: community.dynamics.com/crm/f/117/t/239363 – Arun Vinoth - MVP
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
That’s the idea to explore, you ll end up with 5k limit or 50k limit with fetchxml.. hihaj.com/… – Arun Vinoth - MVP
Aha. Do i put the fetch XML in the URI? – uba2012

5 Answers

4
votes

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)

3
votes

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'])
2
votes

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.

1
votes

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

0
votes

This works:

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