CRM 2013: JavaScript – RetrieveMultiple records using FetchXML query and SOAP Endpoint

When retrieving information from multiple related records, I prefer using FetchXML and the SOAP endpoint over OData. To use FetchXML with JavaScript, we need the “RetrieveMultiple” message in the CRM Organization Service, which is available via what the CRM 2013 SDK (version 6.0.2 at time of writing) calls the “Modern App SOAP endpoint”.

Modern App SOAP Endpoint in CRM 2013

There is already lots of information regarding the differences between REST And SOAP endpoints for Dynamics CRM; see for instance here. Similar to a very nice post here, I prefer working with FetchXML if I have to deal with retrieving information from multiple related entities.

WARNING:

  • To be honest, if you want to use SOAP, it’s best to either include the SDK library or XrmSvcToolkit JavaScript libraries with helper functions to help format and generate SOAP requests and responses. Such libraries also have helper functions to format the XML response so that as a developer you only need to worry about getting your FetchXML query right!

For the sake of clarity, I’m going to do it the hard way. 🙂

FetchXML in CRM 2013

Fetch is a query language that is used in Dynamics CRM. To learn the basic of building FetchXML query see here. FetchXML can be generated by CRM “Advanced Find”.

There used to be a lovely tool called FetchXML Builder which provides a graphical interface for building and testing FetchXML, but unfortunately the community editor doesn’t work with CRM 2013 anymore. So you have to either use Advanced Find in CRM or build your FetchXML query manually. Once completed, check your fetch query using the XRM Toolbox!

Example

On the Account form, you can normally see a list of contacts related to this account record in the quick view section for “Contacts”. These contact records have their “Parent Customer” fields set to the account record you are viewing.

SOAP-01

SOAP-02

Suppose you have the following requirement: If the user creates a new Lead record in CRM 2013 and enters an existing account record in the “Existing Account?” field in the “Lead To Opportunity Sales” business process flow, then the lead record’s “Stakeholder” grid should be populated with all active contact record whose “Parent Customer” field is set to the same account record.

SOAP-03

How to do this?

Technical Details Of This Requirement…

Before writing any code, we need to understand that the requirement involves several components on the Lead entity form.

(1) The lead attribute that the label “Existing Account?” in the business process flow refers to?

Looking in the default business process flow this field is called “Parent Account For Lead” (scheme name: “ParentAccountId”) on the lead entity.

(2) What kind of entity records the “STAKEHOLDERS” subgrid is showing on the Lead entity form?

Connection records!

SOAP-04

So if I add “Janet Ralley” as one of the Stakeholders for the Lead record called “Rebecca Cook”, in the background CRM creates a connection record linking “Janet Ralley” to “Rebecca Cook”.

SOAP-05

SOAP-06

Therefore we can break down the requirement as follows:

  1. When the “ParentAccountId” field is set on the Lead entity record, retrieve the guid of this account record.
  2. With the account guid, construct a query to retrieve all active contact records associated with this account record.
  3. For each contact record retrieved, create a connection record and set them all as “Stakeholders” connected to the lead record.

FetchXML to the rescue!

Using FetchXML, I shall retrieve a list of active contact records (with their name and id) whose parentcustomerid is set to the GUID of a specific account record. Note: see an earlier post on entity image on how to use the OData endpoint to retrieve multiple records using the “$select” filter.

Here is the FetchXML query tested in “XRM Toolbox”. Note that the extra information I needed to provide is the GUID of an account record in my CRM organization “Sweet Fruit Ltd” (here, “70B975E3-3863-E311-93FD-00155D02E726”). We shall retrieve all active contacts associated with this account record.

SOAP-07

Here is the response:

SOAP-08

Now that I have my FetchXML query, how to use it in a JavaScript function?

Modern app SOAP endpoint comes to the rescue!

Unlike the REST endpoint, the SOAP endpoint uses the Organization service, so the XMLHttpRequests are sent to a different URL from that of OData endpoint:

<organization URL>/XRMServices/2011/Organization.svc/web

In JavaScript, you need to POST your RetrieveMultiple request using the browser’s XMLHttpRequest object, where the request body (which in our case contains the FetchXML query) must be encoded in XML. You will also need to parse the XML response before you can make use of the result.

Using Fiddler, you can now see the SOAP XMLHttpRequest being sent out with the encoded FetchXML query embedded in “a:Query”:

SOAP-09

The XMLHttpResponse can also be captured in Fiddler where you can see the structure of the XML document object (at the time of this capture I was using IE10). Note that the key-value pair for “fullname” and “contactid” returns the fullname and the GUID of the contact record retrieved by the query.

SOAP-10

The JavaScript Code …?

Here we go…

