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

Question

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?

Lineman

Check this: community.dynamics.com/crm/f/117/t/239363 – Christoper

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. – Lineman

That’s the idea to explore, you ll end up with 5k limit or 50k limit with fetchxml.. hihaj.com/… – Christoper

Aha. Do i put the fetch XML in the URI? – Lineman

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. – Burkes

Answer 1

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

Cand

It worked for me, only one comment, it will accept bare entity names, so account not accounts. – Gennagennaro

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. – Vermiform

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... – Maunsell

Answer 2

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)

Christoper

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. – Lineman

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. – Lineman

@Lineman 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. – Christoper

I used fetchxml. I am still surprised that it is not possible to do without using fetchxml. – Lineman

@Lineman whole community is waiting for MS to give a direct service method to get this :) – Christoper

@Lineman Looks like MS delivered the direct function in v9.1 :) – Christoper

Answer 3

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.

Keeshakeeshond

Answer 4

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

Illbehaved

Sorry OData v4 does not support that – Lineman

Answer 5

This works:

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

Tallinn

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. – Musketry