CRM 2011 : SOAP Logger

One of the best ways of creating FetchXml query is to use soap logger. It is way better to use stunware’s FetchXml designer if creating query is your objective. It is available in the crm sdk folder in the following folder <SDK folder>\sdk\samplecode\cs\client\soaplogger. when the solution is opened look for the a comments which looks like bellow,

//Add the code you want to test here:
//You must use the SoapLoggerOrganizationService 'slos' proxy rather than the IOrganizationService proxy you would normally use.

Add your code underneath the lines compile and run the program. if you are running the soap logger solution for the first time then the system will take you through the registration process. once the registration process

it will create output.txt in the bin directory which will have the soap action and fetchxml command with it. here's one example that i have created which gets some account contact information from crm,

//Add the code you want to test here:
// You must use the SoapLoggerOrganizationService 'slos' proxy rather than the IOrganizationService proxy you would normally use.

QueryExpression qe = new QueryExpression();
qe.EntityName = "account";
qe.ColumnSet = new ColumnSet();
qe.ColumnSet.Columns.Add("name");
qe.Criteria.AddCondition("accountid", ConditionOperator.Equal, "19C5970E-ABBC-E211-B7AD-3C4A92DBD85C");


qe.LinkEntities.Add(new LinkEntity("account", "contact", "primarycontactid", "contactid", JoinOperator.Inner));
qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");
qe.LinkEntities[0].EntityAlias = "primarycontact";

slos.RetrieveMultiple(qe);

And here's output of the query in the file.

HTTP REQUEST
--------------------------------------------------
POST https://serveraddress/XRMServices/2011/Organization.svc/web
Content-Type: text/xml; charset=utf-8
SOAPAction: http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/RetrieveMultiple

<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body>
    <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <query i:type="a:QueryExpression" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
        <a:ColumnSet>
          <a:AllColumns>false</a:AllColumns>
          <a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
            <b:string>name</b:string>
          </a:Columns>
        </a:ColumnSet>
        <a:Criteria>
          <a:Conditions>
            <a:ConditionExpression>
              <a:AttributeName>accountid</a:AttributeName>
              <a:Operator>Equal</a:Operator>
              <a:Values xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                <b:anyType i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">19C5970E-ABBC-E211-B7AD-3C4A92DBD85C</b:anyType>
              </a:Values>
            </a:ConditionExpression>
          </a:Conditions>
          <a:FilterOperator>And</a:FilterOperator>
          <a:Filters />
        </a:Criteria>
        <a:Distinct>false</a:Distinct>
        <a:EntityName>account</a:EntityName>
        <a:LinkEntities>
          <a:LinkEntity>
            <a:Columns>
              <a:AllColumns>false</a:AllColumns>
              <a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                <b:string>firstname</b:string>
                <b:string>lastname</b:string>
              </a:Columns>
            </a:Columns>
            <a:EntityAlias>primarycontact</a:EntityAlias>
            <a:JoinOperator>Inner</a:JoinOperator>
            <a:LinkCriteria>
              <a:Conditions />
              <a:FilterOperator>And</a:FilterOperator>
              <a:Filters />
            </a:LinkCriteria>
            <a:LinkEntities />
            <a:LinkFromAttributeName>primarycontactid</a:LinkFromAttributeName>
            <a:LinkFromEntityName>account</a:LinkFromEntityName>
            <a:LinkToAttributeName>contactid</a:LinkToAttributeName>
            <a:LinkToEntityName>contact</a:LinkToEntityName>
          </a:LinkEntity>
        </a:LinkEntities>
        <a:Orders />
        <a:PageInfo>
          <a:Count>0</a:Count>
          <a:PageNumber>0</a:PageNumber>
          <a:PagingCookie i:nil="true" />
          <a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>
        </a:PageInfo>
        <a:NoLock>false</a:NoLock>
      </query>
    </RetrieveMultiple>
  </s:Body>
</s:Envelope>
--------------------------------------------------

