Wednesday, March 16, 2011

RetrieveMultiple Calls in Jscript in Microsoft Dynamics CRM 2011

RetrieveMultiple is probably the most common call you will have to format up in your Microsoft CRM career,  It has been for me anyways.  I wanted to give you a couple examples of how to do this in Jscript.  The first one is taken partially derived from the SDK that contains a linkentity (join) and the second is a simpler call that is a basic call that has criteria (where clause) but no links (joins).

This example is asynchronous, if you want to learn how to make JScript SOAP calls synchronously please visit this posthttp://mileyja.blogspot.com/2011/07/using-jscript-to-access-soap-web.html

These examples were formatted up using the soaplogger solution included in the SDK in conjunction with the CRM 2011 SOAP Request Formatter.  The project for the formatter is at http://crm2011soap.codeplex.com/

To learn the ins and outs of parsing the response in 2011 using the DOM parser review this post.
http://mileyja.blogspot.com/2011/03/microsoft-dynamics-crm-2011-parsing.html


You can call either of these examples using the following in the GUI form eventhandler interface:

SDK.SAMPLES.RetrieveMultipleRequest

Call #1: returns (alerts) the name of the roles for a user when a valid guid is provided. (you will have to replace the guid with a valid one from your system)  To do this see the systemuserbase table in your org database


