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…

Problem with JavaScript text editor after installing Roll Up 10 (CRM2011)

I was talking to my friend Eric yesterday and he pointed out a bug introduced in Roll Up 10.  After installing rollup 10, if you open an existing JavaScript webresource or create a new JavaScript webresource using crm text editor, chances are that you will receive the following error message.

If you click on “OK”, it will truncate your JavaScript. The good news that you can still create new webresource or update existing resources using some external text editor and upload that file into your JavaScript webresource.

Thanks.

How to create meaningful device credentials using Device Registration Tool

To create a standalone application ( web application or console application) or generate early bind classes using CrmSvcUtil, we need a connection string to connect to the CRM. The connection strings are different for different deployment types. Here is the link to the MSDN article about CRM connection strings.
To connect to CRMOnline, we need to specify the device credentials as well as LIVE username and password. Here is an example of CRMOnline connection string.
Url=https://contoso.crm.dynamics.com; Username=jsmith@live-int.com; Password=passcode; DeviceID=contoso-ba9f6b7b2e6d; DevicePassword=passcode
The device credentials sit in “C:\Users\username\LiveDeviceID\LiveDeviceID.XML” file.
If the file does not exist then it means your device is not registered. If it does exist then the file contents will look like the following screen.

If you look at username and pwd in the above screen shot, they look like random letters and numbers.
Microsoft provides the “Device Registration Tool” to register the device for CRMOnline. This tool can be found at “\sdk\tools\deviceregistration\bin\Debug.DeviceRegisteration.exe” location.
The DeviceRegistration.exe supports 2 operations.

• Show
• Register

Here is some of the screen shots of “Device Registration Tool” in action.


The second screen shot displays the outcome of register operation. The tool created a device id and device password. As I mentioned earlier in the blog, these credentials are not readable and you can’t really remember them. But we can actually specify the device id and password by passing username and password parameter in the register operation. Have a look at the following screen.
 
That's how you can specify your own device id and device password for device registration. The only thing I can’t work it out is that registration tool adds 11 in front of the specified device id.
Thanks….

Check if the date is a business day in CRM2011

In CRM solutions, some times we need to know that  a date is working business day. For example a company policy can be a complaint must be closed within 5 working days. In this scenario, we need to update the working days on the case entity at the end of every business day. One of the solution to this scenario can be to write a console application and schedule it to run every night and update the working days.
I have written this code to check if current date is a business day. The code is checking if the date is a weekend or a “Business Closure” day defined in CRM. Here is the code. Make the changes according to your requirements.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Crm.Sdk.Messages;

namespace dateCheck
{
    class dateCheck
    {
                
        static void Main(string[] args)
        {
            //Date to be checked 
            DateTime dateBeingTested = DateTime.Now;

             if (IsItWeekend(dateBeingTested) || CheckHolidays(dateBeingTested))
             {
                 Console.WriteLine(dateBeingTested.ToString("d") + " is not working day.");
             }
             else
             {
                 Console.WriteLine(dateBeingTested.ToString("d") + " is a working day.");
             }
        }

        static bool IsItWeekend(DateTime date)
        {
            if ((date.DayOfWeek== DayOfWeek.Saturday) || (date.DayOfWeek==DayOfWeek.Sunday))
            {
                return true;
            }
            return false;          
        }

        static bool CheckHolidays(DateTime date)
        {
            var connection = new CrmConnection("Crm");
            var service = new OrganizationService(connection);

            QueryExpression query = new QueryExpression("calendar");
            query.ColumnSet = new ColumnSet(true);
            ConditionExpression condition = new ConditionExpression();
            condition.AttributeName = "name";
            condition.Operator = ConditionOperator.Equal;
            condition.Values.Add("Business Closure Calendar");
            query.Criteria.Conditions.Add(condition);
            EntityCollection calendars = service.RetrieveMultiple(query);
            EntityCollection calendarrule = calendars[0].GetAttributeValue<EntityCollection>("calendarrules");
            return calendarrule.Entities
            .Where(e => ((DateTime)e["starttime"]).Date == date.Date).Any();

        }
    }
}

To make this code works, we need to define a connection string “Crm” in app.config file. Change the server name and credentials to match your deployment.

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <!--<add name="Crm" connectionString="ServiceUri=http://servername/orgname; Domain=domainname; Username=username; Password=password"/>-->
    <add name="Crm" connectionString="ServiceUri=https://orgname.crm5.dynamics.com; Username=username; Password=password; DeviceID=deviceid; DevicePassword=password"/>
  </connectionStrings>
  </configuration>

Happy Programming…Thanks Russel for the code.

CRM2011 and Cross Domain calls Part 2

In my last blog, we discussed what is JSONP and how to make cross domain calls using JSONP? In this blog, I will share how to make cross domain calls using jQuery and Ajax. I will use the currency exchange and twitter APIs to make JSONP calls. As of version 1.5 jQuery supports JSONP calls.

How to make a JSONP call using jQuery

Here is the code. The code is using the twitter APIs. The code will retrieve the tweets by Donna Edward( CRM MVP) and display the text of the first tweet.

<HTML><HEAD><TITLE></TITLE>
<SCRIPT src="http://servername/SandpitAmreek//WebResources/new_/jquery1.7.2.js"></SCRIPT>

<SCRIPT type=text/javascript>

function callTheJsonp()

{

//debugger;

// the url of the script where we send the asynchronous call 

//var url = "http://openexchangerates.org/api/latest.json?callback=?";

jQuery.getJSON("https://api.twitter.com/1/statuses/user_timeline.json?callback=?", { include_entities: "true", include_rts: "true", screen_name: "edwardsdna" },
 function(data) {
   alert(data[0].text);
});


}

} </SCRIPT>

<META charset=utf-8></HEAD>
<BODY onload=callTheJsonp() contentEditable=true>hey hey</BODY></HTML>

Run the code and we will received the similar to the following screen.

How to make a JSONP call using AJAX

Here is the code call the JSONP call using AJAX.

<HTML><HEAD><TITLE></TITLE>
<SCRIPT src="http://auntmsftv23/SandpitAmreek/WebResources/new_/jquery1.7.2.js"></SCRIPT>

<SCRIPT type=text/javascript>

function callTheJsonp()

{

debugger;

// the url of the script where we send the asynchronous call 

var url = "http://openexchangerates.org/api/latest.json?callback=parseRequest";

$.ajax({

   url: url,

   dataType: "jsonp",

   jsonpCallback: "parseRequest"

});


}

// this function should parse responses.. you can do anything you need.. 

// you can make it general so it would parse all the responses the page receives based on a data field

function parseRequest(data)

{

try // try to output this to the javascript console 
{
   alert("AUD: " + data.rates.AUD);} 

catch(an_exception)
// alert for the users that don't have a javascript console
{
     alert(data);
} 

} </SCRIPT>

<META charset=utf-8></HEAD>
<BODY onload=callTheJsonp() contentEditable=true>Testing Cross Domain Calls</BODY></HTML>

Run the code and we will received message USD/AUD exchange rate .