Outer Joins in Fetch XML queries in CRM2011

This blog will walk you through on how and when to use “Outer” joins in CRM2011 fetch queries. Outer joins can be very important for  fetch xml based SSRS reports.
Here are some of the examples.

Example 1

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>

This query will return name(Topic), customerid(Potential Customer), estimatedvalue_base(Estmated Revenue), telephone1(Main Phone from account entity).
This query will return the above columns for all the opportunities. What will happen if the potential customer on opportunity is a contact instead of an account? The answer is it will still return all the opportunities as there is an outer join between the two entities. It will return the empty telephone 1 column. If we remove the link-type="outer" then the query won’t return any opportunities that have contacts as potential customers.
This query is also possible using advance find.

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>

The above fetch xml will retrieve the subject, description, createdon  columns for related phone call activities as well as the columns mentioned in example one.
I have created a sample SSRS report using the above fetch xml query. Here is result. You can change the display name for the columns in the data set as required.

Single Sign On for CRM 2011 Online

In July 2012 CRM online has been moved to “Online Commerce Platform”. It is same platform used by Office 365. One of the benefits of this change is that the customers can access various office 365 services using same username and password. But the thing that excites me the most is the ability to configure Active Directory Federation Services (ADFS) to work with Office 365. This makes single sign on a reality for CRM Online users. The users can use their AD credentials to login to CRM Online, Office 365 services and other on premise applications.
I did some research and found these 2 blogs on how to setup ADFS for CRM Online/ Office 365.
http://blogs.msdn.com/b/girishr/archive/2012/09/05/dynamics-crm-identity-federation-demo-setup.aspx
http://www.messageops.com/documentation/office-365-documentation/ad-fs-with-office-365-step-by-step-guide
Go Dynamics CRM….

Error message on running SDK samples for CRM Online on Office 365

If you are trying to connect to CRM Online using Office 365 account  you will receive an error message “LDAP server is unavailable”.
Here is the screen shot.

The problem is the authentication process. I debugged the code and find out that SDK samples are using  the following code to the username.

//For OnlineFederation environments, initially try to authenticate with the current UserPrincipalName for single sign-on scenario.
                                        
else if (config.EndpointType == AuthenticationProviderType.OnlineFederation && config.AuthFailureCount == 0)
{
    config.UserPrincipalName = UserPrincipal.Current.UserPrincipalName;
    return null;
}

The value of "UserPrincipal.Current.UserPrincipalName" is always null and hence the error message. This code sits in CrmServiceHelpers.cs.
If we comment out the above mentioned code. The system will prompt you to enter your username and password and you are good to go.
You will receive the similar error message if you are trying to use developer’s toolkit with CRM Online on Office 365.The exact message is “Value Can not be null”. Here is the screen shot.

I don’t have a solution for this problem. I know  the Visual Studio stores the CRM connection string for developer toolkit in .suo file. I was hoping if I can create this file manually or update this file. But I don’t have a solution yet. If you guys have a solution, please let me know.
Thanks…