First up is the piece of JavaScript which is attached to the “OnChange” event of the “parentaccountid” attribute of the Lead entity. This method retrieves the account GUID, passes this to “BuildFetchXMLQuery” method for building the FetchXML query (given in the above section), then the “XMLEncode” method from the CRM2013 SDK encodes this query.

Note that methods in the sample code below that start with “SDK.Metadata.<function name>” are all methods from the CRM2013 SDK section “Sample: Retrieve entity metadata using JavaScript” (version 6.0.2 at time of writing).


//Hook this method to the "OnChange" event of "parentaccountid" field
OnAttributeChange_ParentAccountId: function () {

    var existingAccount = Xrm.Page.getAttribute("parentaccountid").getValue();

    if (existingAccount != null && existingAccount[0].id != null) {

        var accountId = existingAccount[0].id;
        var accountName = existingAccount[0].name;

        var fetchQuery = this.BuildFetchXMLQuery(accountId);
        var encodedFetchQuery = SDK.MetaData.XMLEncode(fetchQuery);

        this.GetActiveContacts(accountId, encodedFetchQuery, this.CreateConnections, this.GetActiveContactsErrorCallback);

    }
},

Once the query is encoded, I pass this along with the account GUID as the first two parameters into another method called “GetActiveContacts” to retrieve the relevant contact records. This method is the one that contains the SOAP envelope specifying that the “RetrieveMulitple” request to be used, along with the XMLHttpRequest header. The last two parameters of the “GetActivecontacts” method are callback method to be called afterwards on success and error respectively.

GetActiveContacts: function (accountId, encodedFetch, successCallback, errorCallback) {

    //request SOAP envelope for the encoded fetchXML query
    var request = [
        '<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body>',
            '<Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" >',
                '<request i:type="a:RetrieveMultipleRequest" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">',
                    '<a:Parameters xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">',
                        '<a:KeyValuePairOfstringanyType>',
                            '<b:key>Query</b:key>',
                            '<b:value i:type="a:FetchExpression">',
                                '<a:Query>', encodedFetch, '</a:Query>',
                            '</b:value>',
                        '</a:KeyValuePairOfstringanyType>',
                    '</a:Parameters>',
                    '<a:RequestId i:nil="true"/>',
                    '<a:RequestName>RetrieveMultiple</a:RequestName>',
                '</request>',
            '</Execute>',
        '</s:Body></s:Envelope>'
    ].join("");

    //Synchronous XMLHttpRequest to retrieve contact records
    var req = new XMLHttpRequest();
    req.open("POST", encodeURI(Xrm.Page.context.getClientUrl() + "/XRMServices/2011/Organization.svc/web"), false);
    try { req.responseType = 'msxml-document' } catch (e) { }
    req.setRequestHeader("Accept", "application/xml, text/xml, */*");
    req.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
    req.setRequestHeader("SOAPAction","<a href="http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute">http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute</a>");
    req.onreadystatechange = function () {
        if (this.readyState == 4 /* complete */) {
            req.onreadystatechange = null; //Addresses potential memory leak issue with IE
            if (this.status == 200  /*success*/) {
                var doc = req.responseXML;
                successCallback(doc);
            }
            else {
                errorCallback(accountId);
            }
        }
    }
    req.send(request);
},

Several things to note regarding this piece of code:

  • You must encode your FetchXML query before embedding the query inside the SOAP request
  • The namespaces used in the SOAP request are defined to match those namespaces in the CRM2013 SDK sample. If you use another JavaScript library the namespaces might well be defined differently. For the purpose of this blog, the following namespaces are defined:
    • xmlns:s=http://schemas.xmlsoap.org/soap/envelope/
    • xmlns:i=http://www.w3.org/2001/XMLSchema-instance
    • xmlns:a=http://schemas.microsoft.com/xrm/2011/Contracts
    • xmlns:b=http://schemas.datacontract.org/2004/07/System.Collections.Generic
  • In the POST XMLHttpRequest header, we use the CRM2013 organization service
  • An extra try-catch condition is included in the XMLHttpRequest to set the response type to ‘msxml-document’ so MSXML-specific functionality such as “selectNodes” can be used when manipulating the response object in IE10 (see IEBlog for more detail).

If the request is unsuccessful, the errorCallback function named “GetActiveContactsErrorCallback” is called to display a non-blocking “SDK.Utility.alertDialog” box (see an earlier Post if you want to know how to use the method).

If the request succeeds, the response object is of type “MSXML-document” and is passed to the successCallback function named “CreateConnections”:

