Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How to get all the rec...
Microsoft Dynamics CRM (Archived)

How to get all the records with count using Webapi call in MS CRM?

(0) ShareShare
ReportReport
Posted on by Community Member

Hi,

I want to get following things:

1- Get list of all the records

As we know CRM provide fix number of records in single call. I don't know what is exact count. I want to get all the records even records are 1000000000.

Here is example what I use currently:

function test()
{
          //call
          var getAllRecords = getAPIRecords(MapEntityUri);
}

//common function
function getAPIRecords(Query) {

          var data;
          var req = new XMLHttpRequest();
          req.open("GET", serverUrl + "/api/data/v8.0/" + Query, false);
          req.setRequestHeader("Accept", "application/json");
          req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
          req.setRequestHeader("OData-MaxVersion", "4.0");
          req.setRequestHeader("OData-Version", "4.0");
          //req.setRequestHeader("Prefer", "odata.include-annotations=OData.Community.Display.V1.FormattedValue");
          req.setRequestHeader("Prefer", "odata.include-annotations=*");
          req.onreadystatechange = function () {
          if (this.readyState == 4 /* complete */) {
          req.onreadystatechange = null;
          if (this.status == 200) {
                    data = JSON.parse(this.response);
          }
          else {
                    var error = JSON.parse(this.response).error;
                    alert(error.message);
          }
          }
          };
          req.send();
          return data;

}

Can anybody please suggest me any link or solution?

2- How to get count with filter?

I want to get count of records on the base of filter. Please consider above scienario also if there are large number of records.

Can anybody please suggest?

*This post is locked for comments

  • Suggested answer
    Nuno Profile Picture
    Nuno 2,005 on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    Hi,

    You could build an fetchXml using the count aggregate

    msdn.microsoft.com/.../gg309565.aspx

    As the aggregate cannot be performed over 50000 records, you cloud get the records grouped by the created by day, month and year ... this way you probably wont get reach that limit msdn.microsoft.com/.../gg309565.aspx

    For example, the following fetch gets the count of active accounts created by each day ... then to get all the active accounts just sum all the results.

    <fetch distinct='false' mapping='logical' aggregate='true'>
    <entity name='account'>
    <attribute name='accountid' alias='account_count' aggregate='count'/>
    <attribute name='createdon' groupby='true' dategrouping='day' alias='day' />
    <attribute name='createdon' groupby='true' dategrouping='month' alias='month' />
    <attribute name='createdon' groupby='true' dategrouping='year' alias='year' />
    <filter type='and'>
    <condition attribute='statecode' operator='eq' value='0' />
    </filter>
    </entity>
    </fetch>

    And to execute custom FetchXML queries with webapi ...

    msdn.microsoft.com/.../mt607533.aspx

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    You'd have to cache the records and save them over pages ("paging"). Not sure how to do this with a javascript web call, but I've done it with C#.

    However have you tested retrieving the posts with the webapi call in your post? I think it should be able to retrieve more than the 5000 limited elsewhere.

  • arif imteyaz Profile Picture
    arif imteyaz 375 on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    Try this

    private static decimal FetchResult_Vision_exp(IOrganizationService service, Guid id)

           {

               //, pname, "boc_objectiveid", "boc_total"

               var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";

               fetchXml += "<entity name='boc_strategic'>";

               fetchXml += "<attribute name='boc_strategicid' />";

               fetchXml += "<attribute name='boc_total' />";

               fetchXml += "<attribute name='boc_acbug' />";

               fetchXml += "<attribute name='boc_exbug' />";

               fetchXml += "<filter type='and'>";

               fetchXml += "<condition attribute='boc_vision' operator='eq' value='" + id + "' />";

               fetchXml += "<condition attribute='statecode' operator='eq' value='Active' />";

               fetchXml += "</filter>";

               fetchXml += "</entity>";

               fetchXml += "</fetch>";

               decimal TotalValue = 0;

               int no = 0;

               var result = service.RetrieveMultiple(new FetchExpression(fetchXml));

               if (result.Entities.Count > 0)

               {

                   foreach (var c in result.Entities)

                   {

                       no = result.Entities.Count;

                       if (c.Contains("boc_exbug"))

                           TotalValue += ((Money)c.Attributes["boc_exbug"]).Value;

                   }

               }

               return TotalValue;

           }

  • arif imteyaz Profile Picture
    arif imteyaz 375 on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    Try this

    private static decimal FetchResult_Vision_exp(IOrganizationService service, Guid id)

           {

               //, pname, "boc_objectiveid", "boc_total"

               var fetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>";

               fetchXml += "<entity name='boc_strategic'>";

               fetchXml += "<attribute name='boc_strategicid' />";

               fetchXml += "<attribute name='boc_total' />";

               fetchXml += "<attribute name='boc_acbug' />";

               fetchXml += "<attribute name='boc_exbug' />";

               fetchXml += "<filter type='and'>";

               fetchXml += "<condition attribute='boc_vision' operator='eq' value='" + id + "' />";

               fetchXml += "<condition attribute='statecode' operator='eq' value='Active' />";

               fetchXml += "</filter>";

               fetchXml += "</entity>";

               fetchXml += "</fetch>";

               decimal TotalValue = 0;

               int no = 0;

               var result = service.RetrieveMultiple(new FetchExpression(fetchXml));

               if (result.Entities.Count > 0)

               {

                   foreach (var c in result.Entities)

                   {

                       no = result.Entities.Count;

                       if (c.Contains("boc_exbug"))

                           TotalValue += ((Money)c.Attributes["boc_exbug"]).Value;

                   }

               }

               return TotalValue;

           }

  • Suggested answer
    Aric Levin Profile Picture
    Aric Levin 30,188 Moderator on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    The 5000 record limit cannot be modified in CRM Online. You will only be able to retrieve 5000 records at a time.

    In CRM On-Premise this can be modified with registry and database configuration directly, but not a supported configuration.

    You will have to get 5000 records at a time, and manipulate the data as you wish after that.

    You can get the total number of records using a separate aggregate api call:

    stackoverflow.com/.../returning-count-for-an-odata-query

    Hope this helps.

  • Community Member Profile Picture
    Community Member on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    Hi Erik,

    If I have large number of records(let's say 100,000), will my code return all the records?

    If no, how can I get all the records?

  • Community Member Profile Picture
    Community Member on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    I don't think the WebApi returns the 5000 post limit as previous fetches did. I don't think you need to page the results or anything like that.

  • Suggested answer
    Community Member Profile Picture
    Community Member on at
    RE: How to get all the records with count using Webapi call in MS CRM?

    You're using data = JSON.parse(this.response);

    this should return an object array you can call length on

    e.g.

    if (this.status == 200) {

    data = JSON.parse(this.response);

    alert(data.length.toString());

    }

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,430 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans