Showing posts with label predefined Queries. Show all posts
Showing posts with label predefined Queries. Show all posts

Tuesday, November 1, 2016

Use FetchXML to Retrieve Data from MS CRM 2016 using Web API

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.