if (typeof (SDK) == "undefined")
   { SDK = { __namespace: true }; }
       //This will establish a more unique namespace for functions in this library. This will reduce the 
       // potential for functions to be overwritten due to a duplicate name when the library is loaded.
       SDK.SAMPLES = {
           _getServerUrl: function () {
               ///<summary>
               /// Returns the URL for the SOAP endpoint using the context information available in the form
               /// or HTML Web resource.
               ///</summary>
               var OrgServicePath = "/XRMServices/2011/Organization.svc/web";
               var serverUrl = "";
               if (typeof GetGlobalContext == "function") {
                   var context = GetGlobalContext();
                   serverUrl = context.getServerUrl();
               }
               else {
                   if (typeof Xrm.Page.context == "object") {
                         serverUrl = Xrm.Page.context.getServerUrl();
                   }
                   else
                   { throw new Error("Unable to access the server URL"); }
                   }
                  if (serverUrl.match(/\/$/)) {
                       serverUrl = serverUrl.substring(0, serverUrl.length - 1);
                   } 
                   return serverUrl + OrgServicePath;
               }, 
           RetrieveMultipleRequest: function () {
               var requestMain = ""
                             
               requestMain += "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
               requestMain += "  <s:Body>";
               requestMain += "    <Execute xmlns=\"http://schemas.microsoft.com/xrm/2011/Contracts/Services\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\">";
               requestMain += "      <request i:type=\"a:RetrieveMultipleRequest\" xmlns:a=\"http://schemas.microsoft.com/xrm/2011/Contracts\">";
               requestMain += "        <a:Parameters xmlns:b=\"http://schemas.datacontract.org/2004/07/System.Collections.Generic\">";
               requestMain += "          <a:KeyValuePairOfstringanyType>";
               requestMain += "            <b:key>Query</b:key>";
               requestMain += "            <b:value i:type=\"a:QueryExpression\">";
               requestMain += "              <a:ColumnSet>";
               requestMain += "                <a:AllColumns>false</a:AllColumns>";
               requestMain += "                <a:Columns xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                  <c:string>name</c:string>";
               requestMain += "                </a:Columns>";
               requestMain += "              </a:ColumnSet>";
               requestMain += "              <a:Criteria>";
               requestMain += "                <a:Conditions />";
               requestMain += "                <a:FilterOperator>And</a:FilterOperator>";
               requestMain += "                <a:Filters />";
               requestMain += "              </a:Criteria>";
               requestMain += "              <a:Distinct>false</a:Distinct>";
               requestMain += "              <a:EntityName>role</a:EntityName>";
               requestMain += "              <a:LinkEntities>";
               requestMain += "                <a:LinkEntity>";
               requestMain += "                  <a:Columns>";
               requestMain += "                    <a:AllColumns>false</a:AllColumns>";
               requestMain += "                    <a:Columns xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\" />";
               requestMain += "                  </a:Columns>";
               requestMain += "                  <a:EntityAlias i:nil=\"true\" />";
               requestMain += "                  <a:JoinOperator>Inner</a:JoinOperator>";
               requestMain += "                  <a:LinkCriteria>";
               requestMain += "                    <a:Conditions>";
               requestMain += "                      <a:ConditionExpression>";
               requestMain += "                        <a:AttributeName>systemuserid</a:AttributeName>";
               requestMain += "                        <a:Operator>Equal</a:Operator>";
               requestMain += "                        <a:Values xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                          <c:anyType i:type=\"d:string\" xmlns:d=\"http://www.w3.org/2001/XMLSchema\">81ED14D9-4441-E011-963A-080027AD5B6E</c:anyType>";
               requestMain += "                        </a:Values>";
               requestMain += "                      </a:ConditionExpression>";
               requestMain += "                    </a:Conditions>";
               requestMain += "                    <a:FilterOperator>And</a:FilterOperator>";
               requestMain += "                    <a:Filters />";
               requestMain += "                  </a:LinkCriteria>";
               requestMain += "                  <a:LinkEntities />";
               requestMain += "                  <a:LinkFromAttributeName>roleid</a:LinkFromAttributeName>";
               requestMain += "                  <a:LinkFromEntityName>role</a:LinkFromEntityName>";
               requestMain += "                  <a:LinkToAttributeName>roleid</a:LinkToAttributeName>";
               requestMain += "                  <a:LinkToEntityName>systemuserroles</a:LinkToEntityName>";
               requestMain += "                </a:LinkEntity>";
               requestMain += "              </a:LinkEntities>";
               requestMain += "              <a:Orders />";
               requestMain += "              <a:PageInfo>";
               requestMain += "                <a:Count>0</a:Count>";
               requestMain += "                <a:PageNumber>0</a:PageNumber>";
               requestMain += "                <a:PagingCookie i:nil=\"true\" />";
               requestMain += "                <a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>";
               requestMain += "              </a:PageInfo>";
               requestMain += "              <a:NoLock>false</a:NoLock>";
               requestMain += "            </b:value>";
               requestMain += "          </a:KeyValuePairOfstringanyType>";
               requestMain += "        </a:Parameters>";
               requestMain += "        <a:RequestId i:nil=\"true\" />";
               requestMain += "        <a:RequestName>RetrieveMultiple</a:RequestName>";
               requestMain += "      </request>";
               requestMain += "    </Execute>";
               requestMain += "  </s:Body>";
               requestMain += "</s:Envelope>";
               var req = new XMLHttpRequest();
               req.open("POST", SDK.SAMPLES._getServerUrl(), true)
               // Responses will return XML. It isn't possible to return JSON.
               req.setRequestHeader("Accept", "application/xml, text/xml, */*");
               req.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
               req.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
               var successCallback = null;
               var errorCallback = null;
               req.onreadystatechange = function () { SDK.SAMPLES.RetrieveMultipleResponse(req, successCallback, errorCallback); };
               req.send(requestMain);
           },
       RetrieveMultipleResponse: function (req, successCallback, errorCallback) {
               ///<summary>
               /// Recieves the assign response
               ///</summary>
               ///<param name="req" Type="XMLHttpRequest">
               /// The XMLHttpRequest response
               ///</param>
               ///<param name="successCallback" Type="Function">
               /// The function to perform when an successfult response is returned.
               /// For this message no data is returned so a success callback is not really necessary.
               ///</param>
               ///<param name="errorCallback" Type="Function">
               /// The function to perform when an error is returned.
               /// This function accepts a JScript error returned by the _getError function
               ///</param>
               
               
               if (req.readyState == 4) {
               if (req.status == 200) {
               //if (successCallback != null)
               //***********************
               //   ALERT RESULT HERE
               //***********************
                alert(req.responseXML.xml.toString()); }
               //}
               else {
                   errorCallback(SDK.SAMPLES._getError(req.responseXML));
               }
           }
       },
       _getError: function (faultXml) {
           ///<summary>
           /// Parses the WCF fault returned in the event of an error.
           ///</summary>
           ///<param name="faultXml" Type="XML">
           /// The responseXML property of the XMLHttpRequest response.
           ///</param>
           var errorMessage = "Unknown Error (Unable to parse the fault)";
           if (typeof faultXml == "object") {
               try {
                   var bodyNode = faultXml.firstChild.firstChild;
                   //Retrieve the fault node
                   for (var i = 0; i < bodyNode.childNodes.length; i++) {
                       var node = bodyNode.childNodes[i];
                       //NOTE: This comparison does not handle the case where the XML namespace changes
                       if ("s:Fault" == node.nodeName) {
                       for (var j = 0; j < node.childNodes.length; j++) {
                           var faultStringNode = node.childNodes[j];
                           if ("faultstring" == faultStringNode.nodeName) {
                               errorMessage = faultStringNode.text;
                               break;
                           }
                       }
                       break;
                   }
               }
           }
           catch (e) { };
        }
        return new Error(errorMessage);
     },
 __namespace: true
};


Call #2: returns (alerts) the name of an account when a valid guid is provided. (you will have to replace the guid with a valid one from your system)  To do this see the accountbase table in your org database.  One other thing is that if you queried by a different attribute besides id you could get more than one record which is usually the point of a retrievemultiple. 

if (typeof (SDK) == "undefined")
   { SDK = { __namespace: true }; }
       //This will establish a more unique namespace for functions in this library. This will reduce the 
       // potential for functions to be overwritten due to a duplicate name when the library is loaded.
       SDK.SAMPLES = {
           _getServerUrl: function () {
               ///<summary>
               /// Returns the URL for the SOAP endpoint using the context information available in the form
               /// or HTML Web resource.
               ///</summary>
               var OrgServicePath = "/XRMServices/2011/Organization.svc/web";
               var serverUrl = "";
               if (typeof GetGlobalContext == "function") {
                   var context = GetGlobalContext();
                   serverUrl = context.getServerUrl();
               }
               else {
                   if (typeof Xrm.Page.context == "object") {
                         serverUrl = Xrm.Page.context.getServerUrl();
                   }
                   else
                   { throw new Error("Unable to access the server URL"); }
                   }
                  if (serverUrl.match(/\/$/)) {
                       serverUrl = serverUrl.substring(0, serverUrl.length - 1);
                   } 
                   return serverUrl + OrgServicePath;
               }, 
           RetrieveMultipleRequest: function () {
               var requestMain = ""
               requestMain += "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
               requestMain += "  <s:Body>";
               requestMain += "    <Execute xmlns=\"http://schemas.microsoft.com/xrm/2011/Contracts/Services\" xmlns:i=\"http://www.w3.org/2001/XMLSchema-instance\">";
               requestMain += "      <request i:type=\"a:RetrieveMultipleRequest\" xmlns:a=\"http://schemas.microsoft.com/xrm/2011/Contracts\">";
               requestMain += "        <a:Parameters xmlns:b=\"http://schemas.datacontract.org/2004/07/System.Collections.Generic\">";
               requestMain += "          <a:KeyValuePairOfstringanyType>";
               requestMain += "            <b:key>Query</b:key>";
               requestMain += "            <b:value i:type=\"a:QueryExpression\">";
               requestMain += "              <a:ColumnSet>";
               requestMain += "                <a:AllColumns>false</a:AllColumns>";
               requestMain += "                <a:Columns xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                  <c:string>name</c:string>";
               requestMain += "                </a:Columns>";
               requestMain += "              </a:ColumnSet>";
               requestMain += "              <a:Criteria>";
               requestMain += "                <a:Conditions>";
               requestMain += "                  <a:ConditionExpression>";
               requestMain += "                    <a:AttributeName>accountid</a:AttributeName>";
               requestMain += "                    <a:Operator>Equal</a:Operator>";
               requestMain += "                    <a:Values xmlns:c=\"http://schemas.microsoft.com/2003/10/Serialization/Arrays\">";
               requestMain += "                      <c:anyType i:type=\"d:string\" xmlns:d=\"http://www.w3.org/2001/XMLSchema\">ABA9D2CE-CD4B-E011-9078-080027AD5B6E</c:anyType>";
               requestMain += "                    </a:Values>";
               requestMain += "                  </a:ConditionExpression>";
               requestMain += "                </a:Conditions>";
               requestMain += "                <a:FilterOperator>And</a:FilterOperator>";
               requestMain += "                <a:Filters />";
               requestMain += "              </a:Criteria>";
               requestMain += "              <a:Distinct>false</a:Distinct>";
               requestMain += "              <a:EntityName>account</a:EntityName>";
               requestMain += "              <a:LinkEntities />";
               requestMain += "              <a:Orders />";
               requestMain += "              <a:PageInfo>";
               requestMain += "                <a:Count>0</a:Count>";
               requestMain += "                <a:PageNumber>0</a:PageNumber>";
               requestMain += "                <a:PagingCookie i:nil=\"true\" />";
               requestMain += "                <a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>";
               requestMain += "              </a:PageInfo>";
               requestMain += "              <a:NoLock>false</a:NoLock>";
               requestMain += "            </b:value>";
               requestMain += "          </a:KeyValuePairOfstringanyType>";
               requestMain += "        </a:Parameters>";
               requestMain += "        <a:RequestId i:nil=\"true\" />";
               requestMain += "        <a:RequestName>RetrieveMultiple</a:RequestName>";
               requestMain += "      </request>";
               requestMain += "    </Execute>";
               requestMain += "  </s:Body>";
               requestMain += "</s:Envelope>";
               var req = new XMLHttpRequest();
               req.open("POST", SDK.SAMPLES._getServerUrl(), true)
               // Responses will return XML. It isn't possible to return JSON.
               req.setRequestHeader("Accept", "application/xml, text/xml, */*");
               req.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
               req.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
               var successCallback = null;
               var errorCallback = null;
               req.onreadystatechange = function () { SDK.SAMPLES.RetrieveMultipleResponse(req, successCallback, errorCallback); };
               req.send(requestMain);
           },
       RetrieveMultipleResponse: function (req, successCallback, errorCallback) {
               ///<summary>
               /// Recieves the assign response
               ///</summary>
               ///<param name="req" Type="XMLHttpRequest">
               /// The XMLHttpRequest response
               ///</param>
               ///<param name="successCallback" Type="Function">
               /// The function to perform when an successfult response is returned.
               /// For this message no data is returned so a success callback is not really necessary.
               ///</param>
               ///<param name="errorCallback" Type="Function">
               /// The function to perform when an error is returned.
               /// This function accepts a JScript error returned by the _getError function
               ///</param>
               if (req.readyState == 4) {
               if (req.status == 200) {
               //if (successCallback != null)
               alert(req.responseXML.xml.toString()); }
               //}
               else {
                   errorCallback(SDK.SAMPLES._getError(req.responseXML));
               }
           }
       },
       _getError: function (faultXml) {
           ///<summary>
           /// Parses the WCF fault returned in the event of an error.
           ///</summary>
           ///<param name="faultXml" Type="XML">
           /// The responseXML property of the XMLHttpRequest response.
           ///</param>
           var errorMessage = "Unknown Error (Unable to parse the fault)";
           if (typeof faultXml == "object") {
               try {
                   var bodyNode = faultXml.firstChild.firstChild;
                   //Retrieve the fault node
                   for (var i = 0; i < bodyNode.childNodes.length; i++) {
                       var node = bodyNode.childNodes[i];
                       //NOTE: This comparison does not handle the case where the XML namespace changes
                       if ("s:Fault" == node.nodeName) {
                       for (var j = 0; j < node.childNodes.length; j++) {
                           var faultStringNode = node.childNodes[j];
                           if ("faultstring" == faultStringNode.nodeName) {
                               errorMessage = faultStringNode.text;
                               break;
                           }
                       }
                       break;
                   }
               }
           }
           catch (e) { };
        }
        return new Error(errorMessage);
     },
 __namespace: true
};



I hope this helps!!

21 comments:

  1. Have you been able to use the RetrieveMultiple request via JavaScript to submit FetchXML requests? If so, could you post a sample?

    Thanks!

    ReplyDelete
  2. No, I haven't used FetchXML for many thing besides SSRS based custom Reports for CRM 2011 Online.

    ReplyDelete
  3. Hey admin, I think Google stripped out your post because you tried to post jscript code in the comments here. I think I might have understood your question from the email though. I think you are getting prompted for credentials is because this jscript code is meant to be used from within the CRM application in the provided GUI events that allow the attachment of jscript event handlers. If you are using another application you will want to make a web reference to the soap service and use something similar to the .NET code.

    ReplyDelete
  4. Hey id,

    I have since covered the topic you requested by using retrievemultiple to call fetchXML
    http://mileyja.blogspot.com/2011/06/use-fetchxml-queries-in-jscript-and-net.html

    ReplyDelete
  5. Hi,

    I have tried the 2nd call but req.responseXML.xml.toString() doesn't return the value of account name!
    It instead shows the requestMain xml string, but I've observed account name is inbetween name & account id tags of xml.

    Any idea please?

    ReplyDelete
  6. If you go back and look, there are a couple links in the blog post (towards the top) that might be helpful.

    One is a tutorial on how to use the DOM parser to get the values out of the XML document that is returned by the request. There is also a link up there on how to make this call synchronous instead of asynchronous and admittedly retrieve calls and retrieve multiple calls are usually better used synchronously because you usually have to act on the results after they are returned in the XML.

    ReplyDelete
  7. this really helped me, fantastic

    ReplyDelete
  8. Hi Jamie,

    Your blog has been a life saver for me since I found you on Google :)

    Quick question:

    If I wanted to retrieve all Contacts (RetrieveMultiple) by owner, what do I need to add to the SOAP structure to achieve this?

    ReplyDelete
  9. If you can format a call successfully in C# you should try out the SOAP Logger solution in the SDK and then use my SOAP formatter to generate the SOAP jscript for the call.

    ReplyDelete
  10. Hi Jamie Miley,

    I am into Middle ware (EAI).

    I am trying to retrievemultiple Operation using below WSDL through the Java.

    https://slb.api.crm.dynamics.com/XRMServices/2011/Organization.svc?wsdl=wsdl0

    Now, my question is I need to handle the soap response using retrieveMultipleResponse when i do retrieveMultipleRequest Operation.

    In order to handle the response I need to have an XSD. But I could not make out from the WSDL.

    If possible can you please provide me? I have tried to get the XSD from the WSDL but I am totally failed. it is failing at keyvaluepairofSting tag.


    Thanks a ton in advance... I hope to see the quick response from you.

    ReplyDelete
  11. Usually I use the xsd.exe tool that comes with visual studio. You can point it at the xml file and it will generate one for you.

    ReplyDelete
  12. is there any master (Generic XSD) for the retrievemultiple response operation? I have genreated xsd using this tool and not validating / unmarshal the data properly. I have created xsd using altova it is working fine. every time i see different responses from the retrievemultiple request operation. I want generic response from dynamics. if you know please provide soon. thanks a lot

    ReplyDelete
  13. Visual Studio also has the disco.exe tool. I think that can get you a generic xsd if you point it at the wsdl.
    http://msdn.microsoft.com/en-us/library/cy2a3ybs(v=vs.80).aspx

    ReplyDelete
  14. Hi Jamie,

    I want to use SDK.SAMPLES.RetrieveMultipleRequest... but instead of retrieving the data based on Equality, I want the filter to be based on Contains...

    For example:
    Parameter value= "abc"
    Field to be compared-with value= "abcde"

    if Field value contains Parameter value then i want to do the retrieve.

    What changes in the code should I make to achieve this task?

    Thanks in advance.

    ReplyDelete
    Replies
    1. You should be able to change "Equal" to "Contains" in the condition tag above. Condition operator can have a ton of different values in CRM 2011.

      http://www.resultondemand.nl/support/sdk/T_Microsoft_Xrm_Sdk_Query_ConditionOperator.htm

      Delete
    2. This comment has been removed by the author.

      Delete
    3. I tried to change "Equal" to "Contains" in the tag "Operator"..
      but it didn't work!

      Delete
  15. My reply system strips out code if you try to post it but I think people will have to look at your code to be effective in helping further. You should start a new question in this forum (http://social.microsoft.com/Forums/en-US/crmdevelopment/threads) and post your code with it.

    ReplyDelete
  16. tengo un problema
    estoy en un abierto que le llamare formulario1 y tengo un campo fecha con valores(01-02-2010)quiero enviarlo al formulario2 que esta cerrado donde tambien tengo un campo fecha sin valores - quiero llevar el valor del campo fecha del form1 al campo fecha del form2 y grabar los cambios sin abrir el form2.

    espero me puedan guiar

    ReplyDelete
  17. Pretty interesting post! Thanks it was interesting.click here

    ReplyDelete