Showing posts with label FetchXML. Show all posts
Showing posts with label FetchXML. 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.


Tuesday, May 5, 2015

Convert FetchXML to SQL query


Some time we need to get SQL query for fetchxml. For MS CRM application you can get FetchXML‘s SQL query by running profiler on SQL server and find the query from profiler.
But if there are more users accessing CRM, then there are lot of queries are executing and we need to go through each query and need to relate to our fetchXML.
This tool converts existing entities system view FetchXML queries to SQL queries. Also you can enter directly fetch XML and it will convert to SQL query.

1.      Convert System View FetchXML to SQL query
a.      First select Entity from drop down
b.      Select system view, FetchXML of selected view will be converted to SQL query.
c.       Click on Get FetchXML, FetchXML for selected view will be retrieved from CRM and displayed in Textbox
d.     If required you can modify fetch xml as per requirement

e.      Click on Get SQL query, your FetchXML will be converted to SQL query and shown in textbox.



2.       Manual FetchXML to SQL
a.      Enter FetchXML in first text box, you want to convert into SQL query
b.      Click on Get SQL query, FetchXML converted and shown in textbox. 





User or Team filters
-          Based on current user all conditions are shown.

-          If any filter condition for user and/or userteam, then current user id and user teams will be retrieved and Id’s will be shown in SQL query.



Date time operator
-          Most of the time showing CRM database functions in SQL query. Because date time is varying based on user settings.





-          For some of the date time operators it is very hard to find exact value. So just showing what will be the possible values.

e.g. Operator  = last-x-fiscal-periods, every CRM might have different fiscal period setting, so user need to put those dates.


For Managed solution : https://fetchxml2sql.codeplex.com/