locked
Using natural join with fetch xml...how to ?? RRS feed

  • Question

  • Hi all,

    suppose i have the following code

     

    var authenticationHeader = window.opener.GenerateAuthenticationHeader();

    // Define the SOAP XML to access Microsoft Dynamics CRM Web service.

     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\">" + 

     authenticationHeader+

     "<soap:Body>" + 

     "<RetrieveMultiple xmlns="+

     "\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 

     "<query xmlns:q1="+

     "\"http://schemas.microsoft.com/crm/2006/Query\" "+

     "xsi:type=\"q1:QueryByAttribute\">" + 

     "<q1:EntityName>subject</q1:EntityName>" + 

     "<q1:ColumnSet xsi:type=\"q1:ColumnSet\">" + 

     "<q1:Attributes>" + 

     "<q1:Attribute>title</q1:Attribute>" + 

     "</q1:Attributes>" + 

     "</q1:ColumnSet>" + 

     // Specify the attribute that you are querying on.

     "<q1:Attributes>" + 

     "<q1:Attribute>parentsubjectname</q1:Attribute>" + 

     "</q1:Attributes>" + 

     // Set the value of the attribute using the owner 

     // value of the case record.

     "<q1:Values>" + 

     "<q1:Value xsi:type=\"xsd:string\">"+

     'mmm_1'+

     "</q1:Value>" + 

     "</q1:Values>" +

     

     "</query>" + 

     "</RetrieveMultiple>" + 

     "</soap:Body>" + 

     "</soap:Envelope>";

     

     // Create an instance of an XMLHTTP object.

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

     // Configure the XMLHttp object for the 

     // Microsoft CRM Web services.

      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

       );

     // Send the XMLHttp request.

      xmlHttpRequest.send(xml);

     // Capture the XMLHttp response in XML format.

      var resultXml = xmlHttpRequest.responseXML;

    alert(resultXml.xml);

     

    I need to use natural join with it (I 'll make some modifications for sure)

    and i have the code to this (i has been generated using the fetch xml builder)

    can anybody help me on where to insert the tag for natural join and the correct syntax that match the code as the code generated by the fetch xml builder is a little bit deifferent

     

    thanks in advance :)

    Saturday, October 2, 2010 6:16 PM

Answers

  • Could you explain what join you want to make? For most Fetch queries it is sufficient by using link-entities. That is the method to join the entities.
    Saturday, October 2, 2010 9:38 PM

All replies

  • Hi all,

    suppose i have the following code

     

    var authenticationHeader = window.opener.GenerateAuthenticationHeader();

    // Define the SOAP XML to access Microsoft Dynamics CRM Web service.

     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\">" + 

     authenticationHeader+

     "<soap:Body>" + 

     "<RetrieveMultiple xmlns="+

     "\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 

     "<query xmlns:q1="+

     "\"http://schemas.microsoft.com/crm/2006/Query\" "+

     "xsi:type=\"q1:QueryByAttribute\">" + 

     "<q1:EntityName>subject</q1:EntityName>" + 

     "<q1:ColumnSet xsi:type=\"q1:ColumnSet\">" + 

     "<q1:Attributes>" + 

     "<q1:Attribute>title</q1:Attribute>" + 

     "</q1:Attributes>" + 

     "</q1:ColumnSet>" + 

     // Specify the attribute that you are querying on.

     "<q1:Attributes>" + 

     "<q1:Attribute>parentsubjectname</q1:Attribute>" + 

     "</q1:Attributes>" + 

     // Set the value of the attribute using the owner 

     // value of the case record.

     "<q1:Values>" + 

     "<q1:Value xsi:type=\"xsd:string\">"+

     'mmm_1'+

     "</q1:Value>" + 

     "</q1:Values>" +

     

     "</query>" + 

     "</RetrieveMultiple>" + 

     "</soap:Body>" + 

     "</soap:Envelope>";

     

     // Create an instance of an XMLHTTP object.

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

     // Configure the XMLHttp object for the 

     // Microsoft CRM Web services.

      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

       );

     // Send the XMLHttp request.

      xmlHttpRequest.send(xml);

     // Capture the XMLHttp response in XML format.

      var resultXml = xmlHttpRequest.responseXML;

    alert(resultXml.xml);

     

    I need to use natural join with it (I 'll make some modifications for sure)

    and i have the code to this (i has been generated using the fetch xml builder)

    can anybody help me on where to insert the tag for natural join and the correct syntax that match the code as the code generated by the fetch xml builder is a little bit deifferent

     

    thanks in advance :)

     

    • Merged by DavidJennawayMVP, Moderator Thursday, October 21, 2010 2:37 PM Duplicate thread. Please only post the question in one forum
    Saturday, October 2, 2010 6:14 PM
  • You need to use LinkEntities/LinkEntity, which are not available using the QueryByAttribute model.  You must use the QueryExpression model instead.  Assembling the query itself in XML is really simple:  it's basically an exact mirror of the query structure in C#.  Node names within the XML structure become the schema-names of members within the hierarchy of a C#-equivalent query, and their values and child-nodes follow identically.

    So, the best way to proceed would be to ask you to write up the C# equivalent of a QueryExpression that represents your "natural join".


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Saturday, October 2, 2010 8:52 PM
    Moderator
  • There are several posts in this thread that show how to use Javascript SOAP queries for RetrieveMultiple that use QueryExpression and LinkEntities:  http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/1db96081-120e-4600-92ee-9f36b3f39d43

    That should give you a good example to follow.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Saturday, October 2, 2010 9:06 PM
    Moderator
  • Could you explain what join you want to make? For most Fetch queries it is sufficient by using link-entities. That is the method to join the entities.
    Saturday, October 2, 2010 9:38 PM
  • Dear David,

     

    Could you please give me a reference to a full C# plugin that uses fetch xml to retrieve data and assign it to variables

     

    this is only for time saving purpose as i'am the only crm developper in my company and they need this task to be completed as fast as i can

     

    and also if there any way to use the  LinkEntities/LinkEntity with fetch xml that is embeded in the onLoad event please inform me

     

    thanks in advance :)

    Sunday, October 3, 2010 9:04 AM
  • http://nishantrana.wordpress.com/2007/11/02/link-entity-query-expression-and-fetchxml-wizard/

    http://msdn.microsoft.com/en-us/library/bb928434.aspx

    Though your question indicated that you wished to use FetchXML, your original post does not use FetchXML, it uses a SOAP-based call to the "RetrieveMultiple" message.  I answered your question in the context of the post itself, and not its title.  Regardless, the links above should give you what you're looking for.

    On a side-note, I'm the only CRM developer in my company as well, and am all too well familiar with the "as fast as I can" approach.  That said, I highly recommend Google as a searching tool to locate the things you need, as it serves me well in finding answers to many of my problems in CRM that have been thoroughly documented or blogged by others.


    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Sunday, October 3, 2010 4:45 PM
    Moderator
  • Hopefully answered here: http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/92acf2d2-57e3-43af-a283-79ded0dd55c7
    Dave Berry - MVP Dynamics CRM - http:\\crmentropy.blogspot.com
    Monday, October 4, 2010 8:29 PM
    Moderator