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

(0) Share

Report

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

I have the same question (0)

All responses (8)

Answers (0)

Suggested answer

Community Member on at

Like (0)

Report

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());

}

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

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?

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (1)

Report

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.

Was this reply helpful? Yes No
arif imteyaz 375 on at

Like (0)

Report

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;

}

Was this reply helpful? Yes No
arif imteyaz 375 on at

Like (1)

Report

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;

}

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Suggested answer

Nuno 2,005 on at

Like (1)

Report

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

Was this reply helpful? Yes No