HTTP RESPONSE
--------------------------------------------------
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
  <s:Body>
    <RetrieveMultipleResponse xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <RetrieveMultipleResult xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
        <a:Entities>
          <a:Entity>
            <a:Attributes xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
              <a:KeyValuePairOfstringanyType>
                <b:key>name</b:key>
                <b:value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Adventure Works (sample)</b:value>
              </a:KeyValuePairOfstringanyType>
              <a:KeyValuePairOfstringanyType>
                <b:key>accountid</b:key>
                <b:value i:type="c:guid" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/">19c5970e-abbc-e211-b7ad-3c4a92dbd85c</b:value>
              </a:KeyValuePairOfstringanyType>
              <a:KeyValuePairOfstringanyType>
                <b:key>primarycontact.firstname</b:key>
                <b:value i:type="a:AliasedValue">
                  <a:AttributeLogicalName>firstname</a:AttributeLogicalName>
                  <a:EntityLogicalName>contact</a:EntityLogicalName>
                  <a:Value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Nancy</a:Value>
                </b:value>
              </a:KeyValuePairOfstringanyType>
              <a:KeyValuePairOfstringanyType>
                <b:key>primarycontact.lastname</b:key>
                <b:value i:type="a:AliasedValue">
                  <a:AttributeLogicalName>lastname</a:AttributeLogicalName>
                  <a:EntityLogicalName>contact</a:EntityLogicalName>
                  <a:Value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Anderson (sample)</a:Value>
                </b:value>
              </a:KeyValuePairOfstringanyType>
            </a:Attributes>
            <a:EntityState i:nil="true" />
            <a:FormattedValues xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
            <a:Id>19c5970e-abbc-e211-b7ad-3c4a92dbd85c</a:Id>
            <a:LogicalName>account</a:LogicalName>
            <a:RelatedEntities xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
          </a:Entity>
        </a:Entities>
        <a:EntityName>account</a:EntityName>
        <a:MinActiveRowVersion>-1</a:MinActiveRowVersion>
        <a:MoreRecords>false</a:MoreRecords>
        <a:PagingCookie>&lt;cookie page="1"&gt;&lt;accountid last="{19C5970E-ABBC-E211-B7AD-3C4A92DBD85C}" first="{19C5970E-ABBC-E211-B7AD-3C4A92DBD85C}" /&gt;&lt;/cookie&gt;</a:PagingCookie>
        <a:TotalRecordCount>-1</a:TotalRecordCount>
        <a:TotalRecordCountLimitExceeded>false</a:TotalRecordCountLimitExceeded>
      </RetrieveMultipleResult>
    </RetrieveMultipleResponse>
  </s:Body>
</s:Envelope>
--------------------------------------------------

As i have mentioned before there are tools available online which can do all the above and some more. CRM itself has some sdk message that converts fetch xml to query expression and vise versa. One example of it can be found here.

Introduction to Fetch XML in CRM (Part 2)

A simple FetchXml

I did not know until recently that you can actually view your FetchXml through advance find. To do it you have to go to advance find of any record. Once in you need to select a new query. And as soon as you do that you’ll see ‘Download FetchXml’ button in top ribbon.

Now for just to get you guys started let’s do a very simple FetchXml. The following is a sample FetchXml which gets all the contacts in CRM,

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
  </entity>
</fetch>

The code itself is very much self explanatory. Entity name is the entity you are selecting. The attribute identifies the columns you are selecting. “Order” notes defines order by statement. The order by is default and not required. Here's almost similar query with 'and' and 'or' condition.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="contact">
    <attribute name="fullname" />
    <attribute name="telephone1" />
    <attribute name="contactid" />
    <order attribute="fullname" descending="false" />
    <filter type="and">
      <filter type="or">
        <filter type="and">
          <condition attribute="createdon" operator="on" value="2013-06-26" />
          <condition attribute="department" operator="eq" value="IT" />
        </filter>
        <condition attribute="firstname" operator="eq" value="John" />
      </filter>
    </filter>
  </entity>
</fetch>

So there you go, a very simplistic FetchXml from a very novice point of view. For more information you go to MSDN . Also Gareth Tucker’s Blog is ideal place for starters in FetchXml.

Introduction to Fetch XML in CRM

What I am about to discuss will probably be very primitive to most CRM developers. In my defense i can only say that the keyword here is “Introduction”. This topic is really going to be for absolute starters from a starter’s point of view.  If time permits I would like this topic to be extended into some parts in where I would like show how to use FetchXml in both server and client end, a glimpse of a code example based on JavaScript which i recently did .Enough said, now let’s start the party,

FetchXml: What, Why and When

FetchXml is an Xml query structure in MSCRM 2011 to retrieve data. Every data request in crm gets converted into one of these query and gets passed to the CRM services. CRM Service (Data / meta data) runs the query to the database and then returns an Xml response to the request. CRM then parses the XML response and puts the information into its place and displays the page. Since most of this process can be done in client end, the whole FetchXml business is visibly fast. It is also the only way to manipulate data for Crm Online solutions.

FetchXml is widely used in creating reports in CRM. You can also use FetchXml, to use in CRM form properties to to access and manage data.

Benefits:

As I have discussed earlier it is the most faster way to access and manipulate data. Through FetchXml, you can do almost all the things you can do in an average workflow.

You can integrate FetchXml with javascript. So it it is very light weight.

Shortcoming

The only improvement factor that i can think of about FetchXml now, is it involves a lot of coding. complicated queries will take even more lines of code which may lead to programmatic mistakes. There are some FetchXml query builder available in the net. Probably the most famous and acclaimed of them is called SwTools available in this web site . Before you dig into it let me remind you that this is a complete third party software so if you use it you pretty much on your own. The software which initially was build CRM 4.0, claims that it works with CRM 2011 as well. I have tried using it and it really works. However it only do retrieve commands (selects). As much as i know it does not do Non Queries. (Did i forget that you can perform Insert/Update/Delete through FetchXml?).

I prefer one that involves a slight more coding but developed by Microsoft. It is called SoapLogger which is available in CRM 2011 sdk. I will discuss about this in future posts.