none
How to fetch Link entity attributes in fetchxml using jquery in ms crm 2011

    Question

  •    In this code I am Linking two entities Quote and Quotelineitem and I need to fetch out some attributes from both the entities but I am only

    able to retireve Quote attributes but this doesn't provide attributes of other entity. please help below is my Xml to get desired results

    var xml = "" +
      "<?xml version=\"1.0\" encoding=\"utf-8\"?>" +
      "<soap:Envelope xmlns:soap=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\">" +
      Xrm.Page.context.getAuthenticationHeader() +
      " <soap:Body>" +
      " <RetrieveMultiple xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
      " <query xmlns:q1=\"http://schemas.microsoft.com/crm/2006/Query\" xsi:type=\"q1:QueryExpression\">" +
      " <q1:EntityName>quote</q1:EntityName>" +
      " <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
      " <q1:Attributes>" +
      " <q1:Attribute>name</q1:Attribute>" +
      " <q1:Attribute>createdby</q1:Attribute>" +
      " <q1:Attribute>createdon</q1:Attribute>" +
       " <q1:Attribute>customerid</q1:Attribute>" +

     
      " </q1:Attributes>" +
      " </q1:ColumnSet>" +
      " <q1:Distinct>true</q1:Distinct>" +
      " <q1:LinkEntities>" +
      " <q1:LinkEntity>" +
      " <q1:LinkFromAttributeName>quoteid</q1:LinkFromAttributeName>" +
      " <q1:LinkFromEntityName>quote</q1:LinkFromEntityName>" +
      " <q1:LinkToEntityName>new_quotelineitem</q1:LinkToEntityName>" +
      " <q1:LinkToAttributeName>new_quotelineitemsid</q1:LinkToAttributeName>" +
      " <q1:JoinOperator>Inner</q1:JoinOperator>" +

     

         " <q1:LinkCriteria>" +

     

     

       " <q1:FilterOperator>And</q1:FilterOperator>" +
      " <q1:Conditions>" +
       "<q1:Condition> <q1:AttributeName>new_fromaddressstreet</q1:AttributeName> <q1:Operator>Like</q1:Operator><q1:Values><q1:Value  xsi:type=\"xsd:string\">%" + street + "%</q1:Value> </q1:Values> </q1:Condition>" +
       " <q1:Condition> <q1:AttributeName>new_fromaddresscity</q1:AttributeName> <q1:Operator>Like</q1:Operator><q1:Values><q1:Value  xsi:type=\"xsd:string\">%" + city + "%</q1:Value> </q1:Values> </q1:Condition>" +
       " <q1:Condition> <q1:AttributeName>new_addressfromstate</q1:AttributeName> <q1:Operator>Like</q1:Operator><q1:Values><q1:Value  xsi:type=\"xsd:string\">%" + state + "%</q1:Value> </q1:Values> </q1:Condition>" +
       " <q1:Condition> <q1:AttributeName>new_addressfromzippostalcode</q1:AttributeName> <q1:Operator>Like</q1:Operator><q1:Values><q1:Value  xsi:type=\"xsd:string\">%" + zip + "%</q1:Value> </q1:Values> </q1:Condition>" +

             " </q1:Conditions>" +

      " </q1:LinkCriteria>" +
        //  " </q1:LinkEntity>" +
        //  " </q1:LinkEntities>" +
      " </q1:LinkEntity>" +
      " </q1:LinkEntities>" +
      " </query>" +
      " </RetrieveMultiple>" +
      " </soap:Body>" +
      "</soap:Envelope>" +
      "";


        var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

        xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
        xmlHttpRequest.setRequestHeader("SOAPAction", " http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
        xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
        xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
        xmlHttpRequest.send(xml);

        var resultXml = xmlHttpRequest.responseXML;

    Saturday, May 19, 2012 7:12 PM

Answers

  • HI Harkamal,

                    If you are working on crm 2011 I recommend creating the query you want using the advance find tool then download the fetch xml by clicking on the button I show below.

    Then you can use the crmfetchkit.js that can be downloaded from http://crmfetchkit.codeplex.com

    One important thing to consider from your logic, be sure you start from the Quotelineitem when you create the query then link to Quote, that way you will be able to get the Quote fields.


    Regards,
    Damian Sinay

    Sunday, May 20, 2012 2:42 AM

All replies

  • HI Harkamal,

                    If you are working on crm 2011 I recommend creating the query you want using the advance find tool then download the fetch xml by clicking on the button I show below.

    Then you can use the crmfetchkit.js that can be downloaded from http://crmfetchkit.codeplex.com

    One important thing to consider from your logic, be sure you start from the Quotelineitem when you create the query then link to Quote, that way you will be able to get the Quote fields.


    Regards,
    Damian Sinay

    Sunday, May 20, 2012 2:42 AM
  • just to make it clear, when you link two entities you can link the primary entity to only an entity that has a lookup attribute on the primary entity i.e n:1 relationship because you can only include columns of only one matching record.

    When you start with quoteline item it has a lookup to quote and u can include its attributes. But one quote can have many lines and so a fetch starting with quote and linking lines is not possible.

    HTH 

    Sam 


    Dynamics CRM MVP | Inogic | http://inogic.blogspot.com| news at inogic dot com

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Monday, May 21, 2012 1:26 AM
  • Hi Damian ,

           Thanks for your help but I constructed the query using advance find and used it with crmfetchkit as shown below but crmfetchkit.js is showing error as show in screenshot below. Please guide if I am doing something wrong.

     function GetRelatedQuotesTest(street, city, state, zip) {



        fetchxml = ['<fetch mapping="logical" version="1.0">',
                                      '<entity name="quote">',
                                       '<all-attributes/>'    ,   
                                        '<order attribute="createdon" descending="true" />',
                                        '<link-entity name="new_quotelineitem" from="new_quotelineitemsid" to="quoteid" alias="aa">',
                                           '<attribute name="new_fromaddressstreet" />',
                                           '<attribute name="new_fromaddresscity" />',
                                           '<attribute name="new_addressfromstate" />',
                                            '<attribute name="new_addresstostreet" />',
                                           '<attribute name="new_addresstocity" />',
                                           '<attribute name="new_addresstostate" />',
                                           '<attribute name="new_addresstozippostalcode" />',
                                           '<attribute name="new_addressfromzippostalcode" />',
                                           '<attribute name="new_fibertechlocationfrom" />',
                                           '<attribute name="new_fibertechlocationto" />',
                                           '<filter type="and">',
                                          '<filter type="or">',
                                          '<filter type="and">',
                                            '<condition attribute="new_fromaddressstreet" operator="eq" value="' + street + '" />',
                                            '<condition attribute="new_fromaddresscity" operator="eq" value="' + city + '" />',
                                            '<condition attribute="new_addressfromstate" operator="eq" value="' + state + '" />',
                                           
                                            '</filter>',
                                           '<filter type="and">',
                                            '<condition attribute="new_addresstostreet" operator="eq" value="' + street + '" />',
                                            '<condition attribute="new_addresstocity" operator="eq" value="' + city + '" />',
                                           '<condition attribute="new_addresstostate" operator="eq" value="' + state + '" />',
                                          
                                            '</filter>',
                                            '</filter>',
                                             '</filter>',
                                          
                                        '</link-entity>',
                                      '</entity>',
                                    '</fetch>'].join('');

        // execute the query (async)
       
        CrmFetchKit.Fetch(fetchxml)
        
        .fail(function (xhr, status, errorThrown) {

            // get the error-message

          
            var msg = $(xhr.responseXML).find('Message').text();

            alert('Error occured: ' + msg);
        })
        .done(function (results) {

            var contactid = null;
            alert('after fetchxml')
            alert(results);
            alert(results.length);
            for (var i = 0, max = results.length; i < max; i++) {

                contactid = results[i].getValue('contactid');

                // Assign (async)
                CrmFetchKit.Assign(contactid, 'contact', bId);
            }
        });






    }


    Harkamal Grewal

    Monday, May 21, 2012 3:32 PM
  • Hi Harkamal,

                    What are the values you are passing to street, city and state? Be sure they don’t contain any character that might break the fetchxml like “, < or >. If that is the case you will need to replace those characters with xml encoding.  Look at this thread for the values you would need to replace

    http://stackoverflow.com/questions/1091945/where-can-i-get-a-list-of-the-xml-document-escape-characters


    Regards,
    Damian Sinay

    Monday, May 21, 2012 3:42 PM
  • Hi Harkamal, 

    please try the latest version of CrmFetchKit.js (currently 1.6). This problem should not occure in the latest version. 

    have fun

    Daniel

    • Proposed as answer by thuld Tuesday, May 22, 2012 1:49 PM
    Tuesday, May 22, 2012 1:49 PM
  • Thanks All for your help I got this issue resolved by using XRMServiceToolKit, but I would love to do this by CrmFetchKit which is still not

    producing results even with its latest version, anyways Thanks All :):).


    Thanks, Harkamal Grewal

    Wednesday, May 23, 2012 12:20 PM
  • Hello Harkamal,

    I am sorry but the 1.6 Version introduced another error. But the latest version (1.7) will work: The unit test works:

    var ctx = this;
        fetchxml = ['<fetch version="1.0" output-format="xml-platform" mapping="logical">',
                    '  <entity name="account">',
                    '    <attribute name="name" />',
                    '    <attribute name="accountid" />',
                    '    <attribute name="createdon" />',
                    '    <attribute name="createdby" />',
                    '    <filter type="and">',
                    '      <condition attribute="name" operator="eq" value="' + ctx.accountName + '" />',
                    '    </filter>',
                    '    <link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" link-type="inner" alias="bu">',
                    '       <attribute name="name" />',
                    '       <filter>',
                    '           <condition attribute="parentbusinessunitid" operator="null" />',
                    '       </filter>',
                    '   </link-entity>',
                    '  </entity>',
                    '</fetch>'].join( '' );
    
    // action - execute fetchxml with linked entity
    CrmFetchKit.Fetch( fetchxml ).then( function ( results ) {
    
        // assert - access attr of the linked-entity
        QUnit.ok( results[0].getValue( 'bu.name' ) !== null, 'Access linked-attributes' );
    
        // continue with the other tests
        QUnit.start();
    
    }, onFetchError );

    Daniel

    Wednesday, May 23, 2012 4:12 PM