With Web
API in MS CRM, we are retrieve data using FetchXML.
Easy way to
construct FetchXML queries are using advanced find in CRM, you can create query
in advanced find, add columns, define sorting and then download FetchXML.
Once your
FetchXML is ready, you can use this FetchXML in web API to retrieve data from
CRM.
Here is
sample code for how to execute FetXML and get values from FetchXML result set
ExecuteFetchXML: function () {
var accountFetchXML = ['< fetch version="1.0"
output-format="xml-platform" mapping="logical"
distinct="false">' +
'< entity name="account">' +
'< attribute name="name" />' +
'< attribute name="accountid" />' +
'< attribute name="numberofemployees"
/>' +
'< attribute name="processid" />' +
'< attribute name="donotpostalmail"
/>' +
'< attribute name="statuscode" />' +
'< attribute name="statecode" />' +
'< attribute name="telephone1" />' +
'< attribute name="ownerid" />' +
'< attribute name="description" />' +
'< attribute name="primarycontactid"
/>' +
'< attribute name="address1_longitude"
/>' +
'< attribute name="exchangerate" />' +
'< attribute name="createdon" />' +
'< attribute name="openrevenue" />' +
'< attribute name="industrycode" />' +
'< attribute name="shippingmethodcode"
/>' +
'< order attribute="name"
descending="false" />' +
'< filter type="and">' +
'< condition attribute="industrycode"
operator="not-null" />' +
'</
filter>' +
'< link-entity name="contact"
from="contactid" to="primarycontactid"
visible="false" link-type="outer"
alias="prmContact">' +
'< attribute name="telephone3" />' +
'<
attribute name="spousesname" />' +
'< attribute name="ownerid" />' +
'< attribute name="gendercode" />' +
'< attribute name="description" />' +
'< attribute name="createdby" />' +
'< attribute name="address1_latitude"
/>' +
'< attribute name="exchangerate" />' +
'< attribute name="createdon" />' +
'< attribute name="annualincome" />' +
'</ link-entity>' +
'</ entity>' +
'</ fetch>'].join('');
var encodedFetchXML =
encodeURIComponent(accountFetchXML);
var req = new XMLHttpRequest();
req.open("GET",
Xrm.Page.context.getClientUrl() + "/api/data/v8.0/accounts?fetchXml=" + encodedFetchXML, true);
req.setRequestHeader("OData-MaxVersion", "4.0");
req.setRequestHeader("OData-Version", "4.0");
req.setRequestHeader("Accept", "application/json");
req.setRequestHeader("Prefer", "odata.include-annotations=\"OData.Community.Display.V1.FormattedValue\"");
req.onreadystatechange = function () {
if (this.readyState === 4) {
req.onreadystatechange
= null;
if (this.status === 200) {
var results = JSON.parse(this.response);
for (var i = 0; i <
results.value.length; i++) {
var accountDetails =
results.value[i];
//Single Line Text
var nameValue = accountDetails['name'];
var telephone1Value =
accountDetails['telephone1'];
//UniqueIdentifier
var accountidValue =
accountDetails['accountid'];
var processidValue =
accountDetails['processid'];
//Whole Number
var numberofemployeesValue =
accountDetails['numberofemployees'];
//Two Option
var donotpostalmailValue =
accountDetails['donotpostalmail'];
var donotpostalmailTextValue =
accountDetails['donotpostalmail@OData.Community.Display.V1.FormattedValue'];
//Status Reason
var statuscodeValue =
accountDetails['statuscode'];
var statuscodeTextValue =
accountDetails['statuscode@OData.Community.Display.V1.FormattedValue'];
//Status
var statecodeValue =
accountDetails['statecode']; //Status
var statecodeTextValue = accountDetails['statecode@OData.Community.Display.V1.FormattedValue'];
//Owner
var owneridValue =
accountDetails['_ownerid_value'];
var OwnerName = accountDetails["_ownerid_value@OData.Community.Display.V1.FormattedValue"]
//Multiple Line
var descriptionValue =
accountDetails['description'];
//Lookup
var primarycontactidValue=
accountDetails['_primarycontactid_value'];
var primarycontactidName =
accountDetails['_primarycontactid_value@OData.Community.Display.V1.FormattedValue'];
//Floating Point Number
var address1_longitudeValue= accountDetails['address1_longitude'];
//Decimal
var exchangerateValue =
accountDetails['exchangerate'];
//Date Time
var createdonValue =
accountDetails['createdon'];
//Currency
var openrevenueValue =
accountDetails['openrevenue'];
//Option set
var industrycodeValue =
accountDetails['industrycode'];
var industrycodeTextValue =
accountDetails['industrycode@OData.Community.Display.V1.FormattedValue'];
//Contact - Linked entity data
//Single Line Text
var prmContact_telephone3Value =
accountDetails['prmContact_x002e_telephone3'];
var prmContact_spousesnameValue
= accountDetails['prmContact_x002e_spousesname'];
//Owner
var prmContact_owneridValue =
accountDetails['prmContact_x002e_ownerid'];
//Option set
var prmContact_gendercodeValue =
accountDetails['prmContact_x002e_gendercode'];
var
prmContact_gendercodeTextValue = accountDetails['prmContact_x002e_gendercode@OData.Community.Display.V1.FormattedValue'];
//Multiple Line
var prmContact_descriptionValue
= accountDetails['prmContact_x002e_description'];
//Lookup
var prmContact_createdbyValue =
accountDetails['prmContact_x002e_createdby'];
//Floating Point Number
var prmContact_address1_latitudeValue = accountDetails['prmContact_x002e_address1_latitude'];
//Decimal
var prmContact_exchangerateValue
= accountDetails['prmContact_x002e_exchangerate'];
//Date Time
var prmContact_createdonValue =
accountDetails['prmContact_x002e_createdon'];
//Currency
var prmContact_annualincomeValue
= accountDetails['prmContact_x002e_annualincome'];
}
}
else {
alert(this.statusText);
}
}
};
req.send();
},
Remember
when using fetchXML query in WEB API
1. You need to URL encoded FetchXML
2. With FetchXML you can apply paging
by setting page and count attribute of the fetch element.
