FetchXml – Arvind's CRM Blog

Retrieve more than 5000 records in Dynamics 365

We know there is limitation that by default we can get maximum 5000 records in a single web service call but by using paging cookie concept we can get more then 5000 records. Sharing sample code how to use it.

public static List<StudentRecord> ReturnStudentListTest(IOrganizationService service, Int16 pageNumber, Int16 fetchCount)
{
List<StudentRecord> StudentList = new List<StudentRecord>();
string fetchStudent = string.Empty;
string pagingCookie = null;

if (fetchCount == 0)
{
fetchCount = 100;
}
if (pageNumber == 0)
{
pageNumber = 1;
}

fetchStudent = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>” +
” <entity name=’contact’>” +
” <attribute name=’contactid’ />” +
” <attribute name=’fullname’ />” +
” <order attribute=’fullname’ descending=’false’ />” +
” <filter type=’and’>” +
” <condition attribute=’statecode’ operator=’eq’ value=’0′ />” +
” </filter>” +
” </entity>” +
“</fetch>”;

while (true)
{
string fetchXmlWithPaging = Helper.CreateXml(fetchStudent, pagingCookie, pageNumber, fetchCount);
var fetchExpression = new FetchExpression(fetchXmlWithPaging);
EntityCollection studentCollection = service.RetrieveMultiple(fetchExpression);
if (studentCollection.Entities.Count > 0)
{
foreach (var record in studentCollection.Entities)
{
try
{
StudentRecord studentRecord = new StudentRecord();
studentRecord.studentId = record.Contains(CRMConstants.Contact.studentId) ? ((Guid)record.Attributes[CRMConstants.Contact.studentId]).ToString() : string.Empty;
studentRecord.fullname = record.Attributes.Contains(CRMConstants.Contact.fullname) ? record.Attributes[CRMConstants.Contact.fullname].ToString().ToUpper() : String.Empty;
StudentList.Add(studentRecord);
}
catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> ex)
{
throw new Exception(ex.Detail.Message);
}
catch (Exception ex)
{
throw new InvalidCastException(ex.Message);
}
}
if (studentCollection.MoreRecords)
{
pageNumber++;
pagingCookie = studentCollection.PagingCookie;
}
else
break;
}
}
return StudentList;
}

Please refer the below URL for details.
https://msdn.microsoft.com/en-us/library/mt269606.aspx
https://softchief.com/2016/02/20/retrieve-more-than-5000-records-in-dynamics-crm/

Hope it helps.

Retrieve Data Using Web API and FetchXml

Sharing sample code to retrieve data using Web API and using FetchXml to build the query instead of oData. In this example, I am retrieving contact based on email/mobile no and calling function form an entity’s ribbon button to validate duplicate record.

function ValidateContact() {
var mobilePhone = Xrm.Page.getAttribute(“mc_contactnumber”).getValue();
var email = Xrm.Page.getAttribute(“emailaddress”).getValue();
var clientURL = Xrm.Page.context.getClientUrl();
var fetchContact = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>” +
” <entity name=’contact’>” +
” <attribute name=’fullname’ />” +
” <attribute name=’emailaddress1′ />” +
” <attribute name=’mc_mobilephone’ />” +
” <attribute name=’createdon’ />” +
” <attribute name=’ownerid’ />” +
” <order attribute=’fullname’ descending=’false’ />” +
” <filter type=’and’>” +
” <filter type=’or’>” +
” <condition attribute=’mc_mobilephone’ operator=’eq’ value='” + mobilePhone + “‘ />” +
” <condition attribute=’emailaddress1′ operator=’eq’ value='” + email + “‘ />” +
” </filter>” +
” <condition attribute=’statecode’ operator=’eq’ value=’0′ />” +
” </filter>” +
” </entity>” +
“</fetch>”;

To use FetchXml, we need to format FetchXml in usable Web API service endpoint format. We do this by storing the FetchXML in a variable and encoding the string with the encodeURI function native to JavaScrip as below.
var encodedFetchXml = encodeURI(fetchContact);

Another point, if you want formatted values to be included, then you need to set request header like below(This is mainly used to get Lookup, OptionSet, DateTime specific formatted value like lookup text value).
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”*\””);
OR
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”OData.Community.Display.V1.FormattedValue\””);

Please refer the below URL for details.
https://community.dynamics.com/crm/b/mscrmcustomization/archive/2016/10/18/ms-crm-2016-web-api-operations-retrieve-single-or-multiple-records

http://himbap.com/blog/?p=2012

Set Custom View as Default View for Lookup Control

