Sunday, 26 May 2013

Outer Join in Fetch Xml Ms Crm 2011

Creating a query to retrieve  opportunities with fields from related account entity. Here is the query.
 
  
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
     <attribute name="estimatedvalue_base" />
     <order attribute="name" descending="false" />
    <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="accountid">
      <attribute name="telephone1" />
    </link-entity>
</entity>
</fetch>
 
 
 
 example 2
What if we want to return all the opportunities and also any phone call 
activities related to these opportunities. This is also achievable using
 outer joins. There is 1:N relationship between opportunity and 
activitypointer entity. Therefore the query will return multiple records
 for the same opportunity if there are more than one phone call recorded
 against an opportunity. Here is fetch xml for the query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunity">
    <attribute name="name" />
    <attribute name="customerid" />
    <attribute name="estimatedvalue_base" />
     <order attribute="name" descending="false" />
    <link-entity name="account" from="accountid" to="customerid" visible="false" link-type="outer" alias="accountid">
      <attribute name="telephone1" />
    </link-entity>
<link-entity name="activitypointer" from="regardingobjectid" to="opportunityid" visible="false" link-type="outer" alias="phonecall">
      <attribute name="subject" />
 <attribute name="description" />
 <attribute name="createdon" />
<filter type="and">
      <condition attribute="activitytypecode" operator="eq" value="4210" />
</filter>
    </link-entity>
  </entity>
</fetch>