CreateConnections: function (doc) {

    //retrieve contact arrays from doc
    var contactLookupArray = PT.Lead_Main.ParseRetrievedXML(doc);

    if (contactLookupArray == null) { //no contact records found
        var errorMsg2 = "CreateConnections Error: no contact records retrieved.";
        //display a non-blocking alert dialog
        Xrm.Utility.alertDialog(errorMsg2, function () { });
        return;
    }

    var currentLeadLookup = PT.Lead_Main.GetCurrentLead();

    //for each contactLookup in the contactLookupArray, send a request to create connection record
    //name = contactLookupArray[i], connected from = currentLeadLookup[0]
    for (var i = 0; i < contactLookupArray.length; i++) {
        PT.Lead_Main.RetrieveConnectionRoleIdWithOData(currentLeadLookup[0], contactLookupArray[i], "Stakeholder", PT.Lead_Main.CreateConnectionWithOData);

    }
},

The hardest part now is to understand how to manipulate the object of type “msxml-document” from the SOAP XMLHttpRequest. First we need to retrieve all the contact records (in “ParseRetrievedXML” method). Once that is done, we also need the lookup information of the current Lead entity record opened. The last step is to retrieve the connection role GUID of the “Stakeholder” record using OData endpoint (in “RetrieveConnectionRoleIdWithOData”), and put everything together by creating the required connection record from each retrieved contact record to the current lead record (in “CreateConnectionWithOData”).

Some details of these methods follow. For brevity I shall display only the “ParseRetrievedXML” method in detail.

//parse the xml document returned by the RetrievedMultiple SOAP request
//See the SDK section "Use JavaScript with Microsoft Dynamics CRM" for more detail.
ParseRetrievedXML: function (doc) {

    try { SDK.MetaData.SetSelectionNamespace(doc); } catch (e) { }

    var resultNodes = SDK.MetaData.SelectNodes(doc, "//b:value");

    if (resultNodes.length == 1) {
        //no b:value for individual records
        return null;
    }

    var contactLookupArray = new Array();
    var contactCount = 0;
    for (var i = 1; i < resultNodes.length; i=i+2) { //ignore the first childNode

        contactLookupArray[contactCount] = new Object();
        contactLookupArray[contactCount].name = SDK.MetaData.GetNodeText(resultNodes[i]);
        contactLookupArray[contactCount].id = SDK.MetaData.GetNodeText(resultNodes[i + 1]);
        contactLookupArray[contactCount].entityType = "contact";
        contactCount++;

    }

    return contactLookupArray;
},

Things to note about this method:

  • This method mainly uses several of the following SDK methods that can be found in the CRM2013 SDK (version 6.0.2 at time of writing). Please refer to the section “Sample: Retrieve entity metadata using JavaScript” for the actual code in those methods:
    • SetSelectionNamespace
    • SelectNodes
    • GetNodeText
  • There are significant differences between different browsers when it comes to parsing XML. The SDK methods above have been updated to cater for both IE (using MSXML api) and non-IE browsers. More information can be found in the CRM SDK section “User JavaScript with Microsoft Dynamics CRM”.

The rest of the methods using OData follow the same lines as those given in earlier posts on OData (e.g. Update Entity Image post). Here is a part of the CreateConnectionWithOData which creates the connection record from one of the retrieved contact records to the current lead record, stringifies this connection object and POSTs it to CRM via the OData endpoint:

// fragment of the CreateConnectionWithOData function
var connection = {};
connection.Name = toContact.name; //name of the toContact
connection.Record2Id = { Id: toContact.id, LogicalName: "contact", Name: toContact.name };
connection.Record2RoleId = { Id: toContactRoleId, LogicalName: "connectionrole", Name: toContactRole };
connection.Record1Id = { Id: fromLead.id, LogicalName: "lead", Name: fromLead.name };

var jsonConnection = JSON.stringify(connection);

//OData URI
var oDataURI = Xrm.Page.context.getClientUrl()
    + "/XRMServices/2011/OrganizationData.svc/"
    + "ConnectionSet";

And we are done!

Advertisements

8 thoughts on “CRM 2013: JavaScript – RetrieveMultiple records using FetchXML query and SOAP Endpoint

    1. Hi Methecitizen,

      Thanks for liking my posts! I enjoy blogging and have received many positive responses to this blog which is great. It’s just finding the time in between work to continue that is getting a little difficult. 🙂

      But don’t give up on me just yet. I’ve got a few more ideas up my sleeve….

      Stay tuned…

      Cheers,
      Priscilla.

  1. Thank you; this is a great outline of how to use the SOAP endpoint from JS, and the caveats and workaround for browser-based differences in XML handling are exactly what’s missing from most other blog posts on the subject.

    Just one small change: in the call to set the SOAPAction header, the header value should be a plain URL; at the moment it’s a combination HTML anchor/JavaScript syntax error – suspect something went awry in a copy/paste 🙂

  2. Thanks for this wonderful Post. I am getting the following error :-
    System.ArgumentNullException:

    Value cannot be null.
    Parameter

    name:

    Request.RequestName

    Can you help me out with this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s