Let’s say we have to set Custom View as Default View of a particular Lookup. Below are the steps to set Custom View as default View.

1. Create a Custom View as per your requirement and Save it.(In my case, Created a custom view of Account which will be shown on contact entity for Primary Customer Field)

2. Get View Id from Browser. 3. Download Fetch XML for your custom view as shown below. In my case, below is the FetchXML query..

4. Format the FetchXML as string variable as below.

    var fetchXml = “<fetch version=’1.0′ “ +
“output-format=’xml-platform’ “ +
” mapping=’logical’ “ +
” distinct=’false’>” +
“<entity name=’account’>” +
    “<attribute name=’name’ />” +
    “<attribute name=’primarycontactid’ />” +
    “<attribute name=’telephone1′ />” +
    “<attribute name=’accountid’ />” +
    “<attribute name=’accountratingcode’ />” +
    “<order attribute=’name’ descending=’false’ />” +
    “<filter type=’and’>” +
     ” <condition attribute=’telephone1′ operator=’eq’ value=’555-0135′ />” +
    “</filter>” +
“</entity>” +
“</fetch>”;
5. Create layoutXml to set Grid properties to display the view as below.
var layoutXml = “<grid name=’resultset’ “ +
“object=’1′ “ +
“jump=’name’ “ +
“select=’1′ “ +
“icon=’1′ “ +
“preview=’1′>” +
“<row name=’result’ “ +
“id=’accountid’>” +
“<cell name=’name’ “ +
“width=’300′ />” +
“<cell name=’primarycontactid’ “ +
“width=’150′ />” +
“<cell name=’telephone1′ “ +
“width=’100′ />” +
“<cell name=’accountratingcode’ “ +
“width=’100′ />” +
“disableSorting=’1′ />” +
“</row>” +
“</grid>”;

6. Call the addCustomView() method of Lookup to set custom view as Default View.

Sets the viewId, viewDisplayName, fetchXml, and layoutXml variables to pass as arguments so that a custom view is added as the default view to the control for the Parent Customer lookup field.

7. Add above steps in a custom function and call it on OnLoad or OnChange as per requirement
function setDefaultCustomView() {
    var viewId = “{00000000-0000-0000-00AA-000010001001}”;
    var viewDisplayName = “Custom Account View”;
    var fetchXml = “<fetch version=’1.0′ ” +
“output-format=’xml-platform’ ” +
” mapping=’logical’ ” +
” distinct=’false’>” +
“<entity name=’account’>” +
“<attribute name=’name’ />” +
“<attribute name=’primarycontactid’ />” +
“<attribute name=’telephone1′ />” +
“<attribute name=’accountid’ />” +
“<attribute name=’accountratingcode’ />” +
“<order attribute=’name’ descending=’false’ />” +
“<filter type=’and’>” +
” <condition attribute=’telephone1′ operator=’eq’ value=’555-0135′ />” +
“</filter>” +
“</entity>” +
“</fetch>”;

var layoutXml = “<grid name=’resultset’ ” +
“object=’1′ ” +
“jump=’name’ ” +
“select=’1′ ” +
“icon=’1′ ” +
“preview=’1′>” +
“<row name=’result’ ” +
“id=’accountid’>” +
“<cell name=’name’ ” +
“width=’300′ />” +
“<cell name=’primarycontactid’ ” +
“width=’150′ />” +
“<cell name=’telephone1′ ” +
“width=’100′ />” +
“<cell name=’accountratingcode’ ” +
“width=’100′ />” +
“disableSorting=’1′ />” +
“</row>” +
“</grid>”;

Xrm.Page.getControl(“parentcustomerid”).addCustomView(viewId, “account”, viewDisplayName, fetchXml, layoutXml, true);

}

Note: Pls change the double quote(“) manually before testing the code.

Using the Advanced Find for FetchXML builder

You can just open the Advanced find page and build the query as you like. Then run the query to see if it returns the data as you wish it should. If you are satisfied with the results and you want to know what query was sent into the CRM Framework, then press F11 to get the address bar and enter this script and press enter.

javascript:alert(resultRender.FetchXml.value);

If you get a warning about leaving the page, just press ‘ok’ and then the query which is sent to the framework opens up in a popup. Unfortunately you cannot select the text to copy and paste. But with Windows XP and Windows Server 2003 you can copy all text on the popup by clicking somewhere on the popup (not on the button “OK” ofcourse) and pressing ctrl+c. Now in notepad you can paste the text of the FetchXML.

Good luck!

You can also try this instead of the alert:

javascript:prompt(“my query:”, resultRender.FetchXml.value);