Thursday, June 2, 2011

Getting Row Count or Entity Count in Jscript or .NET in Microsoft Dynamics CRM 2011 using FetchXML

This illustration shows you how get a rowcount or entity count for an entity type using FetchXML queries in code in jscript and also C# in Microsoft Dynamics CRM 2011 though RetrieveMultipleRequest.   This example will be given in SOAP (JScript) and in C# (.NET).

This example counts the number of active account entities in my CRM org.  I know from looking in the system that there are 14.

Ok, here is what the code look like!
First in C#:

RetrieveMultipleRequest req = new RetrieveMultipleRequest();
FetchExpression fetch = new FetchExpression("<fetch distinct='false' mapping='logical' aggregate='true'>" +
   "<entity name='account'>" +
   "<attribute name='accountid' aggregate='count' alias='testcount'/>" +
   "</entity>" +
   "</fetch>");
req.Query = fetch;
RetrieveMultipleResponse resp = (RetrieveMultipleResponse)service.Execute(req);

If you need help instantiating a service object in .NET within a plugin check out this post:
http://mileyja.blogspot.com/2011/04/instantiating-service-object-within.html

Now here is the Jscript nicely formatted by the CRM 2011 SOAP formatter. Available at: http://crm2011soap.codeplex.com/

Now in Jscript:


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;
               }, 
           CountAccountsRequest: function () {
               var requestMain = ""
               requestMain += "<pre style=\"font-family: Andale Mono, Lucida Console, Monaco, fixed, monospace; color: #000000; background-color: #eee;font-size: 12px;border: 1px dashed #999999;line-height: 14px;padding: 5px; overflow: auto; width: 100%\"><code>RetrieveMultipleRequest req = new RetrieveMultipleRequest();";
               requestMain += "FetchExpression fetch = new FetchExpression(&quot;&lt;fetch distinct='false' mapping='logical' aggregate='true'&gt;&quot; +";
               requestMain += "   &quot;&lt;entity name='account'&gt;&quot; +";
               requestMain += "   &quot;&lt;attribute name='accountid' aggregate='count' alias='testcount'/&gt;&quot; +";
               requestMain += "   &quot;&lt;/entity&gt;&quot; +";
               requestMain += "   &quot;&lt;/fetch&gt;&quot;);";
               requestMain += "req.Query = fetch;";
               requestMain += "RetrieveMultipleResponse resp = (RetrieveMultipleResponse)slos.Execute(req);";
               requestMain += "</code></pre>";
               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.CountAccountsResponse(req, successCallback, errorCallback); };
               req.send(requestMain);
           },
       CountAccountsResponse: 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)
               { successCallback(); }
               }
               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
};




To understand how to parse the response please review my post on using the DOM parser.
Now you can call the SDK.SAMPLES.CountAccountsRequest function from your form jscript handler.
Thats all there is to it!

I hope this helps!

3 comments:

  1. Jamie, is there a way to modify your example above to get the count of all related sub-accounts and then set the results in to a number field of the parent account? My requirements make it necessary for the count to include all sub-accounts in a multi-tier account structure. I am not a hardcore coder by any stretch of the imagination so any guidance you could provide would be greatly appreciated.

    Thank you for all your contributions to the CRM community. This is not the first time I have found your code extremely useful in enhancing my companies CRM.

    Thanks,
    Rob

    ReplyDelete
  2. There isn't a good way to modify this methodology to get related sub-accounts that I am aware of, but you can get perform another retrievemultiple based on accounts where parent account equals some account. Then you can count those results.

    ReplyDelete
  3. You can test FetchXML Online and Browse Metadata here, its quick - http://msxrmtools.com

    ReplyDelete