How to manage ODATA 50 records limitation

Hi Guys,

When we use javascript in Dynamics CRM webresources we often ask this question : Did I use SOAP or ODATA ?
For those who have chosen ODATA, when they need to retrieve many recods they are quickly confronted with a classic problem . This problem is : Your resultset contains only 50 records and if you need to get the 200 accounts that you need you should use pagination.

There are three ways to manage this limitation:
- First One: is to accept it and to use pagination to get your data.
In this case, here is the way to use pagination in ODATA queries.

You can create an array to get all records like this array : relatedAccounts[] and a javascript function called GetRecords(url) with a parameter containning the url of your ODATA Request.

The callback success  of the ajax method of jQuery library retrieve the first page of the resultset.

But if you __next member of the data.d object you will got the url of the next page of your resultset.

So, Now you should call the same method GetRecords by passing the new url. Look at this javascript source code :

1. relatedAccounts = [];
2.  
3. function GetAllAccountsRecords() {
4.     var serverUrl = Xrm.Page.context.getServerUrl();
5.     var oDataUri = serverUrl + "/xrmservices/2011/OrganizationData.svc/AccountSet?$select=AccountId,Name,&$filter=StateCode/Value eq 0";
6.     GetRecords(oDataUri);
7.     var totalRecords = relatedAccounts.length;
8. }
9.  
10. function GetRecords(url) {
11.     jQuery.ajax({
12.         type: "GET",
13.         contentType: "application/json; charset=utf-8",
14.         datatype: "json",
15.         url: url,
16.         async: false,
17.         beforeSend: function (XMLHttpRequest) {
18.             XMLHttpRequest.setRequestHeader("Accept", "application/json");
19.         },
20.         success: function (data, textStatus, XmlHttpRequest) {
21.             if (data && data.d != null && data.d.results != null) {
22.                 AddRecordsToArray(data.d.results);
23.                 FetchRecordsCallBack(data.d);
24.             }
25.         },
26.         error: function (XmlHttpRequest, textStatus, errorThrown) {
27.             alert("Error :  has occured during retrieval of the records ");
28.         }
29.     });
30. }
31.  
32. function AddRecordsToArray(records) {
33.     for (var i = 0; i < records.length; i++) {
34.         relatedAccounts.push(records[i]);
35.     }
36. }
37.  
38. function FetchRecordsCallBack(records) {
39.     if (records.__next != null) {
40.         var url = records.__next;
41.         GetRecords(url);
42.     }
43. }

That means that you use a reccursive method. This method will stop executing when the data.d.__next = null.

When this method stops, you will got all your records in the relatedAccounts array.

- Second One: is to say no I should increase this limitation.
In this case, It's possible but if you increase this limitation to 250 you can got a retrieve that returns 300 records for example and you will be in the same case of 50 records.
But if you say no matter I need to increase it to 250 you have to modify MaxResultsPerCollection property in ServerSettings configuration table in MSCRM_Config database.

You should know that this modification of server settings impact all the server CRM organizations.

Now you can do it using Deployment Service like this :


Or you can do it also by PowerShell cmdlets like this :

1. Add-PSSnapin Microsoft.Crm.PowerShell
2. $setting = New-Object "Microsoft.Xrm.Sdk.Deployment.ConfigurationEntity"
3. $setting.LogicalName = "ServerSettings"
4. $setting.Attributes = New-Object "Microsoft.Xrm.Sdk.Deployment.AttributeCollection"
5. $attribute = New-Object "System.Collections.Generic.KeyValuePair[String, Object]" ("MaxResultsPerCollection", 250)
6. $setting.Attributes.Add($attribute)
7. Set-CrmAdvancedSetting -Entity $setting

After executing this cmdlets run IISRESET ou restart IIS service because these kind of settings are cached.

By the way ServerSettings table contains many other settings that you can show in this link : http://msdn.microsoft.com/en-us/library/gg334675.aspx


- Third One: is to get only 50 records and you show a message to say: This resultset is capped with 50 records you should use more filters to refine search.
That's all :)